Multiple database searches
I want to get records from a database in the current project.
I have 5 tables; Each makes a profile, which is stored at the beginning of the four tables, then defines a partner profile. I want to get all the partner name that matches the profile.
This is my table structure:
Basic Info (reg_id, name, gender, dob, martial_status)
Education (id,reg_id,education,college)
Location (id,reg_id,country,state, city)
Other_details (id,reg_id,height, weight)
Partner (id, reg_id, gender, dob, education, college, country, state, city, height, weight,martial_status) [ This is the Main Table]..
So far I have tried this but no luck:
SELECT `basic_info`.`reg_id`,
`basic_information`.`dob`,
`other_detail`.`height`,
`location`.`city`,
`education`.`education`
FROM `basic_information`
INNER JOIN(
SELECT *
FROM `patner`
WHERE `patner`.`reg_id`='shi01') `patner`
ON `basic_information`.`martial_status`=`partner`.`martial_status`
AND `basic_information`.`reg_id`!='shi01'
INNER JOIN `education`
ON `patner`.`education`=`education`.`education`
AND `patner`.`education`=`education`.`education`
INNER JOIN `location`
ON `patner`.`city`=`location`.`city`
INNER JOIN `other_detail`
ON `patner`.`bodytype`=`other_detail`.`body_type`
AND `patner`.`skin`=`other_detail`.`skin`
AND `patner`.`height1` <= `other_detail`.`height`
GROUP BY `basic_information`.`reg_id`;
+3
source to share
1 answer
You made mistakes while writing the connection conditions. I tried to fix it. Try this, it should work;
SELECT BI.reg_id, BI.name
FROM basic_info BI
INNER JOIN
Education E ON BI.reg_id = E.reg_id
INNER JOIN
Location L ON BI.reg_id = L.reg_id
INNER JOIN
Other_details OD ON BI.reg_id = OD.reg_id
INNER JOIN
(Select reg_id, martial_status, height, education, city FROM Partner where reg_id = 'Your_ID') P
ON BI.reg_id = P.reg_id
WHERE
BI.marital_status = P.marital_Status
AND E.Education = P.Education
AND L.city = P.City
AND OD.height <= P.Height
AND BI.reg_id <> 'Your_ID';
0
source to share