Complex Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Complex Query

Author

Topic

wabs27
Starting Member

4 Posts

Posted - 2007-10-25 : 12:52:16

This may be hard to explain so let me know if you need any further clarification.

I have a table Transactions that has some details about each transaction. There can be different types of transactions (purchases, sales, etc). I want to generate a Select statement that will return data from the Transaction table along with data from the table that each row is associated with (for example: row 1 can be associated to a Sale so I would want SaleName, row2 can be associated to a Purchase so I would want PurchaseName, row3 can be associated to a customer so I would want CustomerName, etc).

My question is how do I generate the value for DisplayName since it is pulling it from a different table each time, or since it is dynamic.

Below is a more detailed description of my desired SELECT statement along with a description of my tables.

MY DESIRED SELECT STATEMENT
===============================
TranID from Transaction
TranAmt from TransAction
TranTypeName from TranType
DisplayName (this is going to be either SaleName or PurchaseName or any display name from an other table. To get this record it is practically: Select (value of TranType.Display) from (value of TranType.table) Where (value of TranType.PKey) = @MyKey as DisplayName

TRANSACTION
============
TranID
TranAmt
TranDate
TranType
TranExtID

TranType Table
===============
TranTypeID
TranTypeName
Table
Pkey
Display

Other tables..

Sales
======
SaleID
SaleName
...
....
...

Purchases
===========
PurchaseID
PurchaseName
.....
.....
.....

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-10-25 : 13:38:17

Looks like you might have to use Dynamic SQL because you dont know which table has your data. Or you need to write a bunch of IF statements to find out which table has the data and join with it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

wabs27
Starting Member

4 Posts

Posted - 2007-10-25 : 13:41:56

How would I go about writing dynamic sql?

Even if I was to use IF statements (which I'd rather not) how can do it since I need to run the IF within the SELECT. How would it be written?

quote:
Originally posted by dinakar

Looks like you might have to use Dynamic SQL because you dont know which table has your data. Or you need to write a bunch of IF statements to find out which table has the data and join with it.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-10-26 : 15:45:51

If you know what a particular type is, for example: Sales or Puchases you can run individual queries and UNION them togeher. For example:

SELECT
	T.TranID
	T.TranAmt
	TT.TranTypeName 
	S.SaleName AS DisplayName 
FROM
	[Transaction] AS T
INNER JOIN
	TranType AS TT
	ON T.TranTypeID = TT.TranTypeID
INNER JOIN
	Sales AS S
	ON T.TranExtID = S.SalesID
WHERE
	tt.TranTypeName = 'Sales'
	-- Or use the ID  tt.TransTypeID = XX

UNION ALL


SELECT
	T.TranID
	T.TranAmt
	TT.TranTypeName 
	P.PurchaseName AS DisplayName 
FROM
	[Transaction] AS T
INNER JOIN
	TranType AS TT
	ON T.TranTypeID = TT.TranTypeID
INNER JOIN
	Purchase AS P
	ON T.TranExtID = P.PurchaceID
WHERE
	tt.TranTypeName = 'Purchase'
	-- Or use the ID  tt.TransTypeID = XX

UNION ALL


--ETc..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources