LEFT OUTER JOIN with consideration to dates

Forum Moderators: open

Message Too Old, No Replies

LEFT OUTER JOIN with consideration to dates

ocon

1:18 am on Aug 19, 2013 (gmt 0)

I have two tables I would like to join.

tableA: id (unique), name
1, New York City
2, Springfield
3, Bellview
4, Springfield

tableB: id, path (unique), created
1, nyc, 2011-01-10
1, new-york-city, 2012-05-08
2, springfield, 2010-08-04
2, springfield-id, 2003-08-05
4, springfield-fl, 2012-07-05
5, anchorage, 1999-01-01

I'd like to join all of tableA with with the newest matching tableB data: id, name, path, created
1, New York City, new-york-city, 2012-05-08
2, Springfield, springfield, 2010-08-04
3, Bellview, NULL, NULL
4, Springfield, springfield-fl, 2012-07-05

I know how to do a LEFT OUTER JOIN but I don't know how to account for the created dates:

SELECT * FROM tableA LEFT OUTER JOIN tableB ON tableA.id = tableB.id

Dijkgraaf

3:13 am on Aug 19, 2013 (gmt 0)

A Not Exists and sub query maybe.

SELECT * FROM tableA
LEFT OUTER JOIN tableB ON tableA.id = tableB.id
WHERE NOT EXISTS (SELECT 1 FROM tableB AS tableB2 where tableB2.id = tableB.id and tableB2.created > tableB.created)

ocon

6:04 am on Aug 19, 2013 (gmt 0)

Thank you, that worked perfectly!

ocon

12:27 pm on Aug 22, 2013 (gmt 0)

Thank you again, now I'm trying to do another query where I select one row from tableA by it's id and join with the latest path, if any from tableB:

SELECT id, name (SELECT path FROM tableB WHERE id=2 ORDER BY created DESC LIMIT 1) AS path FROM tableA WHERE id=2 LIMIT 1

That is working well so far, but I want to combine X separate requests:

SELECT id, name (SELECT path FROM tableB WHERE id=11 ORDER BY created DESC LIMIT 1) AS path FROM tableA WHERE id=11 LIMIT 1
SELECT id, name (SELECT path FROM tableB WHERE id=12 ORDER BY created DESC LIMIT 1) AS path FROM tableA WHERE id=12 LIMIT 1
SELECT id, name (SELECT path FROM tableB WHERE id=17 ORDER BY created DESC LIMIT 1) AS path FROM tableA WHERE id=17 LIMIT 1
SELECT id, name (SELECT path FROM tableB WHERE id=18 ORDER BY created DESC LIMIT 1) AS path FROM tableA WHERE id=18 LIMIT 1
SELECT id, name (SELECT path FROM tableB WHERE id=31 ORDER BY created DESC LIMIT 1) AS path FROM tableA WHERE id=31 LIMIT 1

Into just one, if possible:

SELECT id, name (SELECT path FROM tableB WHERE id=X ORDER BY created DESC LIMIT 1) AS path FROM tableA WHERE id IN (11, 12, 17, 18, 31)

Thanks

Dijkgraaf

9:56 pm on Aug 22, 2013 (gmt 0)

SELECT id, name (SELECT path FROM tableB WHERE id=tableA.id ORDER BY created DESC LIMIT 1) AS path FROM tableA WHERE id IN (11, 12, 17, 18, 31)