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 |
|
Callaway
Starting Member
16 Posts |
 Posted - 2005-04-21 : 04:16:51
|
| I have a table of lessons and a table of topics. I also have a crossref table of LessonTopic (lessontopicID,lessonID,topicID).I'd like to select all the topics from the topic table that do not have a crossref record for the current lesson.The reason I need this is to provide a quick list of topics NOT associated with the lesson. This will keep my users from accidentaly adding redundant crossref records from the front-end.Thanks in advance for any advice :) |
|
|
satishdg
Starting Member
10 Posts |
Posted - 2005-04-21 : 05:29:19
|
| Is there any relation between lessons and topics table. |
 |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-04-21 : 06:33:50
|
| [code]SELECT t.TopicIDFROM dbo.Topics AS t LEFT JOIN dbo.LessonTopics AS lt ON lt.TopicID = t.TopicID AND lt.LessonID = ? WHERE lt.TopicID IS NULL[/code]Replace the '?' with the LessonID.Mark |
|
|
|
satishdg
Starting Member
10 Posts |
Posted - 2005-04-21 : 06:44:37
|
| TRY THIS.SELECT t.TopicIDFROM dbo.Topics AS t LEFT JOIN dbo.LessonTopics AS lt ON lt.TopicID = t.TopicID AND lt.LessonID = ? WHERE lt.TopicID IS NOT NULL |
|
|
|
Callaway
Starting Member
16 Posts |
Posted - 2005-04-21 : 11:09:18
|
quote:
Originally posted by mwjdavidson
SELECT t.TopicIDFROM dbo.Topics AS t LEFT JOIN dbo.LessonTopics AS lt ON lt.TopicID = t.TopicID AND lt.LessonID = ? WHERE lt.TopicID IS NULL Replace the '?' with the LessonID.Mark
Wow, that worked like a charm, I was so close to that answer, but I missed the Null part, very smart ! Thanks for your replies :) |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-04-21 : 12:00:54
|
| No problem CallawayMark |
|
|
|
|
|
|
query excluding crossref