what kind of join? - 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)

what kind of join?

Author

Topic

mentalee
Starting Member

6 Posts

Posted - 2005-06-17 : 20:16:06

Table A
Fields: Order, Date, Total

Table B
Fields: Order, Item, Source

Desired Results:

Table A only has one record row per order, however Table B can have multiple record rows for the same order (1 for each unique item). Table B also contains the source of that item (which would be the same as the order) so all "sources" for the same order would be the same.

I am wanting to join the Order Totals from Table A and Group them by the source in Table B, however the left join is returning multiple records because because there are multiple items in Table B thus skewing my total.

Any ideas?

Following is the Query I was going to use, but for the duplicate record problems.

Select B.Source,Sum(A.Total)
From A
Left Join B on A.Order = B.Order
Group by B.Source

Thanks in Advance!!!

Michael

jhermiz

3564 Posts

Posted - 2005-06-17 : 22:29:33

Show us sample data, insert statements, ddl, etc..

The sample data should be broken up into what you currently have,
and what you would like to have...

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]
Imperfection living for perfection -- [url]http://jhermiz.blogspot.com/[/url]

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-17 : 23:06:24

quote:
Originally posted by mentalee

Table B also contains the source of that item (which would be the same as the order) so all "sources" for the same order would be the same.

Sounds like column "source" equals column "order", but that doesn't make sense.

I'm confused by the problem statement, but maybe you are saying that different orders might have the same source values and you want to add the totals for those orders that have the same source?


SELECT DISTINCT B.Source, A.Order, A.Total
  FROM B
  INNER JOIN A
    ON A.Order = B.Order

now just group by B.Source to add the totals...

SELECT Source, Sum(Total) As GrandTotal
  FROM ( -- insert the above query here
  ) X
  GROUP BY Source

mentalee
Starting Member

6 Posts

Posted - 2005-06-18 : 19:54:10

sorry, my comment about the source and order did sound confusing I will clarify. The "Source" column seems to me like it belongs more in Table A (the order header information) but for whatever reason it ended up in Table B (Order Detail info). Let's say sample data for "Source" would be either PHO or WEB. I want to sum up my total dollars from each source, but the problem with my query above is that I am returning multiple rows for the same for the same order (1 row for each item/Table B record row) which will skew my total, even though I am only selecting Source and Dollar. It might be easier to explain this problem better by showing the data that would actually be getting summed, so here's an example:

TABLE A
--------------
ORD123 12.50
ORD124 23.75

TABLE B
------------------
ORD123 WEB ITEM1
ORD123 WEB ITEM2
ORD124 PHO ITEM1

--------------------------

Results of my Previous Query would be:

WEB 25.00
PHO 23.75

but the Total for web is really skewed by the fact that since I had two items for ORD123 it joined the Header info in Table A (containing the Order Total) with BOTH record in Table B (one for each item). Since the "Source" for every order remains the same, it is only necessary for me to grab the first (or only one) of the rows in Table B if there is more than just one record row.

Thanks again for your willingness to help, I hope this clarifies better.

Michael

mentalee
Starting Member

6 Posts

Posted - 2005-06-18 : 20:28:38

Sam

I read your post again and looked at your Query a little better and I think it did the trick... had to tweak it a little, but I believe that gave me only ONE row from Table B and then I could Join to Table A and sum my totals.

Thanks for the help!

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources