JOIN Help Needed (Subquery Needed?) 1

[soho34]

Hi,

I'm a programmer, but my SQL joins are really rusty right now.

This is a 1 to Many question. I separated the fields/fieldnames with bars.

I have 3 Tables I need to connect:

Table A = VENDOR (3 records)
----------------------------
VendorID | Name
1| Rooms To Go
2| TXU
3| Circuit City

Table B = CLIENT (8 records)
----------------------------
VendorID | ClientID
1| 20
1| 84
1| 2
1| 69
2| 20
2| 84
2| 2
2| 69

Table C = CLIENTNAME (100+ records)
----------------------------
ClientID | Clientname
20|Acme
84|Barnes
2|Baylor
69|Balda

I want my result set to look like:

VendorID(from VENDORS),Name(Vendors),AllClients(a derived column):
----------------------------------
1 |Rooms To Go |Acme, Barnes,Baylor,Balda
2 |TXU | Acme, Barnes,Baylor,Balda
3 |Circuit City


I need a little help on the joins, and in particular the sub-select I think I may need.

Here's what I have so far, but this initially connects the first two tables and also gives me more records than I'm looking for. I haven't added the 3rd table here yet.

SELECT distinct
v.*,
c.*
FROM vendors v LEFT OUTER JOIN
( select * from client) c
on v.vendorid = c.vendorid

Thanks in advance for any help you can provide.

 

[vongrunt]

This is classic example of on-the-fly denormalization. IMO joins are not suitable for that task.

The most elegant way would be to write user-defined function that takes VendorID and returns AllClients. But definitely not the fastest.

Can you do that client-side? Two nested loops over sorted recordset should be enough.

 

[donutman]

Ok, I smell another speed test. The function solution vs a multi-step temporary table approach. Might even be able to throw a cursor at this. One thing for sure a client side solution that must read every row after a sort can't be the best???
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

OK. In another thread? Let's recycle tables from your row counter example for that.

 

[donutman]

Don't need another thread. The function approach on my production table to denomalize the KarlsOrderDetail for PDesc on each CId completes in 10sec (186,000 rows). No other approach is going to touch that, IMHO. The temp table will be too big and I'm sure you don't want to even think about a cursor.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Personally I'm often doing denormalization client-side. Reasons? Such queries often serve for human-readable purposes, there are usually many of them but with reasonably small result sets (way less than 186k rows). All server has to do is to deliver sorted flat data.

Btw. as an answer to original post, can you post this function?

 

[donutman]

Btw. as an answer to original post, can you post this function?

On the drive home from work, I was thinking the same thing...didn't answer the original post sufficiently!

[Blue]CREATE[/Blue] [Blue]FUNCTION[/Blue] dbo.ClientList [Gray]([/Gray]@VendorID [Blue]AS[/Blue] [Blue]int[/Blue][Gray])[/Gray]
   [blue]RETURNS[/blue] [Blue]varchar[/Blue][Gray]([/Gray]4000[Gray])[/Gray]
[Blue]AS[/Blue]
[Blue]BEGIN[/Blue]
   [Blue]DECLARE[/Blue] @List [Blue]varchar[/Blue][Gray]([/Gray]4000[Gray])[/Gray]
   [Blue]SELECT[/Blue] @List[Gray]=[/Gray]Coallesce[Gray]([/Gray]@List[Gray]+[/Gray][red]', '[/red][Gray],[/Gray][red]''[/red][Gray])[/Gray][Gray]+[/Gray]ClientName
      [Blue]FROM[/Blue] Client C [Blue]INNER[/Blue] [Gray]JOIN[/Gray] ClientName CN
         [Blue]ON[/Blue] C.ClientID[Gray]=[/Gray]CN.ClientID
      [Blue]WHERE[/Blue] C.VendorID[Gray]=[/Gray]@VendorID
   [Blue]RETURN[/Blue] @List
[Blue]END[/Blue]
[Blue]GO[/Blue]

   [Blue]SELECT[/Blue] VendorID[Gray],[/Gray] [Name][Gray],[/Gray] dbo.ClientList[Gray]([/Gray]VendorID[Gray])[/Gray]
      [Blue]FROM[/Blue] Vendor
      [Blue]ORDER[/Blue] [Blue]BY[/Blue] VendorID

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[557]

so how do we do get this result?

 

[557]

got it.... thanks