SQL Server Performance Forum – Threads Archive

Comparing 2 sql statements

I have two tables – SOrder and SalesOrder. I want to somehow compare the values in the two tables. What I want to find out is if its possible to compare the count of items in one table to the count of items in the other table. E.g Table SOrder SOrder NoItems
1 2
2 3 Table SalesOrders SOrder NoItems
1 2
2 4 Here I want to be able to return that SOrder 2 is different.
Select SOrder, count (*) As NoItems from SalesOrders
group by SOrder Select SOrder, NoItems from SOrders

---

Are you trying to check how many rows are in one table and they are not in other and vice versa?

---

try this–
SELECT DISTINCT tmp.sorder FROM (SELECT COUNT(sorder)AS cnt,sorder FROM SOrder GROUP BY sorder
union all
SELECT COUNT(sorder),sorder from SalesOrders
GROUP BY sorder
) AS tmp
GROUP BY tmp.sid,tmp.cnt
HAVING COUNT(*)<2 this will result in those sorder having count of sorder not equal in
both the tables. i hope this is what ur question indicates.

---

What I am trying to do is count the occurences of a SOrder in the first table SalesOrders and check if this number corresponds to the value in the column NoItems in table SOrder. So SOrder 1 may occur 4 times in table SalesOrders, but the value in table SOrders may be like so: SOrder NoItems
1 3 in this case I want to only return the SOrders from Sorder table where the count of a particular SOrder in table SalesOrders is different to the value in NoItems column in SOrders.

---

ok so u already have count of sorder in both the tables….right SELECT SOrder.sid
FROM SalesOrders INNER JOIN
SOrder ON SalesOrders.sid = SOrder.sid AND SalesOrders.noitem <> SOrder.noitem

---

If I got it right, you want something like:
select a.SOrder, a.NoItems, b.NoItems as ItemCount
from SOrder as a
join (select SOrder, count(*) as NoItems
from SalesOrders) as b
on a.SOrder = b.SOrder
where a.NoItems <> b.NoItems

---

]]>