tags:

views:

43

answers:

4

I have this query which works perfectly:

SELECT *
FROM Customer
WHERE SacCode IN
(
    SELECT SacCode
    FROM SacCode
    WHERE ResellerCorporateID = 392
    ORDER BY SacCode
)
AND CustomerID IN
(
    SELECT CxID
    FROM CustAppointments
    WHERE AppRoomID IN
    (
     SELECT AppRoomID
     FROM ClinicRooms
     WHERE ClinID IN
     (
      SELECT ClinID
      FROM AppClinics
      WHERE ClinDate >='20090101'
      AND ClinDate <='20091119'
     )
    )
)

However, I need to see the value of ClinDate (inside the last nested query)...

How do I do it?

Thanks.

A: 

Think I'd use derived table in the FROM statement rather than 3 deep nested query, will allow you to access values and will look a LOT better.

Fermin
How would I do that?
Oliver
A: 

You'll need to copy the subselects to the FROM clause or rewrite the query using JOINs.

Greg
How would that look?
Oliver
A: 

it should look something like this:

SELECT c.*, a.ClinDate

FROM Customer c

inner join CustAppointments ca
inner join ClinicRooms cr
inner join AppClinics a

where c.SacCode IN
(
    SELECT SacCode
    FROM SacCode
    WHERE ResellerCorporateID = 392
    ORDER BY SacCode
)

and c.CustomerID = ca.CxID
and ca.AppRoomID = cr.AppRoomID
and cr.ClinID = a.ClinID

and a.ClinDate >='20090101' 
and a.ClinDate <='20091119'
davek
Thanks Dave, but I can get it to work...Where does s.SacCode come from?
Oliver
I meant *Can't* get it to work...!
Oliver
that should be c.SacCode as you are selecting from the inline query i.e. there is not join to SacCode
davek
...although you could do (see answer from Jim)
davek
+1  A: 

I'd rewrite the query using joins. Then, you can access any data from any of the joined tables.

For example, you could rewrite your query like this:

SELECT c.*, ac.ClinDate
FROM Customer c
  JOIN SacCode sc ON sc.SacCode = c.SacCode
  JOIN CustAppointments ca ON ca.CustomerID = c.CustomerID
  JOIN ClinicRooms cr ON cr.AppRoomID = ca.AppRoomID
  JOIN AppClinic ac ON ac.ClinID = cr.ClinID
WHERE ac.ClinDate >='20090101'
  AND ac.ClinDate <='20091119'
  AND sc.ResellerCorporateID = 392
Jim
SLICK!SLICK!SLICK!SLICK!
Oliver