CODEWITHSUNDEEP
phpmysqlsqljoinsubquery
Raikumar Khangembam
Raikumar Khangembam

Reputation: 1008

how to join different tables?

i tried searching related query statement and applied but not work.

i am trying to join all tables for my api in one GET call and i am not able to perform it. query failed and get errors.

My query looks like this for the table i have shown. 

SELECT projects.*, images.*, rewards.*, shipping.* 
FROM projects 
LEFT JOIN rewards ON rewards.projects_id = projects.id 
LEFT INNER JOIN rewards ON rewards.id = shipping.reward_id 
LEFT JOIN images ON projects.id = images.project_id 
WHERE projects.id = 1

enter image description here

i want to join rewards and shipping with all table, project to rewards has relationship of one to many and rewards to shipping also one to many. How can i combine in all this in one query?? i am also trying sub queries but i have no idea how to make the queries in these table. Please explain me how would it work in sub-queries or in join? If it is not good practice please explain me others technique also.

UPDATE

i tried it like this using subquery it said unknown column shipping

SELECT projects.*, rewards.*, images.image, shipping = (SELECT *
FROM shipping
WHERE shipping.reward_id IN (SELECT * FROM rewards WHERE rewards.project_id = 1)) 
FROM projects JOIN rewards ON rewards.project_id = projects.id 
LEFT JOIN images ON projects.id = images.project_id 
WHERE projects.id = 1

Upvotes: 0

Views: 86

Answers (1)

trincot
trincot

Reputation: 350310

Your query has several syntax issues. To join in all tables, you would write:

SELECT    projects.title, rewards.price, images.images, shipping.shipping
FROM      projects
LEFT JOIN images ON images.project_id = projects.id
LEFT JOIN rewards ON rewards.project_id = projects.id
LEFT JOIN shipping ON shipping.rewards_id = rewards.id
WHERE     projects.id = 1

... but be aware that if you have many images and many rewards for the same project, this query will produce all the combinations of images and rewards, because your database schema does not define which image belongs with which reward.

Upvotes: 1

Related Questions