Help with 2 table query

shawjames

I have 2 tables, with fields:

products -> num, name, price, size

carts -> num, name, price

I want to do a do a query to select all "carts" as well as the "size" field if "num" matches. So I use:

SELECT carts.num, carts.name, carts.price, products.size FROM carts, products WHERE carts.num=products.num

The problem is, sometimes there is something in carts that is custom and does not match anything in products. I need this to be in the list as well.

I want to do this with one query, is it possible?

(I understand there are work arounds, with a query in a loop for products... or by adding the size field to carts... but this example is a simplified version of the issue at hand.)

Thanks!

Piranha

By doing a left join you get the size where there is a product and NULL if there are no product:

SELECT carts.num, carts.name, carts.price, products.size FROM carts LEFT JOIN products ON carts.num=products.num

But why do you have the information in two different tables, seems strange to me.

bubblenut

If the relationship between carts and products is one-one for this case then you could use a left join.

SELECT carts.num, carts.name, carts.price, products.size
FROM carts
LEFT JOIN products ON (carts.num=products.num)

Piranha

bubblenut, nothing says that the relationship is one-one. But it is likely to be one-many since it seems like products.num is a PK. Anyway you can use the query that both I and bubblenut posted if the only error with your previous query is that it removes carts that don't have a product, and then it doesn't matter what relationship it is between the tables.