sql - 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?

标签： sqloracleresponsys

Answer 1

为子选择命名：

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;