Join Query?

[dyluck]

Hi There.  How do i do a join query or some sort of query that only gives me results based on the fact that a certian value doesn't exist in another table only given the ID number of the first table?

 

Example:

Types of Fruits

1Apple

2Orange

3Banana

4Kiwi

 

Fruits In Stock

1Apple

2Apple

3Banana

4Banana

5Banana

6Banana

 

How can I do a query here where the result would be: Orange and Kiwi (as they doesn't appear in the second table)

 

Thanks for your help!

 

[kickstart]

Hi

 

Something like this

 

SELECT TypeOfFruit
FROM TypesOfFruitTable
LEFT OUTER JOIN FruitsInStockTable
ON TypeOfFruit.Fruit = FruitsInStockTable.Fruit
WHERE FruitsInStockTable.Fruit IS NULL

 

All the best

 

Keith

[dyluck]

Thanks very much Keith.

 

When I try to run the query in my code, it doesn't show the rest of the page...

I guess this was 1 step further.

I didn't write my question properly the first time sorry   what if there was a table that matched a type first

 

TypesOfFood

1 MEAT 

2 FRUIT

3 VEGITABLES

 

AvailableFoods

1  2 Apple

2  2 Orange

3  2 Banana

4  2 Kiwi

5  3  Brocoli

6  3  Carrot

7  1  T-Bone Steak

 

FoodsInStock

1 Apple

2 Apple

3 Banana

4 Banana

5 Banana

6 Banana

 

Given the ability to match #2 from TypesOfFood (Fruit)

 

Can I do the following? 

 

SELECT *
FROM AvailableFoods
LEFT OUTER JOIN FoodsInStockTable
ON TypesOfFood.Name = FoodsInStock.Name
WHERE TypesOfFood.TypeID = '2' AND FoodsInStock.Name IS NULL

 

 

[kickstart]

Hi

 

I think what you are trying to do is:-

 

SELECT *
FROM TypesOfFood
JOIN AvailableFoods
ON TypesOfFood.TypeID = AvailableFoods.TypeID
LEFT OUTER JOIN FoodsInStockTable
ON TypesOfFood.Name = FoodsInStock.Name
WHERE TypesOfFood.TypeName = 'FRUIT' 
AND FoodsInStock.Name IS NULL

 

If you know the TypeId then no need to use the TypesOfFood table:-

 

SELECT *
FROM AvailableFoods
LEFT OUTER JOIN FoodsInStockTable
ON TypesOfFood.Name = FoodsInStock.Name
WHERE AvailableFoods.TypeId = '2' 
AND FoodsInStock.Name IS NULL

 

Probably be better to have a FoodId rather than storing the food name in both the AvailableFoods and FoodsInStockTable tables.

 

All the best

 

Keith

[aebstract]

TypesOfFood

ID  Name

1    MEAT

2    FRUIT

3    VEGITABLES

 

AvailableFoods

ID  TypeID    Name

1    2            Apple

2    2            Orange

3    2            Banana

4    2            Kiwi

5    3            Brocoli

6    3            Carrot

7    1            T-Bone Steak

 

FoodsInStock

ID    Name

1      Apple

2      Apple

3      Banana

4      Banana

5      Banana

6      Banana

 

 

SELECT *
FROM AvailableFoods
LEFT OUTER JOIN FoodsInStockTable
ON AvailableFoods.Name = FoodsInStock.Name
WHERE AvailableFoods.TypeID = '2' AND FoodsInStock.Name IS NULL

 

Not sure what you're doing with the null part, but this will do it correctly. If I understand what you're trying to do anyway.

[kickstart]

Not sure what you're doing with the null part, but this will do it correctly.

 

The NULL check is there to exclude foods that are both on available foods and on foods in stock.

 

I presume he needs a list of items to order.

 

All the best

 

Keith

[dyluck]

Thanks kickstart and aebstract

 

Turns out this code worked for me:

 

SELECT *FROM AvailableFoodsLEFT OUTER JOIN FoodsInStockTableON AvailableFoods.Name = FoodsInStock.NameWHERE AvailableFoods.TypeID = '2' AND FoodsInStock.Name IS NULL

 

Its pretty cool what you can do with MySQL!