Mysql OUTER JOIN syntax

Question

My tables are as follow

productsA : name,price

productsB : name,date,price,...

At the moment, productsB contains a subset of productsA, with today's date (trying to make things simple, I know the info I'm missing, and will be able to sort out the rest).

I want to select all products from products A if there's no records in productsB for today's date. For simplification let's say date='2014-04-15' (fixed).

I tried :

SELECT a.*
FROM productsA AS a 
LEFT OUTER JOIN productsB as b on a.name = b.name AND b.date != '2014-04-15'

But it doesn't work...

I tried to simplify, and remove the date parameter, but I end up with the whole productsA table, no matter how I try :

SELECT a.*
FROM productsA AS a 
LEFT OUTER JOIN productsB AS b ON a.name = b.name

It must be simple, yet I'm stuck (I spared you all the errored attempts)... Anyone, help?

Michael Berkowski · Accepted Answer · 2014-04-15 20:50:42Z

2

To find those in a with no related record in b, principally you need to be looking for NULL values with a LEFT JOIN.

SELECT
  a.*
FROM
  productsA AS a
  /* Your joining condition was almost correct- you need records equaling the date... */
  LEFT JOIN productsB AS b ON a.name = b.name AND b.date = '2014-04-15'
WHERE
  /* NULL returned for b means no match */
  b.name IS NULL

Note: with the correct DATE type on that column you could also use b.date = CURDATE() in the ON clause...

edited Apr 15, 2014 at 20:50

answered Apr 15, 2014 at 20:14

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

xShirase Over a year ago

Yes, I oversimplified the question to get the info I really needed. It will of course be a DATE field in my real db ;)

Michael Berkowski Over a year ago

@xShirase I'm going to edit my answer to use the right date format for MySQL then. I can't bear looking at a m/d/yy date string...

xShirase Over a year ago

haha ok then, I'll edit my question. Thanks for your detailed right answer, i'm sure it'll help more than just me!

Collectives™ on Stack Overflow

Mysql OUTER JOIN syntax

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related