SELECT data that is not in a table from another table? 1

[Niavlys]

Hi,
I have to tables : Models_Default and Soft_Info

Models_Default has 2 fields : TemplateID and SoftID
Soft_Info has 3 fields : SoftID, SoftName, Version

I need to get data in from Soft_Info table that SoftID IS NOT in Models_Default table and TemplateID is equal to 15.

I tried LEFT, RIGHT and INNER JOIN combinations but I can't figure out how to retrieve what I need.


I have something like that but it doesn't work

SELECT Soft_Info.Software, Soft_Info.Version, Soft_Info.SoftID
FROM Soft_Info LEFT JOIN Models_Default ON Soft_Info.SoftID = Models_Default.SoftID 
WHERE Models_Default.SoftID Is Null AND Models_Default.TemplateID=15

Can someone help me please? It is hard to explain so if you need more info...

Thanks!

 

[SQLSister]

Since the template ID is only in the default table, it is not possible to have a value fo 15 if the record does not exist.

Questions about posting. See faq183-874

 

[ESquared]

Try this:

SELECT
   Soft_Info.Software,
   Soft_Info.Version,
   Soft_Info.SoftID
FROM
   Soft_Info
   LEFT JOIN Models_Default
      ON Soft_Info.SoftID = Models_Default.SoftID
      AND Models_Default.TemplateID=15
WHERE Models_Default.SoftID Is Null

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)

 

[Niavlys]

Thank you very much!

 

[ESquared]

Putting conditions in the JOIN clause is very useful, sometimes.

-------------------------------------
It is better to have honor than a good reputation.
(Reputation is what other people think about you. Honor is what you know about yourself.)