hi,

would one of you sql people mind helping me out here. i have to enhance our
internal defect tracking software running on IB6. a simplified list of the
table structures is as follows:

Bugs (id integer, ProductVersionID integer, text varchar(100));
ProductVersions (id integer, ProductID, ProdVer varchar(100));
Products (id integer, ProdName carchar(100));
Notifications (BugID integer, UserID integer);
Personnel (id integer, name varchar(100));

the data goes like this, "many bugs for a product version", "many product
versions for a product". I have now added the notifications table which
lists many-many relationship of personnel and bugs. this is the enhancement
i am trying to add so that i can identify bugs which are on a given users
notification list.

the previous query worked fine and went like this:-
select p.ProdName, pv.ProdVer, b.id, b.text from Bugs b join
(productversions pv join products p on pv.ProductID=p.id) on
b.ProductVersionID=pv.id where etc....

now i need to add to this query a column from Notifications table which will
contain either null or the UserID. ie it should list all the bugs as before
but with the extra column indicating whether or not a row exists in the
Notifications table for a given user ID

i tried this:-
select n.UserID, p.ProdName, pv.ProdVer, b.id, b.text from Bugs b join
(productversions pv join products p on pv.ProductID=p.id) on
b.ProductVersionID=pv.id left join Notifications n on b.id=N.BugID where
n.name='myuser'

but i'm now well out of my SQL depth, and getting very confused about nested
joins

the actual column returned from Notifications table does not matter, my grid
will check for not null and highlight the row.

many thanks in advance

Kind regards,
Pete Bray