mysql how to join to fetch data

Question

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.

Answer 1

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);

SQL Fiddle Demo

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;

Updated SQL Fiddle Demo

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:

• Join (SQL)^{From Wikipedia}.

• Visual Representation of SQL Joins.

• Another Visual Explanation of SQL Joins.

• 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.