LuckyLucks
asked on
Nested query MS SQL 2008
Hi,
I am trying to understand someone else's code and here we have a case of nested query (A) that has linkages to the Customer table of the outerquery B.
I am not sure I understand what A is accomplishing. Can I rewite it as:-
( CustomerType X Customer c X Customer_Old ) X CustomerType_Old ctl
where ctl.customer_role_cd = c.code ??
Code:
---------
Select (Select ctl.id
from CustomerType_Old ctl
inner join CustomerType ct
on ctl.id = ct.id where ctl.customer_role_cd = c.code ) <------ A
from CustomerType ct
inner join Customer c on c.CustomerType_Id = ct.id
inner join Customer_Old cl on c.id = cl.id
left join CustomerType_Old ctl ON ctl.id = ct.id <----- B
B is :
( CustomerType X Customer X Customer_Old ) Left Join (CustomerType_Old)
I am trying to understand someone else's code and here we have a case of nested query (A) that has linkages to the Customer table of the outerquery B.
I am not sure I understand what A is accomplishing. Can I rewite it as:-
( CustomerType X Customer c X Customer_Old ) X CustomerType_Old ctl
where ctl.customer_role_cd = c.code ??
Code:
---------
Select (Select ctl.id
from CustomerType_Old ctl
inner join CustomerType ct
on ctl.id = ct.id where ctl.customer_role_cd = c.code ) <------ A
from CustomerType ct
inner join Customer c on c.CustomerType_Id = ct.id
inner join Customer_Old cl on c.id = cl.id
left join CustomerType_Old ctl ON ctl.id = ct.id <----- B
B is :
( CustomerType X Customer X Customer_Old ) Left Join (CustomerType_Old)
Kyle Abrahams, PMP
can you post the current query and the new proposed query?
The subquery doesn't make much sense. Do you have a requirement spec for this query that might explain the original designer's thinking?
Try just this:
SELECT ctl.id
FROM CustomerType ct
INNER JOIN Customer c ON c.CustomerType_Id = ct.id
INNER JOIN Customer_Old cl ON c.id = cl.id
LEFT JOIN CustomerType_Old ctl ON ctl.id = ct.id
AND ctl.customer_role_cd = c.code
Try just this:
SELECT ctl.id
FROM CustomerType ct
INNER JOIN Customer c ON c.CustomerType_Id = ct.id
INNER JOIN Customer_Old cl ON c.id = cl.id
LEFT JOIN CustomerType_Old ctl ON ctl.id = ct.id
AND ctl.customer_role_cd = c.code
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are you sure you don't get an error for sub query A?
Here to be accepted has to produce a single value.
To understand properly, you need to list few records, and list the anticipated output from running the query.
Here to be accepted has to produce a single value.
To understand properly, you need to list few records, and list the anticipated output from running the query.