Joining duplicate data - 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

SQL Server Development (2000)

Joining duplicate data

Author

Topic

elektrobank
Starting Member

15 Posts

Posted - 2003-05-28 : 04:10:22

I have three tables that I use for my sales transactions, they are something like this:

Transaction: ID|TransID|TotalPurchases
Service: ID|TransID|ServiceID|Total
Retail: ID|TransID|RetailID|Total

So I have one transaction row per sales transaction and many retail or service rows that link to the transaction table using the TransID field. The problem I'm having is when I have a transaction with like 1 service sale and 2 retail sales, if I do a query to return this transaction and group it with the service and retail tables I get something like this:

ID|TransID|TotalPurchases|ID|TransID|ServiceID|Total|ID|TransID|RetailID|Total
1 |101 |$50.00 |1 | 101 |3 |15 |1 |101 |5 |25
1 |101 |$50.00 |1 | 101 |3 |15 |1 |101 |2 |10

So as you see here, there are 2 rows that I get with 1 retail item per row, but the same service item comes up in both rows. I am sending this query to Crystal Reports so I need the results to come out as one single table like this but the results of the service/retail sales MUST be distinct. So I really would want something like either of these as my result:

ID|TransID|TotalPurchases|ID|TransID|ServiceID|Total|ID|TransID|RetailID|Total
1 |101 |$50.00 |1 |101 |3 |15 |1 |101 |5 |25
1 |101 |$50.00 |N |NULL |NULL |NULL|1 |101 |2 |10

or this:

ID|TransID|TotalPurchases|ID|TransID|ServiceID|Total|ID|TransID|RetailID|Total
1 |101 |$50.00 |1 |101 |3 |15 |N |NULL |NULL |NULL
1 |101 |$50.00 |N |NULL |NULL |NULL|1 |101 |2 |10
1 |101 |$50.00 |N |NULL |NULL |NULL|1 |101 |5 |25

I have been able to create a query for the 2nd result by doing the query twice, forcing NULLS for the service on the first query, then forcing NULLS on the retail for the second query then just doing a UNION between the two queries. I can use this as a last resort but the query just becomes so big and messy that I'd rather find another way to do it.

Any ideas??

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources