Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2003-03-14 : 07:26:32
|
John-Paul writes "hi,Im trying to get list of records that aren't in a relationship.... let me explain with an example...Each user has a record in the Users table, and a table stores the relationships between users and a list in a third table (say, membership in a mailing list). i need to get hold of a list of mailing lists that a users isnt subscribed too (ie there isn't a record in the middle table). how can i do this simply?i've tried things like select * from MailingLists where EXISTS (SELECT ListID from UserToMailingList WHERE UserID=1) but that doesn't work properly - am i even close?" |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-03-14 : 07:45:18
|
| You want to consider all users and all mailing lists ... and only return those that aren't in the table joining the two.That translates to:select u.user, m.mailinglistfrom users ucross join mailinglist mleft outer joinUserMailingLists UMLon u.user = UML.user and m.mailinglist = UML.mailinglistWHERE UML.user IS NULLThat's one way of doing it that follows the logic of what you are looking for very closely. A cross join is a good way to consider all possiblities (in this case, all users compared to all mailinglists) and then from there you can LEFT OUTER JOIN to other tables to determine if there is a link between the two.The WHERE is just using an arbitrary non-nullable field from that middle table to find users and mailing lists that do not have a matching record in the usersmailinglists table.Using the cross join --> left outer join techinique allows you to do some very powerful reporting, without dropping out records or missing combinations.- Jeff |
 |
|
|
|
|
|
Getting a table of records not in another table