0

I think I'm not asking the question the right way so I can not find the answer even with hours of research. So I'm hoping I can get an answer here.

Table1
fragranceId, description, status
1,           Floral,      Active
2,           Musk,        Active
3,           Fruity,      Active

Table2
fragranceId, contactId, value
1,           1,         Love It
2,           1,         Hate It
3,           1,         NULL

I can combine the results of the 2 table and get

fragranceId, contactId, description, status

BUT I'm spinning my wheels trying to get it to limit to value and contactId while showing those fragrances that do not have a value.

IE:
fragranceId, contactId, description, status, value
1,           1,         Floral,      Active, Love It
2,           1,         Musk,        Active, Hate It
3,           1,         Fruity,      Active, NULL
suspectus
  • 16,548
  • 8
  • 49
  • 57
user3587156
  • 1
  • As long as you are limiting on contactId you are actually performing an inner join. Or are you looking into also finding entries from Table1 without matching entry in Table2? – TheConstructor Apr 29 '14 at 22:57
  • That is what I am getting an Inner Join. I want to show all fragrances, with a value and those that do not have the value at the same time. I actually found it in another thread. http://stackoverflow.com/questions/1219909/mysql-join-with-where-clause If I'm not mistaken, I do a AND after merging the two tables to limit the results to the value I want. I can then do a WHERE for other criteria. Thanks for the help :D – user3587156 Apr 29 '14 at 23:55

3 Answers3

0

Try this:

SELECT * FROM Table1 LEFT JOIN Table2 USING (fragranceId) WHERE contactId = 1 AND (value IS NULL OR value = 'Hate It')

Comparing to NULL you should use IS NULL, IS NOT NULL and <=> as = will always return NULL if one side is NULL and NULL later evaluates to false.

Also you may be interested in COALESCE() which is a nice way of turning NULLs into text. E.g. use COALESCE(value, 'No opinion given') to get rid of NULL-value-fields in your result.

TheConstructor
  • 4,285
  • 1
  • 31
  • 52
0

Due to the unique properties of null using the "normal" comparison operators will not give you what you may expect. The reason is that logically null means unknown.

So if value contains null. And you compare value = null the result is unknown and the row is not mstched / returned. if you ask value != null the result is unkown and the row is not matched.

SQl solves this with the keyword IS NULL and IS NOT NULL.

So if you state value IS NULL then the result is true and the row is matched / returned.

M.Scherzer
  • 921
  • 7
  • 9
0

What if you try like this

select 
t1.fragranceId, 
t2.contactId, 
t1.description, 
t1.status,
t2.value
from 
Table1 t1 
left join Table2 t2
on t1.fragranceId = t2.fragranceId
and t2.value is not null

See a demo fiddle here http://sqlfiddle.com/#!2/1a702/2

Rahul
  • 76,197
  • 13
  • 71
  • 125