How to write this join query 1

[tofuTnT]

Hi All,

I have wrote a query for MySQL 4.1.4-gamma. Now I moved the file to my hosting server and the query won't work any more because their MySQL server was still 4.0.22 and it didn't support subquery.
The structure of the table is as follow.

table1: Product
product_id (PK)
name
table2: Product_Image
image_id
product_id
location
isPrimary
A product could have multiple (or none at all) images and one of them woul be primary.
My old query on MySQL 4.1.4 was like this
SELECT name, location
FROM Product p
LEFT JOIN (SELECT product_id, location FROM Product_Images WHERE isPrimary=1) AS images i ON p.product_id=i.product_id
My result would be
product1, location1
product2, location2
product3, NULL
product4, location4

Because MySQL 4.0.22 doesn't support subquery, I wonder how you can write a query to achieve this.

Thanks,

tofuTnT

 

[r937]

subquery not necessary if you make the isPrimary condition part of the JOIN

select P.name
     , PI.location 
  from Product as P
left outer
  join Product_Images as PI
    on P.product_id 
     = PI.product_id
   and PI.isPrimary = 1

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

 

[tofuTnT]

r937, Thanks a lot!