Complex Joins in SQL Server using TSQL 
Author Message
 Complex Joins in SQL Server using TSQL

Hi,

I have a problem I want to solve in TSQL.  It is the standard join problem
experienced in relational database systems when a one to many join is added
to a query, but when you come to display the resulting recordset (in a
browser or report, etc.) you don't want that one to many join to create
duplicate records.  If this sounds a bit abstract, here is an example.  Say
for instance that you have a table called "Managers" and another table
called "Common buzzwords".  Any given manager has a finite set of common
buzzwords that they like to use.  Lets assume we performed a join on
"Mangers" and "Common buzzwords". The resultset may look like this:

Jim        Super Fun
Jim        Proactive, Not Reactive
Jim        Win Win Situation
Jim        Center of Excellence

Note, however, that in our report, we would like the data formatted like
thus:

Name        Common Buzzwords

Jim            "Super Fun", "Proactive, Not Reactive", "Win Win Situation",
"Center of Excellence"

In MS Access, you can construct a query which includes a calculated field.
This calculated field could call a user defined function which returned the
string above, given a particular manager id for each row in your query.

The question is whether you can do the equivalent of this using pure TSQL.
By building a stored proc which declares a cursor, you could iterate through
a recordset to build the string as stated above, but can you include calls
to this stored proc from within a SQL statement select list? The
documentation tends to imply that you can't.  Can anyone think how you might
solve this problem by simply using TSQL?  I know I can do this using ADO,
but I would prefer a pure TSQL solution.

TIA

Aaron.



Wed, 24 Jul 2002 03:00:00 GMT  
 Complex Joins in SQL Server using TSQL
Aaron,

    If you have a finite maximum of Common Buzzwords, then, yes, you can do
it. You would need to add another column to the Buzzwords table, call it
BuzzIndex, or whatever.

    For a maximum of 5 'Buzzwords' per manager (BuzzIndex could be 1 to 5)

     SELECT
        Managers.Name,
        Buzzword1 =
                COALESCE((SELECT Buzzword FROM Buzzwords WHERE
Buzzwords.ManagerID = Managers.ManagerID AND BuzzIndex = 1), ''),
 Buzzword2 =
                COALESCE((SELECT Buzzword FROM Buzzwords WHERE
Buzzwords.ManagerID = Managers.ManagerID AND BuzzIndex = 2), ''),
 Buzzword3 =
                COALESCE((SELECT Buzzword FROM Buzzwords WHERE
Buzzwords.ManagerID = Managers.ManagerID AND BuzzIndex = 3), ''),
 Buzzword4 =
  COALESCE((SELECT Buzzword FROM Buzzwords WHERE Buzzwords.ManagerID =
Managers.ManagerID AND BuzzIndex = 4), ''),
 Buzzword5 =
  COALESCE((SELECT Buzzword FROM Buzzwords WHERE Buzzwords.ManagerID =
Managers.ManagerID AND BuzzIndex = 5), '')

    FROM
        Managers

You could also perform a concatenation of the buzzwords, providing spacing
and commas, but this is the fastest solution, and you should have your
report do the comma separation and other logic. The problem that causes the
extra logic is this:

1 Buzzword only: Super,,,,,
 2 Buzzwords only: Super, Fun, , ,
etc...

You don't want that much logic in the T-SQL statement.

Regards,
D. Patrick Hoerter


Quote:
> Hi,

> I have a problem I want to solve in TSQL.  It is the standard join problem
> experienced in relational database systems when a one to many join is
added
> to a query, but when you come to display the resulting recordset (in a
> browser or report, etc.) you don't want that one to many join to create
> duplicate records.  If this sounds a bit abstract, here is an example.
Say
> for instance that you have a table called "Managers" and another table
> called "Common buzzwords".  Any given manager has a finite set of common
> buzzwords that they like to use.  Lets assume we performed a join on
> "Mangers" and "Common buzzwords". The resultset may look like this:

> Jim        Super Fun
> Jim        Proactive, Not Reactive
> Jim        Win Win Situation
> Jim        Center of Excellence

> Note, however, that in our report, we would like the data formatted like
> thus:

> Name        Common Buzzwords

> Jim            "Super Fun", "Proactive, Not Reactive", "Win Win
Situation",
> "Center of Excellence"

> In MS Access, you can construct a query which includes a calculated field.
> This calculated field could call a user defined function which returned
the
> string above, given a particular manager id for each row in your query.

> The question is whether you can do the equivalent of this using pure TSQL.
> By building a stored proc which declares a cursor, you could iterate
through
> a recordset to build the string as stated above, but can you include calls
> to this stored proc from within a SQL statement select list? The
> documentation tends to imply that you can't.  Can anyone think how you
might
> solve this problem by simply using TSQL?  I know I can do this using ADO,
> but I would prefer a pure TSQL solution.

> TIA

> Aaron.



Fri, 26 Jul 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Using ado recordset in an SQL Server inner join

2. TSQL Debugger, VB6 and SQL Server 7

3. Setup SQL Server for TSQL Debugger

4. Can not find SQL Server (I'm not using SQL Server)

5. VB6 application using SQL Server 2000 migrating db to SQL Server 2

6. Using complex data types from VB ActiveX dlls with server-side scripting

7. Using complex data types from VB ActiveX dlls with server-side scripting

8. Using Vbscript and SQL-DMO to connect to SQL Server and run a T-SQL script

9. Problem refreshing SQL Server 7.0 joins in ADO

10. Delete Query on Joined Tables on SQL Server

11. SQL Server JOINed recordsets not updatable?

12. Join diff connections (SQL Server and ODBC) in Data Environment

 

 
Powered by phpBB® Forum Software