Dont want cross join but get it anyway

Question

I am doing something wrong with my join but cant figure it out. It is SQL Server 2005.

The queries:

select count(*) from tblDGHistoryPO
    where ItemID = '#00 CORK'
select count(*) from tblDGHistorySO
    where ItemID = '#00 CORK'

return 10 and 19 respectively. When I try to join them I get a cross join (190):

select count(*)
    from tblDGHistoryPO P
        inner join tblDGHistorySO S on S.ItemID = P.ItemID
    where P.ItemID = '#00 CORK'
    group by P.ItemID

If I remove the aggregate I get similar results (cross join with 190 rows). Anyone know what I am doing wrong?

I was hoping to get 19 rows. I am trying to figure out which item appears the most in both tables. Can this be done? — Joe, Oct 21 '15 at 16:07

John Pasquet · Answer 1 · 2015-10-21T16:05:33.040

1

In reality, you're getting exactly what you would expect. The INNER JOIN matches every matching row in the first table with every matching row in the other table. 10 X 19 = 190

You are getting a count of the JOIN -- not the sum of the counts of matches in each table.

I'm not sure why you are wanting to join the tables. If the tables have the same structure, you might be intending to use UNION

SELECT COUNT(*)
FROM
(
    SELECT ITEMID, DESCRIPTION
    FROM tblDGHistorySO
    WHERE ITEMID = '#00 CORK'

    UNION ALL

    SELECT ITEMID, DESCRIPTION
    FROM tblDGHistorySP
    WHERE ITEMID = '#00 CORK'
}

You could then select the count of that result.

edited Oct 21 '15 at 16:05

answered Oct 21 '15 at 16:02

John Pasquet

1,824
15
20

Thats better, I get 29 records. I must be attacking this the wrong way. I actually have 4 tables and am trying to figure out the items that appear the most in all 4 tables. I may have to do this manually. – Joe Oct 21 '15 at 16:18

score 0 · Answer 2 · answered Oct 21 '15 at 16:02

0

Actually you are getting correct result. Sqlserver will give you 190 records for your condition.

Why?

Because you joined tblDGHistoryPO with tblDGHistorySO on the basis of ItemId. 
So, while executing the query sqlserver will select 1 item id from tblDGHistoryPO  
at a time and will give the matching results from other table i.e. tblDGHistorySO . 

So For every ItemId of tblDGHistoryPO there is 19 records in another table.   

So, total would be 10*19 = 190.

answered Oct 21 '15 at 16:02

Mukund

1,679
1
11
20

As @John suggested, if you want common result then you can use Union , Union All as per your need. – Mukund Oct 21 '15 at 16:04
When I select from the sales order table and the sales order line table I get 2 rows for a sales order with two lines. 2 * 1. I see now how it works. I thought it was just adding lines to the sales order but it was not, it was multiplying but since there was one row in a table it could appear like it was adding. But now I do not know the difference between a join and cross join if there is even one. – Joe Oct 21 '15 at 16:23
http://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sql-server-2008 @Joe – Mukund Oct 21 '15 at 16:50

Dont want cross join but get it anyway

2 Answers2