CODEWITHSUNDEEP
sqljoin
Matthew Lowe
Matthew Lowe

Reputation: 5

SQL Join - Show data if a joined table doesn't hold any data

I have a query that joins 3 tables together. One is a SPAAPPOINTMENTS table - (Spa appointments with price charged in) The second is SPAAPTADDONS - Addons for the appointments (Nibbles etc), Links between apts and rst The third is RSTCATALOG table - Where the addon price is located.

The query:

SELECT        SPAAPPOINTMENTS.APTID, SPAAPPOINTMENTS.SERVICECODE, SPAAPPOINTMENTS.LOCATIONCODE, SPAAPPOINTMENTS.STAFFCODE, 
                         SPAAPPOINTMENTS.BLOCKSTARTTIME, SPAAPPOINTMENTS.BLOCKENDTIME, SPAAPPOINTMENTS.I_RECID, SPAAPPOINTMENTS.S_STAYID, 
                         SPAAPPOINTMENTS.STATUSCODE, SPAAPPOINTMENTS.BLOCKEDTIME, SPAAPPOINTMENTS.GRATUITY, SPAAPPOINTMENTS.TEMPHOLDID, 
                         SPAAPPOINTMENTS.LASTCHGID, SPAAPPOINTMENTS.CANCELID, SPAAPPOINTMENTS.ITEMNO, SPAAPPOINTMENTS.COMMENTS, 
                         SPAAPPOINTMENTS.OFFSITELOCATION, SPAAPPOINTMENTS.PRICE, SPAAPPOINTMENTS.PRIORITY, SPAAPPOINTMENTS.SPAPKGCODE, 
                         SPAAPPOINTMENTS.ENFORCEGENDER, SPAAPPOINTMENTS.NEWSTATUS, SPAAPPOINTMENTS.RESTNO, SPAAPPOINTMENTS.DONOTMOVE, 
                         SPAAPPOINTMENTS.NOSHOW, SPAAPPOINTMENTS.OTHERGENDERREQUESTED, SPAAPPOINTMENTS.MADEBY, SPAAPPOINTMENTS.LINKCODE, 
                         SPAAPPOINTMENTS.CONFIRMED, SPAAPPOINTMENTS.CXLNUMBER, SPAAPPOINTMENTS.REQUEST, SPAAPPOINTMENTS.CUSTOMFIELD1, 
                         SPAAPPOINTMENTS.CUSTOMFIELD2, SPAAPPOINTMENTS.CUSTOMFIELD3, SPAAPPOINTMENTS.VIP, SPAAPPOINTMENTS.NOPREFERENCE, 
                         SPAAPPOINTMENTS.CUSTOMFIELD4, SPAAPPOINTMENTS.CUSTOMFIELD5, SPAAPPOINTMENTS.BOOKID, SPAAPPOINTMENTS.ARTSRESVNO, 
                         SPAAPPOINTMENTS.LMSCONFNO, SPAAPPOINTMENTS.ARTSGUESTID, SPAAPPOINTMENTS.CLUBNO, SPAAPPOINTMENTS.MULTIGRPID, 
                         SPAAPPOINTMENTS.MAINCONTACT, SPAAPPOINTMENTS.SPABKNAME, SPAAPPOINTMENTS.MULTIGRPAPPTXT, SPAAPPOINTMENTS.CONFCOMMENTS, 
                         SPAAPPOINTMENTS.MEMCODE, SPAAPPOINTMENTS.FAMILYMEMTYPE, SPAAPPOINTMENTS.COCOMMENTS, SPAAPPOINTMENTS.GRPMASTERID, 
                         SPAAPPOINTMENTS.GUESTTYPE, SPAAPPOINTMENTS.EXTUNIQUEID, SPAAPPOINTMENTS.SETUPTIME, SPAAPPOINTMENTS.BREAKDOWNTIME, 
                         SPAAPPOINTMENTS.PSBASEPRICE, SPAAPPOINTMENTS.PSCODE, SPAAPPOINTMENTS.PSCALENDARADJPCT, SPAAPPOINTMENTS.PSDETAILADJ, 
                         SPAAPPOINTMENTS.PSPRICE, SPAAPPOINTMENTS.PSDETAILADJSOURCE, SPAAPPOINTMENTS.PSDETAILADJTYPE, SPAAPPOINTMENTS.PSPRICETYPE, 
                         RSTCATALOG.C_DPRICE
FROM  SPAAPPOINTMENTS 
  INNER JOIN SPAAPPTADDONS ON SPAAPPOINTMENTS.APTID = SPAAPPTADDONS.APTID 
  INNER JOIN RSTCATALOG ON SPAAPPTADDONS.ITEMNO = RSTCATALOG.C_ITEM

This returns all data from the appointments table AS LONG AS THEY HAD A ADDON to the Spa Appointment.

If they just had an appointment, and no Add on, it doesn't return the data. For example, if i use the query and postfix it with: WHERE (SPAAPPOINTMENTS.APTID = 626746)

It doesn't show any data, but there is a line in spaappopintments but NOTHING in the addons table.

If I use anopther AptID, which HAS HAD AN ADDON, it shows the data and is output from the query.

How can I get to show the data if there is NO data in the SPAAPTADDONS table please anyone?

Upvotes: 0

Views: 150

Answers (3)

Rahul
Rahul

Reputation: 77876

Change the below INNER JOIN line

INNER JOIN SPAAPPTADDONS ON SPAAPPOINTMENTS.APTID = SPAAPPTADDONS.APTID
INNER JOIN RSTCATALOG ON SPAAPPTADDONS.ITEMNO = RSTCATALOG.C_ITEM

To an LEFT OUTER JOIN like

LEFT JOIN SPAAPPTADDONS ON SPAAPPOINTMENTS.APTID = SPAAPPTADDONS.APTID
LEFT JOIN RSTCATALOG ON SPAAPPTADDONS.ITEMNO = RSTCATALOG.C_ITEM

Upvotes: 1

jpw
jpw

Reputation: 44881

When you join tables together you specify a left and a right table. An inner join will produce a set where the join condition is true for both the left and right side.

What you want is to get a set that has all the data from the left table along with the matching data from the right side. This is a left join.

But, as you also want to get the pricing information for the addons you need to join the third table too. As we can assume that there is a one-to-one matching between the addons and the pricing table it looks like we could use an inner join here, but this won't work as the joins are performed in order, meaning that last join will use the result from the first join as its left side (and that set will have null values for the appointments without any addons) - if we try to do an inner join with the pricing table those rows would be excluded, so you need a left join here too.

In the end you want this:

FROM SPAAPPOINTMENTS 
LEFT JOIN SPAAPPTADDONS ON SPAAPPOINTMENTS.APTID = SPAAPPTADDONS.APTID 
LEFT JOIN RSTCATALOG    ON SPAAPPTADDONS.ITEMNO  = RSTCATALOG.C_ITEM

On a side note I would encourage you to use table aliases to make the query a bit shorter and more readable. Something like this:

FROM SPAAPPOINTMENTS    apt
LEFT JOIN SPAAPPTADDONS add ON apt.APTID  = add.APTID 
LEFT JOIN RSTCATALOG    rst ON add.ITEMNO = rst.C_ITEM

Upvotes: 1

AlexanderW
AlexanderW

Reputation: 147

FROM  SPAAPPOINTMENTS 
LEFT JOIN SPAAPPTADDONS ON SPAAPPOINTMENTS.APTID = SPAAPPTADDONS.APTID 
LEFT JOIN RSTCATALOG ON SPAAPPTADDONS.ITEMNO = RSTCATALOG.C_ITEM

let all data from SPAAPPOINTMENTS to be in the result.

Upvotes: 1

Related Questions