CODEWITHSUNDEEP
sqlsqlite
Todd Vance
Todd Vance

Reputation: 4711

I want to join tables but still receive results from one table even if the other table has no results

I'm doing this in Chrome WebDB/SQLite

I have a members table and an answer table that a member may or may not have answered.

I am TRYING to get back a result set that includes the member name and answer they gave.. BUT if they didnt give an answer, I still want their name to come back.

Can someone try and help me on this? 

SELECT m.LastName, 
       m.FirstName, 
       m.MiddleName, 
       s.surveyAnswer 
FROM Members m 
    LEFT JOIN SurveyAnswers s 
      ON s.memberID = m.id 
WHERE m.id = ? 
AND s.eventID = ?

[results.rows.item(i).memberID, eventId],

This is what I try but still only receive the members who answered a question and is also in the SurveyAnswers table.

Upvotes: 0

Views: 68

Answers (4)

soupy1976
soupy1976

Reputation: 2845

You need to use a LEFT OUTER join, and you also need to specify the condition for SurveyAnswers eventID in the join, not in the where clause. Something like this:

SELECT m.LastName, m.FirstName, m.MiddleName, s.surveyAnswer FROM Members m 
LEFT OUTER JOIN SurveyAnswers s ON (s.memberID = m.id AND s.eventID = ?) 
WHERE m.id = ?

Upvotes: 1

AshBestos
AshBestos

Reputation: 302

Joins were recently explained to me in the form of a venn diagram and since then I've been crystal clear on which joins to use in my queries.

Have a look at the page linked below which explains it in the same way and I promise you'll never need to check which join you need again!

visual explanation of SQL joins

Upvotes: 1

Matt Glover
Matt Glover

Reputation: 1347

Make s.eventID part of the join rather than the WHERE clause.

Your select statement should look more like this:

SELECT m.LastName, 
       m.FirstName, 
       m.MiddleName, 
       s.surveyAnswer 
FROM Members m 
    LEFT JOIN SurveyAnswers s 
      ON s.memberID = m.id 
        AND s.eventID = ? 
WHERE m.id = ?

Upvotes: 2

Jason
Jason

Reputation: 89127

You want to use a LEFT OUTER JOIN.

Upvotes: 0

Related Questions