• 0

[SQL] Problem with Joins and Foreign Keys


Question

I am having some trouble getting my data out of my MSSQL database. I have three tables (Contact, ContactInfo, and ContactType). When I execute the query below I only get values back it has a reference in ContactInfo. Is there a way to get all the data out of Contact even if there is no reference in ContactInfo. I have attached a screen capture of my database diagram to help.

SELECT	 Contact.*, ContactInfo.*, ContactType.*
FROM		 Contact INNER JOIN
					  ContactInfo ON Contact.ContactID = ContactInfo.ContactID INNER JOIN
					  ContactType ON ContactInfo.ContactTypeId = ContactType.ContactTypeId

post-236909-1256172411_thumb.jpg

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0
I am having some trouble getting my data out of my MSSQL database. I have three tables (Contact, ContactInfo, and ContactType). When I execute the query below I only get values back it has a reference in ContactInfo. Is there a way to get all the data out of Contact even if there is no reference in ContactInfo. I have attached a screen capture of my database diagram to help.

SELECT	 Contact.*, ContactInfo.*, ContactType.*
FROM		 Contact INNER JOIN
					  ContactInfo ON Contact.ContactID = ContactInfo.ContactID INNER JOIN
					  ContactType ON ContactInfo.ContactTypeId = ContactType.ContactTypeId

You can use LEFT or RIGHT JOIN instead of INNER JOIN.

SELECT	 Contact.*, ContactInfo.*, ContactType.*
FROM	 Contact LEFT JOIN
		 ContactInfo ON Contact.ContactID = ContactInfo.ContactID LEFT JOIN
		 ContactType ON ContactInfo.ContactTypeId = ContactType.ContactTypeId

Link to comment
Share on other sites

  • 0

^ As he said, an INNER JOIN will only return records if the associated record exists on both sides of the join, i.e., if Contact.* and ContactInfo.*, and ContactType.* all have matching data. If either of those three tables produces null results, the entre row is ommited. Switching to a LEFT or RIGHT JOIN will resolve that for you, you just then have to decide what subset of data is important.

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.