Selecting from multiple tables with conditions

[holk]

Hi

 

I'm working on a database with one table for product information and a couple of size tables. All items in the product information table has a corresponding size table. There are four different size tables, one for jeans and so forth.

 

All size tables have a column called productId with the id of the corresponding product.

 

What I want to do is to select from the database and get the product information and the available sizes for that product.

I've googled and tried a lot of different things but none have succeed. The closest I've been to was to select from all tables at the same time with a:

"SELECT * FROM productInfo, manSize, WomenSize WHERE ( productInfo.productId = $id AND manSize.productId = $id) OR ( productInfo.productId = $id AND womenSize.productId = $id )"

But of course that will give results from all three tables.

 

So to clarify, the query is supposed to select all information from the "productInfo" table, and all the information from the one and only corresponding size table.

 

Does anyone have a solution to the problem?

 

Cheers

[gizmola]

Can you provide the schema for the tables and some sample data from each? 

[holk]

Sure

Here is some sample data from productInfo, shoeSize and womenSize.

If the user wants to open the product with productId=13, mysql should select the "Peter shoes" from the productInfo table and the shoeSize row with productId=13. But before the query mysql doesn't know which size table holds the correct productId so it will have to look at both womenSize and shoeSize. How should I solve this?

 

productInfo

productId

productName

productMaterial

productDescription

productPrice

12

Kajsa Sweater

100% cotton

A sweater...

300

13

Peter shoes

Rubber sole

Green and...

600

 

shoeSize

productId

id

38

39

40

41

42

43

44

45

13

1

0

1

4

6

21

2

3

4

 

womenSize

productId

id

XS/34

S/36

M/38

L/40

XL/42

12

1

5

0

0

13

5

 

That's how the tables look

[gizmola]

Ok, I think i understand what these tables represent.  The various columns contain the inventory for each?

 

Ultimately, there is nothing in SQL that will let you bring this all together in a good way, because the design of these tables is relationally unsound.  with that said, if you outer join from productInfo to all the other tables, you will get a row that has all the columns of all the tables.  There will however, only be non Null values in the columns of the one table that has a corresponding match to the productInfo.productId column.

 

Although you didn't say, I'll assume that you are using mysql:

 

SELECT pi.*, ss.*, ws.* FROM productInfo pi
LEFT JOIN shoeSize ss ON (pi.productId = ss.ProductId AND pi.productId = 13)
LEFT JOIN womensize ws ON (pi.productId = ws.ProductId)