homepage Welcome to WebmasterWorld Guest from 184.73.104.82
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
LEFT OUTER JOIN with consideration to dates
ocon

5+ Year Member



 
Msg#: 4603124 posted 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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4603124 posted 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

5+ Year Member



 
Msg#: 4603124 posted 6:04 am on Aug 19, 2013 (gmt 0)

Thank you, that worked perfectly!

ocon

5+ Year Member



 
Msg#: 4603124 posted 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

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 4603124 posted 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)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved