Possible Duplicate:
How can an SQL query return data from multiple tables
I have 3 table
Attribute
attr_id | attr_name
1 | oval<
2 | white
Product
product_id|product_name
1 | amazonite
2 | agate
attr_detail
attr_detail_id | attr_id | product_id
1 | 1 | 1
2 | 2 | 1
3 | 1 | 2
4 | 2 | 2
Now I want product those are oval shape and white color. How to use join.
Try this:
SELECT DISTINCT
p.product_id,
p.product_name
FROM Product p
INNER JOIN attr_detail ad ON p.product_id = ad.product_Id
INNER JOIN Attribute a ON a.attr_id = ad.attr_id
WHERE a.attr_id IN(1, 2);
This will give you:
| PRODUCT_ID | PRODUCT_NAME |
---------------------------------------
| 1 | amazonite |
| 2 | agate |
| 3 | Product has only white |
Note that: This will give you the products names that have either the white or oval shapes. However, if you are looking for only those products that has both the two shapes, you have to modify your query like so:
SELECT DISTINCT
p.product_id,
p.product_name
FROM Product p
INNER JOIN
(
SELECT product_id
FROM attr_detail
WHERE attr_id IN(1, 2)
GROUP BY product_id
HAVING COUNT(DISTINCT attr_id) = 2
) ad ON p.product_id = ad.product_Id;
This will give you:
| PRODUCT_ID | PRODUCT_NAME |
-----------------------------
| 1 | amazonite |
| 2 | agate |
Please read more about JOINs. Here you can find a useful information about this:
SQL Queries for Mere Mortals(R): A Hands-On Guide to Data Manipulation in SQL, a great book for SQL Basics.
For what I did in the second query:
SELECT product_id
FROM attr_detail
WHERE attr_id IN(1, 2)
GROUP BY product_id
HAVING COUNT(DISTINCT attr_id) = 2
This is called Relational Division.
MySQL JOIN.