SQL-problem: List companies not mapped for value x

[Filips Benoit]

Dear All,

Tables:
COMPANY: COM_ID, COM_NAME, etc.
1 Cola
2 Microsoft
3. Ford

COMPANY_PROPERTY: CPROP_COM_ID, CPROP_PRP_ID, CPROP_VALUE
1 1 True
1 236 False
2 1 True
3 1 True

PROPERTY: PRP_ID, PRP_NAME, etc.
1 Wordwide
236 BadPayer

I want all COM_NAME not mapped to property 236
In this case it shoud return:

Microsoft
Ford

Thanks

Filip

 

[Norman Yuan]

Use DISTINCT keyword in SELECT clause. If all companies have at least one
record in COMPANY_PROPERTY table, then you can

SELECT DISTINCT C.COM_NAME
FROM Company_Property CP
INNER JOIN Company C ON C.Com_ID=CP.CPROP_COMID
WHERE CP.CPROP_PRP_ID<>236

Or, use IN keyword in WHERE clause, if it is possible that some company's
name may not present in Company_Property table, you can

SELECT COM_NAME
FROM Company
WHERE COM_ID NOT IN (SELECT CPROP_COM_ID FROM Company_Property WHERE
CPROP_PRP_ID<>236)

Or, use EXISTS keyword in WHERE clause,...

 

[Alex MRu]

Quick answer:
you should build two queries:
query1:
Select companyid where propid=236

query 2
do a left join on Company and company_property tables on Com_ID and
CPROP_COM_ID
You should also specify WHERE (((Query1.CPROP_COM_ID) Is Null))

Your result will be displayed.