CODEWITHSUNDEEP
sqlsql-server
Xohair Shah
Xohair Shah

Reputation: 29

Select records from 2 tables based on 3rd mapping table

I have 3 tables with the below Structure.
Company - table 1 

Id  
Name  
Phone

Contact - table 2 

Id  
FirstName  
LastName  
CellPhone  
Phone  
Phone2  
Email1

Company_Contact - table 3 

CompanyId  
ContactId

One company can have multiple contacts. Now i want to Select Contact.FirstName, Contact.LastName, Company.Name, Contact.CellPhone, Contact.Phone, Contact.Phone2, Company.Phone, Contact.Email1 for all companies

Upvotes: 1

Views: 487

Answers (3)

Simão Lemos
Simão Lemos

Reputation: 1239

First, you need to know the fields that you want to select and relate them

SELECT Contact.FirstName, Contact.LastName, Company.Name, Contact.CellPhone,
Contact.Phone, Contact.Phone2, Company.Phone, Contact.Email1 
FROM contact, company, company_contact 
WHERE company.id = Company_Contact.companyId and contact.id = company_contact.contactId

I think that's what u want maybe there are errors, but this is the logic of the question

Upvotes: 2

Jos Luijten
Jos Luijten

Reputation: 679

Something like

SELECT Contact.FirstName, Contact.LastName, Company.Name, Contact.CellPhone, Contact.Phone, Contact.Phone2, Company.Phone, Contact.Email1  
FROM Company, Contact, Company_Contact 
WHERE Company_Contact.CompanyId = Company.Id AND Company_Contact.ContactId = Contact.Id

Upvotes: 0

Oto Shavadze
Oto Shavadze

Reputation: 42793

You need simple join

select Contact.FirstName, Contact.LastName, Company.Name, Contact.CellPhone, Contact.Phone, Contact.Phone2, Company.Phone, Contact.Email1 
from Company 
inner join Company_Contact on Company.id = Company_Contact.CompanyId  
inner join Contact on Contact.id = Company_Contact.ContactId

Upvotes: 2

Related Questions