Help me with this Query

I have a table 'Y' with years (YearID, YearDescription)(Eg., 1, 2000-01; 2,
2001-02; etc) I have data in another table 'X' (x,x, YearID,x,x,x...). I hav
e
data populated this table 'X' with data as shown above. Suppose I have a
record such as (x,x,1,x,x,...) pertaining to one employee and another two
records such as (x,x,1,x,x,...) and (x,x,2,x,x...), I need ALL THOSE RECORD
S
WHICH ARE MISSING. i.e. In the case of the first employee here, I need a
recordset which flashes the missing year (i.e. 2 in this case) and in the
case of the next employee the query shoudn't yield any results at all becaus
e
both the years are present in the table 'X'
Can somebody help me please?Sunny
Take a look at WHERE NOT EXISTS clause in the BOL
"SunnyT" <SunnyT@.discussions.microsoft.com> wrote in message
news:689CADB1-8733-4C64-B545-44F11CCA74FD@.microsoft.com...
>I have a table 'Y' with years (YearID, YearDescription)(Eg., 1, 2000-01; 2,
> 2001-02; etc) I have data in another table 'X' (x,x, YearID,x,x,x...). I
> have
> data populated this table 'X' with data as shown above. Suppose I have a
> record such as (x,x,1,x,x,...) pertaining to one employee and another two
> records such as (x,x,1,x,x,...) and (x,x,2,x,x...), I need ALL THOSE
> RECORDS
> WHICH ARE MISSING. i.e. In the case of the first employee here, I need a
> recordset which flashes the missing year (i.e. 2 in this case) and in the
> case of the next employee the query shoudn't yield any results at all
> because
> both the years are present in the table 'X'
> Can somebody help me please?|||>> I have a table 'Y' with years (YearID, YearDescription)(Eg., 1, 2000-01;
2, 2001-02; etc) <<
Months in CHAR(n) format' A really bad design and you failed to post
DDL! Since time is a continuum, we always model it as durations
between date ranges. This is a basic SQL programming trick, not
anything fancy.
Because good programmers care about maintaing their code, they use
better names than Y for data elements.
CREATE TABLE JulianizedMonths
(julian_month INTEGER NOT NULL PRIMARY KEY,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date > end_date));
.) and (x,x,2,x,x...), I need ALL THOSE RECORDS [sic] WHICH ARE MISSING. i.e. In the c
ase of the first employee here, I need a recordset [sic] which flashes the missin
g year (i.e. 2 in this case) and in the case of the next employee the query
shoudn't yie
ld any results at all because both the years are present in the table 'X' <<
Still no DDL for the people trying to do your job for you, unh? Please
be more polite in future postings. Why don't you know that rows are
not records? Please read any basic RDBMS book, so you at least know the
right words.
If your boss gives you specs like this from which to write code, it is
time to quit the job. If you wrote this, quit your job and quit
programming SQL.
Here is a guess at what you might have meant, with a table that is
almost as useless as yours, but funnier and it has a key!! Wow!
Might want to use keys in the future..
CREATE TABLE VaguePeopleThingies
(person_id INTEGER NOT NULL,
julian_month INTEGER NOT NULL,
.
PRIMARY KEY (person_id, julian_month));
Here is one way:
SELECT V1.person_id
FROM VaguePeopleThingies AS V1
WHERE NOT EXISTS
(SELECT *
FROM JulianizedMonths AS J1
V1.julian_month = J1.julian_month);
This is a poor design, however. You should put a date in the
VaguePeopleThingies table then use :
V1.vague_date BETWEEN J1.start_date AND J1.end_date
Please don't post anything this awful again.|||--CELKO-- wrote:
[another priceless rant]
Joe, you should collect your rants from Google Groups and publish them
as a book. :)|||Well, I ready have SQL FOR SMARTIES (third edition 2005), SQL PUZZLES &
ANSWERS, DATA & DATABASES, TREES & HIERARCHIES IN SQL, and SQL
PROGRAMMING STYLE. I just turned in the manuscript for next one, so I
d have a fianl title yet.
Getting serious for minute, I have considered a book on "Bad SQL" which
woulkd show the origianl code, do a clean up, then discuss *why* the
code was bad and finally what the mind-set was that lead to the
mistake. I remember a math book that discussed the cause of errors in
proofs.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1143652476.284305.219270@.t31g2000cwb.googlegroups.com...
> Well, I ready have SQL FOR SMARTIES (third edition 2005), SQL PUZZLES &
> ANSWERS, DATA & DATABASES, TREES & HIERARCHIES IN SQL, and SQL
> PROGRAMMING STYLE. I just turned in the manuscript for next one, so I
> d have a fianl title yet.
> Getting serious for minute, I have considered a book on "Bad SQL" which
> woulkd show the origianl code, do a clean up, then discuss *why* the
> code was bad and finally what the mind-set was that lead to the
> mistake. I remember a math book that discussed the cause of errors in
> proofs.
If this post is any indication...
I hope that you ran that manuscript through a good spelling and grammar
checker.|||Strrrrike! :)
ML
http://milambda.blogspot.com/|||I used to cover up poor spelling with bad handwriting!