using Correlated subQuery in Join not working

Question

using Correlated subQuery in Join not working

tshad

SSCertifiable

Points: 5905
More actions
June 10, 2013 at 2:11 pm

#274906

I have a large query that is returning multiple rows that I tracked down to my jornal table.
It has 2 (or more) credit entries for some entries and I only want one (doesn't matter which one). So I want to use a "TOP 1" but can't use it directly in my JOIN. I can use a CROSS APPLY but not sure if that is the best way.
My query is something like:
SELECT *
FROM Client sc
JOIN dbo.JOURNAL jrnl
ON jrnl.ClientId = sc.ClientId
AND jrnl.DC = 'c'
This will give me back 2 records.
I tried to do a correlate subquery, like so:
SELECT *
FROM dbo.Client sc
JOIN ( SELECT TOP 1 * FROM dbo.JOURNAL jrnl
WHERE jrnl.ClientId= sc.ClientId
AND jrnl.DC= 'c') jrnl
ON jrnl.ClientId= sc.ClientId
WHERE sc.ClientId= 942222
The problem is that the "sc.ClientId" inside the join gets an error:
The multi-part Identifier "sc.ClientId" could not be bound.
Why is that?
I can get this to work using a CROSS APPLY (not sure why) but wanted to use a normal join to solve the issue. This works:
SELECT *
FROM dbo.Client sc
CROSS APPLY( SELECT TOP 1 * FROM dbo.JOURNAL jrnl
WHERE jrnl.ClientId= sc.ClientId
AND jrnl.DC= 'c') jrnl
WHERE sc.ClientId= 942222
Why doesn't the normal Join work?
Thanks,
Tom

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Calibear Ten Centuries Points: 1377 More actions · Answer 1

Can you provide sample data for both tables? This will help while creating a query.

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

tshad SSCertifiable Points: 5905 More actions · Answer 2

Here is a very rudimentary set of tables that has just the right amount of records to see the issue.

The first and last query work with this and the 2nd doesn't.

CREATE TABLE Client

(

ClientId int,

Name varchar(50)

)

CREATE TABLE Journal

(

JournalId int,

ClientId int,

DC char(1)

)

INSERT Client VALUES(942222,'Test Customer')

INSERT Journal VALUES(1000, 942222, 'c')

INSERT Journal VALUES(1001, 942222, 'd')

INSERT Journal VALUES(1002, 942222, 'c')

Thanks,

Tom

Calibear Ten Centuries Points: 1377 More actions · Answer 3

I added a couple more accounts and more test data to make sure my query was complete. This is what I've come up with. Since you only need one row, I've queried on the minimum journalid and used that data:

SELECT *

FROM #Client sc

JOIN (select ClientId, DC, MIN(JournalId) JournalId from #Journal group by ClientId, DC) jrnl

ON jrnl.ClientId = sc.ClientId

AND jrnl.DC = 'c'

--------
For SQL Tips, check out my blog: http://lantztechknowledge.blogspot.com/[/url]
You can also follow my twitter account to get daily updates: @BLantz2455

tshad SSCertifiable Points: 5905 More actions · Answer 4

That make sense.

So you can't really do correlated subqueries, I assume.

Which would be better - the normal join or the CROSS APPLY?

Thanks,

Tom

geoff5 Hall of Fame Points: 3560 More actions · Answer 5

Correlated subqueries can exist in the SELECT clause and the WHERE clause, and as you have shown in the predicate of a CROSS APPLY clause, but not as a simple inline view of the FROM clause. I believe this is because the inline view must be self-contained so that it can be fully "instantiated" before its rows are added to the query results. The view is not self-contained if it has a reference to an instance of a table not within the view itself.

Apparently the tables in a correlated subquery in the SELECT or WHERE clauses are not instantiated in the same way and therefore need not be self-contained but can have references to the FROM tables or inline views.

I have worked on the assumption that CROSS APPLY is just an alternate way of putting a correlated subquery in the SELECT column list, and your observations seem to bear this out.

tshad SSCertifiable Points: 5905 More actions · Answer 6

I understand.

I am still a little confused on the use for the CROSS APPLY, but it seems to work.

Which would be the better one to use the CROSS APPLY or the JOIN you also showed?

Or does it matter? Is the CROSS APPLY just for Microsofts Sql Server or is it ANSI?

Thanks,

Tom

mahavidhya24 SSC Veteran Points: 204 More actions · Answer 7

Cross apply is very difficult than join ...i think there is no data available depends upon your requirement give the table of contents connected with this query.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

There is an alternative way of doing this - you might like to check to see whether it performs any better:

with maxJournal

as (

select ClientId

,JournalId = MIN(JournalId)

from Journal

group by ClientId

)

select *

from Client c

join maxJournal j on c.ClientId = j.clientId

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

mahavidhya24 (6/11/2013)
Cross apply is very difficult than join ...i think there is no data available depends upon your requirement give the table of contents connected with this query.

Please explain what you mean by 'difficult'. It's different, but not so difficult, once you start using it.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin