Condition using left join

[hessodreamy]

I'm having trouble constructing this left join query.
I want to retrieve all rows from tProducts, and relevant matches from pictures, however what I have got just jams up the server and I have to restart. Am I doing it wrong?

SELECT prodCode, prodName
FROM tProducts LEFT JOIN pictures
ON productID = relProdId
AND prodCode = 'SF2'

 

[TonyGroves]

Can't see anything wrong there.

Presumably the productID and relProdId fields are in different tables? If they're both in the same table, that could cause massive problems.

 

[hessodreamy]

Yes they are in different tables. On someones recommendation I have put indexes in now and the query runs, however what I find is that it returns ALL rows from tProducts, and disregards both conditions.

To clarify, I want all rows in tProducts where prodcode starts = SF, and any matching entry in pictures, but also showing null where there is no entry in pictures.

 

[TonyGroves]

Should you be using:
[tt] ON prodcode = relprodid[/tt]

 

[r937]

on someone's recommendation" was me, in another forum on a completely different site



please identify which table the columns prodCode, prodName, productID, and relProdId belong to



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[hessodreamy]

I've got it working now. I actually wanted prodcode like 'SF%'. This returns the correct rows, though I'm not sure why!

Thanks for help anyways.