there are five tables (personTb, addressTb,churchTb) each holds data pertinent to an individual; (personAddress,personChurch) each hold the primary key for the person table and corresponding table IE personAddress holds primary key for addressTb. ​

both snippets work, my question is; Is my join correctly formatted?

concat(personTb.p_fName,' ',personTb.p_mName,'. ',personTb.p_lName) AS Name,
concat(addressTb.a_street,' ',addressTb.a_box,' ',addressTb.a_city) AS Address, 
concat(churchTb.c_name,' ',churchTb.c_city) AS Church 
from personTb, addressTb, churchTb 
where addressTb.a_ID= 
 select personAddress.a_ID 
 from personAddress 
 where personAddress.p_ID=personTb.p_ID 
 select personChurch.c_ID 
 from personChurch 
 where personChurch.p_ID=personTb.p_ID 

/ next is my attempt at a join /

concat(a.p_fName,' ',a.p_mName,'. ',a.p_lName) AS Name,
concat(b.a_street,' ',b.a_box,' ',b.a_city) AS Address, 
concat(c.c_name,' ',c.c_city) AS Church 
FROM personTb a
INNER JOIN addressTb b
ON b.a_ID=
    SELECT d.a_ID
    FROM personAddress d
    WHERE d.p_ID=a.p_ID
INNER JOIN churchTb c
ON c.c_ID=
    SELECT e.c_ID
    FROM personChurch e
    WHERE e.p_ID=a.p_ID
Re: SQL JOIN on five tables 80 80

You don't need use subqueries

from `personTb` a
inner join `personAddress` b on b.`p_ID` = a.`p_ID`
inner join `personChurch` c on c.`p_ID` = a.`p_ID`


     concat_ws(' ', a.`p_fName`, a.`p_mName`, a.`p_lName`) as p_Name
    ,concat_ws(' ', b.`a_street`, b.`a_box`, b.`a_city`) as p_Address
    ,concat_ws(' ', c.`c_name`, c.`c_city`) as p_Church
from `personTb` a
inner join `personAddress` b on b.`p_ID` = a.`p_ID`
inner join `personChurch` c on c.`p_ID` = a.`p_ID`;

... and using MySQL keywords like "name" for column names or aliases is not good practice

Re: SQL JOIN on five tables 80 80

Also, you shouldn't need to use suffixes to identify object types. Tb is already inconsistent; your link tables don't have it, but what if you add a link table with another attribute?

Re: SQL JOIN on five tables 80 80

And anyone who does a join on 5 tables deserves what they get - likely zero results!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.