CODEWITHSUNDEEP
mysqlsqlpdo
Yamaha32088
Yamaha32088

Reputation: 4163

Using JOIN to select multiple tables

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?

Upvotes: 0

Views: 72

Answers (2)

sean
sean

Reputation: 1205

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

Upvotes: 2

Manuel Calles
Manuel Calles

Reputation: 185

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

Upvotes: 2

Related Questions