Welcome to WebmasterWorld Guest from 54.166.222.116

Forum Moderators: open

Message Too Old, No Replies

LEFT OUTER JOIN with consideration to dates

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

5+ Year Member Top Contributors Of The Month



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
3:13 am on Aug 19, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member




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)
6:04 am on Aug 19, 2013 (gmt 0)

5+ Year Member Top Contributors Of The Month



Thank you, that worked perfectly!
12:27 pm on Aug 22, 2013 (gmt 0)

5+ Year Member Top Contributors Of The Month



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
9:56 pm on Aug 22, 2013 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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)
 

Featured Threads

Hot Threads This Week

Hot Threads This Month