CODEWITHSUNDEEP
sqldatabasejoininner-join
Sean Robbins
Sean Robbins

Reputation: 71

SQL INNER JOIN multiple tables not working as expected

I'm trying to have a better understanding of JOIN or INNER JOIN multiple tables in a SQL database.

Here is what I have:

SQL query:

SELECT *
FROM csCIDPull
INNER JOIN CustomerData ON CustomerData.CustomerID = csCIDPull.CustomerID
    INNER JOIN EMSData ON EMSData.EmsID = csCIDPull.EmsID
;

This returns NO results, if I remove the INNER JOIN EMSData section, it provides the info from CustomerData and csCIDPull tables. My method of thinking may be incorrect. I have let's say 5 tables all with a int ID, those ID's are also submitting to a single table to combine all tables (the MAIN table contains only ID's while the other tables contain the data).

Figured I'd shoot you folks posting to see what I might be doing wrong. -Thanks

Upvotes: 2

Views: 17910

Answers (3)

sgeddes
sgeddes

Reputation: 62831

Basically it sounds like you don't have matching data in your EMSData table. You would need to use an OUTER JOIN for this:

SELECT *
FROM csusaCIDPull
    LEFT JOIN CustomerData ON CustomerData.CustomerID = csCIDPull.CustomerID
    LEFT JOIN EMSData ON EMSData.EmsID = csCIDPull.EmsID

A Visual Explanation of SQL Joins

Side note: consider not returning * but rather select the fields you want from each table.

Upvotes: 1

Goutam Pal
Goutam Pal

Reputation: 1763

enter image description here

Check this about the SQL joins

Upvotes: 12

TGH
TGH

Reputation: 39248

It's too much to cover outer join vs inner join in this answer, but I would start by studying the difference between the two:

Here's a brief starter:

The inner join requires that you have data that satisfies the join criteria. If you for some reason don't have any records in EMSData and csCIDPull with a common EmsID - no records will be returned at all. If that's the case, you should consider using an outer join instead if you still want records from CustomerData to be returned independently of the EMDData join

Upvotes: 0

Related Questions