MySql Query to get data from 3 tables-CodePudding

Hope you doing great! I need your help to get data from 3 tables

1st table is child

2nd table is vaccines

3rd table is child_vaccines

I have stored Vaccines in vaccines table and want to show all vaccines against every single child. So child will gonna be repeat for no. of vaccine time. Vaccination status stored in child_vaccines table. I need to get all records from db but unable to do that. I would like to request you for the query which will gonna use to get record from the db according to given scenario.

Best Regards,

CodePudding user response：

Not knowing the colums in the tables, I invented them. Just personalize the query for your needs.

SELECT c.child_name, v.vaccine_name, cv.status
FROM child c, vaccines v, child_vaccines cv
WHERE c.child_id = v.child_id
AND v.vaccine_id = cv.vaccine_id;

CodePudding user response：

For get data from more than one table you need join query. Something like this

select child_name,vaccine_name from child a 
left join child_vaccines b on a.child_id 
join vaccines c on b.vaccine_id = c.vaccine_id

you can search or try Joins from w3schools

CodePudding user response：

You can use LEFT JOIN but you need to have a reference key to child tables. I do not know what are the fields but an example query should be this:

SELECT * FROM child AS C 
LEFT JOIN child_vaccines AS CV ON CV.child_id = C.id 
LEFT JOIN vaccines AS V ON V.id = CV.vaccine_id;