CODEWITHSUNDEEP
mysqlsqljoin
josh
josh

Reputation: 10368

Display row or NULL in SQL query

I'm attempting to get information from three tables in one single query. I don't know if it's possible to do this... what I've tried so far hasn't worked but I also am not that knowledgeable with SQL.

I have three tables: registration, which holds registration information, experience, which holds the event selections for the customer (if they chose a dinner, lunch, single day, etc.), and additionalInfo, which holds additional event info such as their choices for specific workshops during the event. registration and experience will always have values, but additionalInfo will not be guaranteed to have values. What I want to get is, in a single query, to get the registration and event selections, and if there is something in additionalInfo to retrieve that, or return NULL.

I am trying this query:

SELECT registration.firstName, registration.lastName, registration.phone, experience.options, additionalInfo.workshops
FROM registration, experience, additionalInfo
WHERE registration.identifier = experience.identifier
AND experience.paid = 'Yes'
AND registration.eventName = 'march2013'
AND additionalInfo.identifier = registration.identifier
ORDER BY registration.lastName ASC

This query only returns values where additionalInfo.identifier exists, but as I said this is not guaranteed to exist.

Upvotes: 0

Views: 75

Answers (2)

Sri
Sri

Reputation: 29

Apart from using Left Join

You can also check if the database resultset returned has

resultset.eof

I always use this to check if the database returned anything

Upvotes: 0

John Woo
John Woo

Reputation: 263943

use LEFT JOIN

SELECT  registration.firstName, 
        registration.lastName, 
        registration.phone, 
        experience.options, 
        additionalInfo.workshops
FROM    registration
        INNER JOIN experience
            ON registration.identifier = experience.identifier
        LEFT JOIN additionalInfo
            ON additionalInfo.identifier = registration.identifier
WHERE   experience.paid = 'Yes' AND 
        registration.eventName = 'march2013'
ORDER   BY registration.lastName ASC

To further gain more knowledge about joins, kindly visit the link below:

Upvotes: 5

Related Questions