homepage Welcome to WebmasterWorld Guest from 54.225.57.156
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

    
Extended SELECT Statements
Knucklehead00




msg:4047780
 7:42 pm on Dec 22, 2009 (gmt 0)

Hey all.

I am trying to make it so that I do not have to run more than one query here. I normally create numerous queries and then just go loop crazy within PHP, but it is just time for me to learn this.

SELECT t.ID_TOPIC, t.numReplies, t.numViews, t.rating, m.memberName, b.name, msg.posterTime, msg.subject,
(SELECT ra.aid FROM recipephotos_albums AS ra WHERE ra.title LIKE %msg.subject%)
FROM yabb_topics AS t
LEFT JOIN yabb_boards AS b ON (t.ID_BOARD = b.ID_BOARD)
LEFT JOIN yabb_messages AS msg ON (msg.ID_MSG = t.ID_FIRST_MSG)
LEFT JOIN yabb_members AS m ON (m.ID_MEMBER = msg.ID_MEMBER)
WHERE b.ID_CAT='23'

So, what I need to do is SELECT the "aid" from recipephotos table using the "subject" from the messages table in the where condition. Well, at least, in theory, that is what I need to do.

Is this even possible? I am certain that my extended SELECT position is off, but I cannot seem to find an example that is like what I need.

Thanks for the help!

 

syber




msg:4053103
 3:30 am on Jan 3, 2010 (gmt 0)

You are on the right track. This will work as long as their is only one row in recipephotos_albums that matches the corresponding msg.subject. It is called a correlated subquery. it should look like this:

SELECT t.ID_TOPIC, t.numReplies, t.numViews, t.rating, m.memberName, b.name, msg.posterTime, msg.subject, (SELECT ra.aid FROM recipephotos_album WHERE ra.title = msg.subject)
FROM yabb_topics AS t
LEFT JOIN yabb_boards AS b ON (t.ID_BOARD = b.ID_BOARD)
LEFT JOIN yabb_messages AS msg ON (msg.ID_MSG = t.ID_FIRST_MSG)
LEFT JOIN yabb_members AS m ON (m.ID_MEMBER = msg.ID_MEMBER)
WHERE b.ID_CAT='23'

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