Can you have multiple select statemets on a single table then join them?

Question

Please note that I'm not a developer, so any help here would be greatly appreciated.

My problem: I have list in Oracle Responsys that contains all customers booked on a tour. I want to find all records that have missing values for FIRST_NAME (could be Tba/Tbc/Null), so I can send the main booker an email requesting that they update the name details for their additional guests.

The main booker will always have a CUSTOMER_ID_ value ending in '-1'. The records with missing first name values won't.

What I want to do, is select all records from the list that have missing first name values, group them by BOOKING_LOCATOR (a value that is shared by the main booker and their additional guests) - THEN only return the main bookers (records with CUSTOMER_ID_ ending in '-1'.

So far, I've pieced together this rough idea of what Im after from looking at other posts, but it doesn't return anything like what I'm after:

SELECT $A$.BOOKING_LOCATOR, $A$.CUSTOMER_ID_, $A$.EMAIL_ADDRESS_
FROM
(
  SELECT $A$.BOOKING_LOCATOR FROM $A$ WHERE ($A$.FIRST_NAME IN ('TBA', 'TBC', 'Tba', 'Tbc') OR $A$.FIRST_NAME IS NULL) AND $A$.POLAR_BOOKING_STATUS != 'C' AND $A$.BOOKING_STATUS != 'Waitlist' AND $A$.EMBARK_DATE >= SYSDATE+1
  GROUP BY $A$.BOOKING_LOCATOR
) $A$
INNER JOIN (
  SELECT $A$.BOOKING_LOCATOR AS BOOKINGNUM, $A$.CUSTOMER_ID_, $A$.EMAIL_ADDRESS_ FROM $A$ WHERE $A$.CUSTOMER_ID_ LIKE '%-1'
) $A$ ON $A$.BOOKING_LOCATOR = $A$.BOOKINGNUM

Can someone guide me in the right direction?

I would name the second table like `$B$` . I'm surprised your SQL even works. — Adder, May 24 '19 at 08:14

score 2 · Accepted Answer · answered May 24 '19 at 08:24

Give a name to the sub-selects:

SELECT A.BOOKING_LOCATOR, B.CUSTOMER_ID_, B.EMAIL_ADDRESS_
FROM
(
  SELECT $A$.BOOKING_LOCATOR FROM $A$ WHERE ($A$.FIRST_NAME IN ('TBA', 'TBC', 'Tba', 'Tbc') OR $A$.FIRST_NAME IS NULL) AND $A$.POLAR_BOOKING_STATUS != 'C' AND $A$.BOOKING_STATUS != 'Waitlist' AND $A$.EMBARK_DATE >= SYSDATE+1
  GROUP BY $A$.BOOKING_LOCATOR
) A
INNER JOIN (
  SELECT $A$.BOOKING_LOCATOR AS BOOKINGNUM, $A$.CUSTOMER_ID_, $A$.EMAIL_ADDRESS_ FROM $A$ WHERE $A$.CUSTOMER_ID_ LIKE '%-1'
) B ON A.BOOKING_LOCATOR = B.BOOKINGNUM;

Thanks, Adder - this solved my issue - can't believe I missed that. — Bently411, May 25 '19 at 01:24

score 1 · Answer 2 · answered May 24 '19 at 09:38

YEAH,you can do it.You can either use Joins OR OUTER APPLY for this.

SELECT A.BOOKING_LOCATOR, B.CUSTOMER_ID_, B.EMAIL_ADDRESS_
FROM
(
 SELECT $A$.BOOKING_LOCATOR FROM $A$ WHERE ($A$.FIRST_NAME IN ('TBA', 'TBC', 'Tba', 
'Tbc') OR $A$.FIRST_NAME IS NULL) AND $A$.POLAR_BOOKING_STATUS != 'C' AND 
$A$.BOOKING_STATUS != 'Waitlist' AND $A$.EMBARK_DATE >= SYSDATE+1
GROUP BY $A$.BOOKING_LOCATOR
) A
INNER JOIN (
SELECT $A$.BOOKING_LOCATOR AS BOOKINGNUM, $A$.CUSTOMER_ID_, $A$.EMAIL_ADDRESS_ FROM 
$A$ WHERE $A$.CUSTOMER_ID_ LIKE '%-1'
) B ON A.BOOKING_LOCATOR = B.BOOKINGNUM;

--2ND option is by using OuterAPPLY.

SELECT A.BOOKING_LOCATOR, B.CUSTOMER_ID_, B.EMAIL_ADDRESS_
FROM
(
  SELECT $A$.BOOKING_LOCATOR FROM $A$ WHERE ($A$.FIRST_NAME IN ('TBA', 'TBC', 'Tba', 
 'Tbc') OR $A$.FIRST_NAME IS NULL) AND $A$.POLAR_BOOKING_STATUS != 'C' AND 
$A$.BOOKING_STATUS != 'Waitlist' AND $A$.EMBARK_DATE >= SYSDATE+1
 GROUP BY $A$.BOOKING_LOCATOR
) A
 Outer APPLY(
   SELECT $A$.BOOKING_LOCATOR AS BOOKINGNUM, $A$.CUSTOMER_ID_, $A$.EMAIL_ADDRESS_ FROM 
$A$ WHERE $A$.CUSTOMER_ID_ LIKE '%-1'
) B ON A.BOOKING_LOCATOR = B.BOOKINGNUM;

Many thanks for your solution, GraySkull. As above, I only voted Adder as the winner due to the answer pointing out my error. — Bently411, May 25 '19 at 01:31

score 1 · Answer 3 · answered May 24 '19 at 11:17

If you want the booking locating and main customer, then EXISTS comes to mind:

SELECT . . .   -- whatever columns you want
FROM $A$ a1
WHERE a.CUSTOMER_ID_ LIKE '%-1' AND
      EXISTS (SELECT 1
              FROM $A$ a
              WHERE a.FIRST_NAME IN ('TBA', 'TBC', 'Tba', 'Tbc') OR a.FIRST_NAME IS NULL) AND  
                    a.POLAR_BOOKING_STATUS <> 'C' AND  
                    a.BOOKING_STATUS <> 'Waitlist' AND  
                    a.EMBARK_DATE >= SYSDATE+1 AND
                    a.BOOKING_LOCATOR = a1.BOOKING_LOCATOR
              );

This returns only one row per main customer.

Thank you, Gordon - this was an elegant solution, albeit hard for a beginner like me to grasp. It worked. The only reason I didn't vote you as the winner, was because Adder pointed out the simple error that I made in my original code, providing a working solution. — Bently411, May 25 '19 at 01:28

Can you have multiple select statemets on a single table then join them?

3 Answers3