Welcome to WebmasterWorld Guest from 54.82.29.141

Forum Moderators: open

Message Too Old, No Replies

LEFT OUTER JOIN with consideration to dates

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

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
posts:227
votes: 1


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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)
6:04 am on Aug 19, 2013 (gmt 0)

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
posts:227
votes: 1


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

Full Member

5+ Year Member Top Contributors Of The Month

joined:Sept 30, 2009
posts:227
votes: 1


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:May 31, 2005
posts:1108
votes: 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)
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members