CODEWITHSUNDEEP
phpmysql
hunter
hunter

Reputation: 1101

retrieve data from 4 tables

enter image description here

I have these four table assume that ring table consist of five fields(jewelry_id,ring_id,image,type,brand_id)

Note that brand table have its foreign key in ring table and ring and style both have foreign keys in ring_style table. now i want to retrieve the following data from these four table (ring_id, image,type, brand,style) but did't get the query any help will be greatly appreciated.

Upvotes: 0

Views: 117

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272106

SELECT
    ring_id, image, type,
    brand,
    style
FROM      ring
LEFT JOIN brand ON ring.brand_id = brand.brand_id
LEFT JOIN ring_style ON ring.jewelry_id = ring_style.jewelry_id
     LEFT JOIN style ON ring_style.style_id = style.style_id

Note that each ring will appear one or more times. It will appear more than one time when there is more than one ring_style record for the ring.

Upvotes: 1

Dan Grossman
Dan Grossman

Reputation: 52372

You just join each table using the columns that relate them.

SELECT
  ring.ring_id,
  style.image,
  ring.type,
  brand.brand,
  style.style
FROM
  brand
INNER JOIN
  ring
ON
  brand.brand_id = ring.brand_id
INNER JOIN
  ring_style
ON
  ring.jewelry_id = ring_style.jewelry_id
INNER JOIN
  style
ON
  ring_style.style_id = style.style_id

Upvotes: 2

powtac
powtac

Reputation: 41050

You have to use the SQL command JOIN:

An example to get the information from ring and brand with one query use:

SELECT * FROM ring 
JOIN brand ON ring.brand_id = brand.brand_id;
WHERE ring.jewelry_id = 123456

Use multiple JOIN in one query to get multiple tables connected in one query.

Upvotes: 1

Related Questions