Page 1 of 1

need help with a join

Posted: Mon Mar 06, 2006 11:02 am
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?

Posted: Mon Mar 06, 2006 11:42 am
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'
)