I have 3 tables but for now I am only worried about 2 of them. I want to select all entries from two tables. I believe this is what the JOIN statement is for. My tables are "Companies" and "Personal Info". Companies is the parent table with CompanyName being the Primary key and the Personal Info table has a Foreign key index of Company_id. What would the statement be to join the two together as one single query?
2
-
What's your current query? It's generally beneficial to at least try something before asking for help.enderland– enderland2014-01-21 20:09:28 +00:00Commented Jan 21, 2014 at 20:09
-
1$stmt3 = $DB->prepare('SELECT * FROM companies LEFT JOIN personalInfo USING (CompanyName)');Yamaha32088– Yamaha320882014-01-21 20:11:01 +00:00Commented Jan 21, 2014 at 20:11
Add a comment
|
2 Answers
I am not 100% certain of your schema but this is what you are looking for in its simplest form:
SELECT *
FROM Companies C
INNER JOIN PersonalInfo PI ON C.Company_id = PI.Company_id
The nature of the inner join will exclude rows in Companies that do not have any related PersonalInfo rows. If you would like to get all companies regardless then you would use the LEFT OUTER JOIN:
SELECT *
FROM Companies C
LEFT OUTER JOIN PersonalInfo PI ON C.Company_id = PI.Company_id
When you select * in a join it will return all rows from both tables. You could choose the columns to show by specifying them in the select:
SELECT C.CompanyName, PI.ColName1, PI.ColName2
FROM Companies C
LEFT OUTER JOIN PersonalInfo PI ON C.Company_id = PI.Company_id
2 Comments
Yamaha32088
This works but it shows the company name twice is there a way to get rid of the company name?
sean
I updated my answer, hope this gets you closer to what your looking for.
If I understood your question this is what you may need:
$stmt3 = $DB->prepare('select c.field1,c.fieldn,p.fielda,p.fieldx from companies c inner join personal_info p on c.id=p.companies_id');
Comments
