SQL query problem -- Case or Join?

23 Aug 2006 2:23 PM

thabrown

Hi,
Sorry if I'm in the wrong group for this sql language question, but perhaps
someone here can answer -- or direct me to a more appropriate newsgroup?

I have a sql query that attempts to select a "DealerClientType" based on a
Case statement involving values from other tables. Running the query clocks
for minutes and when I cancel I see it must be looping as it returns the same
row over 100,000 times. Copied below is the query. Perhaps some combination
of a left join and case might work? My intent is to return all rows from the
first table (Transactions), and to include a DealerClientType based on
different criteria for different Dealers.

SELECT a.[TransSQLID]
      ,a.[DealerCode]
      ,a.[BranchCode]
      ,a.[AccountNbr]
      ,a.[Units],
(Select Case
        When (Select c.DealerCode from [MIP].[dbo].[DealerAccountNbr] c
                        Where a.DealerCode = c.DealerCode
                        and a.AccountNbr = c.AccountNbr) = 'ABC'
                Then c.AccountClientType
        When (Select d.DealerCode from [MIP].[dbo].[DealerBranch] d
                        Where a.DealerCode = d.DealerCode
                        and a.BranchCode = d.BranchCode) = 'XYZ'
                Then d.BranchClientType
        Else b.[DealerClientType]
end) as TransClientType
FROM [MIP].[dbo].[Transactions] a
         , [MIP].[dbo].[Dealers] b
         , [MIP].[dbo].[DealerAccountNbr] c
         , [MIP].[dbo].[DealerBranch] d
Where a.DealerCode = b.DealerCode

Any advice is much appreciated! Thank you!
Thomas

23 Aug 2006 3:03 PM

Roy Harvey

The usual group for this would be .programming rather than .tools, but
there is no need to repost it.

The problem is in the FROM and WHERE clauses:

> FROM [MIP].[dbo].[Transactions] a
>         , [MIP].[dbo].[Dealers] b
>         , [MIP].[dbo].[DealerAccountNbr] c
>         , [MIP].[dbo].[DealerBranch] d
>Where a.DealerCode = b.DealerCode

a and be are being joined, but c and d are not. This results in a
cross-product; every row from joining a to b is matched up with every
row in c, and that is matched with every row in c. So, of the result
if joining a to b is 100 rows, and there are 100 rows in c, and 100
rows in d, the final result set will have 100 * 100 * 100 = 1,000,000
rows.

One of the good things about the newer JOIN syntax is that you can't
make this mistake as easily. It would look like:

FROM [MIP].[dbo].[Transactions] a
JOIN [MIP].[dbo].[Dealers] b
    ON a.DealerCode = b.DealerCode
JOIN [MIP].[dbo].[DealerAccountNbr] c
    ON --insert missing join criteria
JOIN [MIP].[dbo].[DealerBranch] d
    ON --insert missing join criteria

Once you use the JOIN word (unless it is CROSS JOIN) you have to
supplky the ON clause test or it fails the syntax check.

Roy Harvey
Beacon Falls, CT

On Wed, 23 Aug 2006 07:23:02 -0700, thabrown
<thabr***@discussions.microsoft.com> wrote:

Show quote

>Hi,
>Sorry if I'm in the wrong group for this sql language question, but perhaps
>someone here can answer -- or direct me to a more appropriate newsgroup?
>
>I have a sql query that attempts to select a "DealerClientType" based on a
>Case statement involving values from other tables. Running the query clocks
>for minutes and when I cancel I see it must be looping as it returns the same
>row over 100,000 times. Copied below is the query. Perhaps some combination
>of a left join and case might work? My intent is to return all rows from the
>first table (Transactions), and to include a DealerClientType based on
>different criteria for different Dealers.
>
>
>SELECT a.[TransSQLID]
>      ,a.[DealerCode]
>      ,a.[BranchCode]
>      ,a.[AccountNbr]
>      ,a.[Units],
>(Select Case
>        When (Select c.DealerCode from [MIP].[dbo].[DealerAccountNbr] c
>                        Where a.DealerCode = c.DealerCode
>                        and a.AccountNbr = c.AccountNbr) = 'ABC'
>                Then c.AccountClientType
>        When (Select d.DealerCode from [MIP].[dbo].[DealerBranch] d
>                        Where a.DealerCode = d.DealerCode
>                        and a.BranchCode = d.BranchCode) = 'XYZ'
>                Then d.BranchClientType
>        Else b.[DealerClientType]
>end) as TransClientType
> FROM [MIP].[dbo].[Transactions] a
>         , [MIP].[dbo].[Dealers] b
>         , [MIP].[dbo].[DealerAccountNbr] c
>         , [MIP].[dbo].[DealerBranch] d
>Where a.DealerCode = b.DealerCode
>
>
>Any advice is much appreciated! Thank you!
>Thomas
>
>

23 Aug 2006 5:17 PM

thabrown

Thank you, Roy! I appreciate your reply.

I enhanced your JOINs with LEFT Joins for the last two -- to get all
transactions -- and it works fine.

Thank you for your help.

Thomas

Show quote

"Roy Harvey" wrote:

> The usual group for this would be .programming rather than .tools, but
> there is no need to repost it.
>
> The problem is in the FROM and WHERE clauses:
>
> > FROM [MIP].[dbo].[Transactions] a
> >         , [MIP].[dbo].[Dealers] b
> >         , [MIP].[dbo].[DealerAccountNbr] c
> >         , [MIP].[dbo].[DealerBranch] d
> >Where a.DealerCode = b.DealerCode
>
> a and be are being joined, but c and d are not. This results in a
> cross-product; every row from joining a to b is matched up with every
> row in c, and that is matched with every row in c. So, of the result
> if joining a to b is 100 rows, and there are 100 rows in c, and 100
> rows in d, the final result set will have 100 * 100 * 100 = 1,000,000
> rows.
>
> One of the good things about the newer JOIN syntax is that you can't
> make this mistake as easily. It would look like:
>
>   FROM [MIP].[dbo].[Transactions] a
>   JOIN [MIP].[dbo].[Dealers] b
>     ON a.DealerCode = b.DealerCode
>   JOIN [MIP].[dbo].[DealerAccountNbr] c
>     ON --insert missing join criteria
>   JOIN [MIP].[dbo].[DealerBranch] d
>     ON --insert missing join criteria
>
> Once you use the JOIN word (unless it is CROSS JOIN) you have to
> supplky the ON clause test or it fails the syntax check.
>
> Roy Harvey
> Beacon Falls, CT
>