Public feed
#380488
I've got two tables:
Table1:
+---------+-------+
| Name | Color |
+---------+-------+
| Fruit | Many |
| Apple | Green |
| Orange | Orange|
| Drink | Many |
| Beer | Brown |
| Cola | Black |
+---------+-------+
Table2:
+---------+-------+
| Name | Type |
+---------+-------+
| Apple | Fruit |
| Orange | Fruit |
| Beer | Drink |
| Cola | Drink |
+---------+-------+
Now I am trying to get all of the same type as a given name. For example if I do a select on Apple, Orange or Fruit I want returned
Return list 1:
+---------+-------+--------+
| Name | Type | Color |
+---------+-------+--------+
| Apple | Fruit | Green |
| Orange | Fruit | Orange |
| Fruit | Fruit | Many |
+---------+-------+--------+
And if I do a select on Dring, Beer or Cola I want returned
Return list 2:
+---------+-------+--------+
| Name | Type | Color |
+---------+-------+--------+
| Drink | Drink | Many |
| Beer | Drink | Brown |
| Cola | Drink | Black |
+---------+-------+--------+
I have tried subselects in where clauses and joins left, right and inner but I can't get it right. The closest I got was:
SELECT Table1.Name, Color, Type from Table1 LEFT JOIN Table2 on Table1.Name = Table2.Name WHERE Table2.Type="Drink";
This gave me
+---------+-------+--------+
| Name | Type | Color |
+---------+-------+--------+
| Drink | Drink | Many |
| Drink | Drink | Brown |
| Drink | Drink | Black |
+---------+-------+--------+
Which is close, but no sigar.