sql server programming: select from tblA where countA not match count from tblB1+tblB2 -- sql server programming

RE: select from tblA where countA not match count from tblB1+tblB2

Alejandro Mesa
11/2/2007 12:49:19 PM

Try:

SELECT
*
FROM
(
SELECT code, COUNT(*) AS cnt
FROM [#tmpA]
GROUP BY code
) AS a
LEFT OUTER JOIN
(
SELECT code, COUNT(*) AS cnt
FROM
(
SELECT * FROM [#tmpB1]
UNION ALL
SELECT * FROM [#tmpB2]
) AS t
GROUP BY code
) AS b
ON a.code = b.code
WHERE
b.code IS NULL
OR a.cnt != b.cnt
GO

-- OR

SELECT
*
FROM
(
SELECT code, COUNT(*) AS cnt
FROM [#tmpA]
GROUP BY code
) AS a
FULL OUTER JOIN
(
SELECT code, COUNT(*) AS cnt
FROM
(
SELECT * FROM [#tmpB1]
UNION ALL
SELECT * FROM [#tmpB2]
) AS t
GROUP BY code
) AS b
ON a.code = b.code
WHERE
a.code IS NULL
OR b.code IS NULL
OR a.cnt != b.cnt
GO

AMB

[quoted text, click to view]

RE: select from tblA where countA not match count from tblB1+tblB2

Rich
11/2/2007 1:04:09 PM

Thank you. your solutions worked great!

May I ask what is the difference between an outer join and an inner join?

I can understand the left join - pull everything from the left table and
only the records that match from the right table. The inner join only pulls
records that match from both tables.

it looks like an outer join does not have join fields or you are just
matching all the fields but they don't have to be equal?

[quoted text, click to view]

RE: select from tblA where countA not match count from tblB1+tblB2

Alejandro Mesa
11/2/2007 4:31:01 PM

Hi Rich,

I guess you are asking about the FULL OUTER JOIN, because there two other
OUTER joins, LEFT and RIGHT, but you mentioned knowing about them. The FULL
OUTER JOIN bring rows from both sides even if there is no match in the
opposite side.

Example:

declare @t1 table (c1 int)
declare @t2 table (c1 int)

insert into @t1 values(1)
insert into @t1 values(2)

insert into @t2 values(2)
insert into @t2 values(3)

select *
from @t1 as a left outer join @t2 as b on a.c1 = b.c1

select *
from @t1 as a right outer join @t2 as b on a.c1 = b.c1

select *
from @t1 as a full outer join @t2 as b on a.c1 = b.c1

I used it in the previous post, just in case. You did not mention anything
about if there could be rows in B1 or B2 that were not in A or rows in A that
could not be in B1 union all B2.

AMB

[quoted text, click to view]

Re: select from tblA where countA not match count from tblB1+tblB2

Steve Dassin
11/2/2007 7:39:16 PM

[quoted text, click to view]

How is it when you 'think sets' that you end up with a
full outer join? Put simply, there is a disconnect between
how users are told to think and the language to support it.
This is a simple relational division problem obscured by sql
because sql has no algebra to compare one set to another.
And when you try to compare two sets in sql you wind up with
crazy syntax that compounds the original problem rather than
clarifying it.

There are two tables involved and a simple comparison is all
that should be needed.

select (#tmpA group by {code} add {Count() Cnt})
Table A
code Cnt
---- ---
A 6
B 4
C 4

select (#tmpB1 union #tmpB2) group by {code} add {Count() Cnt}
Table B
code Cnt
---- ---
A 6
B 3
C 4

First find all the rows in table A also in table B. where table A is each
individual row of table A.
Table A < table B if all the rows in table A are in table B, and there is
at least one row in table B that is not in table A.

select
#tmpA group by {code} add {Count() Cnt} //The rows of Table A
where
(
//Create a table for each row of table A for a
//comparison to table B.
table{row{code ThisCode,Cnt ThisCnt}}
//Test if the table based on each row of table A is < table B.
<
//Table B
(
#tmpB1
union
#tmpB2
group by {code}
add {Count() Cnt} {code ThisCode,Cnt ThisCnt}
)
);

code Cnt
---- ---
A 6
C 4

Negate the '<' between the tables for the non-matching rows (counts) ie.
there is a row in table A not in table B and there is at least one row
in table B not in table A.

select
#tmpA group by {code} add {Count() Cnt}
where not
(
table{row{code ThisCode,Cnt ThisCnt}}
<
(
#tmpB1
union
#tmpB2
group by {code}
add {Count() Cnt} {code ThisCode,Cnt ThisCnt}
)
);

code Cnt
---- ---
B 4

This equivalent to the negation of a ('row' from table A in table B).

select
#tmpA group by {code} add {Count() Cnt}
where not
(
row{code ThisCode,Cnt ThisCnt}
in
(
#tmpB1
union
#tmpB2
group by {code}
add {Count() Cnt} {code ThisCode,Cnt ThisCnt}
)
);

code Cnt
---- ---
B 4

Would you think more of these ideas if they came from MS?
(It's possible) :-)

www.beyondsql.blogspot.com

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.