Set,

thanks for reducing the confusion in my join statement. i've now experimented some more and reduced the problem down by ignoring the products and productversions table which aren't really part of the problem. what i'm trying to do is list all items from 'Bugs' in this instance (normally there is a long where clause as part of this query), but add another column derived from the "notifications" table. This table connects the Bugs and personnel table together in a Many-many relationship so that any member of staff can request notification of the change of status of a particular bug. the resulting column from my query should return all bugs, but with a non-null value for each bug which is stored in the Notifications table for a particular user. from my 'less than elementary SQL' i thought that a left outer join did what i needed, ie returned a row for every row in the Bugs table and included data in the row from the Notifications table if data existed that matched the on criteria.

select n.UserID, b.text from Bugs b left outer join Notifications n on b.id=n.BugID

except that i need to specify a where clause so that non-null data is only returned in this column for a given user in the Notification data. e.g. for user 'T'

UserID Text
T Bug1
<null> Bug2
T Bug3

i've experimented with 'union'ing 2 queries together using where n.UserID='T' and where n.UserID<>'T' but still not got what i want.

many thanks for taking a look at this for me.

Kind regards,
Pete