need help with a join

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

need help with a join

Post by Burrito »

I have two tables as follows:

parent table called lp_files

id
filename
filedescription
filetype
etc

child table called lp_contexts
id
fileid
context

I need to do a join to join those two tables together selecting all the info from the parent table based on information in the context field of the child table.

right now I have it working with a subquery but I need to use a different join type to speed up my query some.

here's what I have right now:

Code: Select all

select * from lp_files where id in (select fileid from lp_contexts where context in ('one','two','three))
any way to use a different join type for that? ie inner, left etc?
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

hawleyjr hooked me up with a solution via MSN.

thx jr

Code: Select all

SELECT a. * 
FROM lp_files AS a
LEFT JOIN lp_subjects AS c ON a.id = c.fileid
LEFT JOIN lp_topics AS t ON a.id = t.fileid
WHERE c.subject
IN (
'math'
)
AND t.topics
IN (
'algebra'
)
Post Reply