Jump to content

Recommended Posts

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!

 

Link to comment
https://forums.phpfreaks.com/topic/188004-join-query/
Share on other sites

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

 

 

Link to comment
https://forums.phpfreaks.com/topic/188004-join-query/#findComment-992736
Share on other sites

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

Link to comment
https://forums.phpfreaks.com/topic/188004-join-query/#findComment-992778
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/188004-join-query/#findComment-992781
Share on other sites

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!

Link to comment
https://forums.phpfreaks.com/topic/188004-join-query/#findComment-993114
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.