Help with a confusing SELECT Query....

[Quadcom]

I have a query that I put together which combines 3 tables. There is a WHERE clause applied to a field in one of the tables. The problem is if there are no records to satisfy the where clause for the image_rank then no records at all are returned.

As a better example. Table 1 has product information, table 2 has the images associated with the products. The WHERE clauses specify a category number for the product information and the image rank field equal to 1.

****************Table Examples*************
*Products
1. ID
2. Name
3. Specs
4. products_rank

*Manufacturers
1. ID
2. Name
3. URL
4. manufacturers_rank

*Images
1. ID
2. product_id
3. image_name
4. image_rank

SELECT products.*,  manufacturers.manufacturers_name, images.image_name
FROM products
join  manufacturers on  products_manufacturer =  manufacturers_id
join  images on products_id = images_pid
WHERE products_display_ENCA = 1 AND products_categoryENCA = #url.p#
AND images.image_rank = 1
ORDER BY manufacturers.manufacturers_rank, products.products_rank

[SteveSherryD8]

If I were doing MS SQL i'd use a "left outer join" rather than "join".
I'm not an expert in MySQL, but this seems most logical to me.

[Quadcom]

I had solved the problem ages ago but after writing a couple thousand lines I forgot.

The left joins are correct, the addition is a pseudo where clause on the join statement eg.

left join table on table1.id = table2.fk AND column = 'var'