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?

select 
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 
) 
AND 
churchTb.c_ID= 
( 
 select personChurch.c_ID 
 from personChurch 
 where personChurch.p_ID=personTb.p_ID 
);

/ next is my attempt at a join /

SELECT 
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
);

Recommended Answers

All 3 Replies

You don't need use subqueries

select
     a.`p_ID`
    ,a.`p_fName`
    ,a.`p_mName`
    ,a.`p_lName`
    ,b.`a_ID`
    ,b.`a_street`
    ,b.`a_box`
    ,b.`a_city`
    ,c.`c_ID`
    ,c.`c_name`
    ,c.`c_city`
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`
;

or

select
     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

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?

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 developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.