Troubles with nested queries

Question

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.

Is this not possible with JOINS? You could get all the data you needed then. — Ben Everard
– Ben Everard, Commented Nov 19, 2009 at 12:21

Jim · Accepted Answer · 2009-11-19 12:47:11Z

1

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

answered Nov 19, 2009 at 12:47

Jim

11.5k23 gold badges82 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Fermin · Accepted Answer · 2009-11-19 12:22:26Z

0

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.

answered Nov 19, 2009 at 12:22

Fermin

36.3k22 gold badges87 silver badges131 bronze badges

Comments

Greg · Accepted Answer · 2009-11-19 12:22:43Z

0

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

answered Nov 19, 2009 at 12:22

Greg

323k55 gold badges378 silver badges338 bronze badges

Comments

davek · Accepted Answer · 2009-11-19 12:40:05Z

0

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'

answered Nov 19, 2009 at 12:40

davek

23k11 gold badges80 silver badges97 bronze badges

3 Comments

Oliver Over a year ago

Thanks Dave, but I can get it to work... Where does s.SacCode come from?

davek Over a year ago

that should be c.SacCode as you are selecting from the inline query i.e. there is not join to SacCode

davek Over a year ago

...although you could do (see answer from Jim)

Collectives™ on Stack Overflow

Troubles with nested queries

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related