Question 212806 in Mysql Question posted in
The official MySQL documentation can be found here.

join in mysql between two table with on to many relation

i have two table like this:

phones

id name price
1 a20 100
2 a30 200

models:

id phone_id model
1 1 a20-128GB
2 1 a20-256GB
3 2 a30-128GB
4 2 a30-256GB
5 2 a30-512GB

i want to write SQL to give result like this:

id name price model
1 a20 100 a20-128GB
2 a20 100 200-256GB
3 a30 200 100-128GB
4 a30 200 200-256GB
5 a30 200 200-512GB

i try left join but cant success
i try somthing like this:

SELECT `phones`.* , `models`.`phone_id` , `models`.`model` 
FROM `phones` 
LEFT JOIN `models` 
ON `phones`.id = `models`.`phone_id`

but model column retunr null

Tags:

2

Answers


  1. 0

    This query should work for you:

    SELECT m.id, p.name, p.price, m.model
FROM phones p
INNER JOIN models m ON p.id = m.phone_id;
    Login or Signup to reply.
  2. 0 
    SELECT p.id, p.name, p.price, m.model
FROM phones p
CROSS JOIN models m
WHERE m.phone_id = p.id
ORDER BY p.id, m.id;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search