count and multiple tables (inner join) problem

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)

count and multiple tables (inner join) problem

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-21 : 10:29:29

Hello,

I'll try to explain this as best as I can. I have a table (Table A) that may look something like this:



Num           Type
1             Metal
2             Metal
3             Metal
3             Metal
4             Metal
5             Metal
5             Metal
6             Metal
7             Metal
...

I also have another table (Table B) that looks like this:



Num           Item
1             Metal
2             Metal
3             Metal
4             Metal
4             Metal
5             Metal
6             Metal
7             Metal
7             Metal
...

If you look closely, you'll notice that Table A and Table B don't have the same set. For example, there are two rows with Num 7 in Table B and one row with Num 7 in Table A.

Now, please understand this is a sample set. There are MANY, MANY records in both of these tables. What I want to do is count the number of times METAL appears for each Num, which I accomplish with the COUNT function just fine. I count the number of records that have METAL and group them by the Num, and I do this SEPARATELY for Table A and Table B.

The problem is I get no real correlation between both tables because I'm not linking them. So, my question is, how I can link both tables and count the records from table a and make another count for the records in table b. In other words, something like this:


Resulting Set

TableACount       TableBCount          Num
1                 1                    1
1                 1                    2
2                 1                    3
1                 2                    4
2                 1                    5
1                 1                    6
1                 2                    7
...

This way, I can see that Num 3,4,5,and 7 are amuck and I can make the neccessary corrections.

How can I do get the resulting table above?

Thank you.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 10:48:04

[code]
SELECT ta.cnt, tb.cnt, ta.Num
FROM
(
SELECT Num, cnt = COUNT(*)
FROM TableA
WHERE Type = 'Metal'
GROUP BY Num
) ta
INNER JOIN
(
SELECT Num, cnt = COUNT(*)
FROM TableB
WHERE Item = 'Metal'
GROUP BY Num
) tb ON ta.Num = tb.Num
[/code]

KH
[spoiler]Time is always against us[/spoiler]

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-21 : 11:05:00

You are, indeed, the man! It works! Thank you very much!

I was trying to do something like a count (CASE WHEN...), but it didn't look like that was technically possible and your solution is much more logical and modular.

Thank you again.

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2009-05-21 : 12:51:14

I do have a follow up question. I don't know if linking these two tables to resolve a different query is in order, but I'd like to check if Table A against Table B and vice-versa for missing entires.

For example,

Table A might have an entry like this:
[Code]
Num Type
8 Metal
[/code]

And Table B does not have a Num of 8 AND a Type of Metal. So, I want to check if Table B is missing records and vice-versa. In this example, I know I have to add an entry into Table B that has a Num of 8 and an Item of Metal.

This is a bit more open-ended, I suppose, but how may I go about this task?

Thank you very much.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-21 : 18:17:41

check out outer join

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-22 : 04:43:46

quote:
Originally posted by SQLIsTheDevil

I do have a follow up question. I don't know if linking these two tables to resolve a different query is in order, but I'd like to check if Table A against Table B and vice-versa for missing entires.

For example,

Table A might have an entry like this:
[Code]
Num Type
8 Metal

And Table B does not have a Num of 8 AND a Type of Metal. So, I want to check if Table B is missing records and vice-versa. In this example, I know I have to add an entry into Table B that has a Num of 8 and an Item of Metal.

This is a bit more open-ended, I suppose, but how may I go about this task?

Thank you very much.

you can do like this


SELECT SUM(CASE WHEN Cat=1 THEN 1 ELSE 0 END) AS TableACount,
SUM(CASE WHEN Cat=2 THEN 1 ELSE 0 END) AS TableBCount,
Num
FROM
(
SELECT Num,Type,1 AS Cat
FROM TableA
UNION ALL
SELECT Num,Type,2 
FROM TableB
)t
GROUP BY Num
[/code]
this will count of num from both tables as well 0 in cases where its missing from a table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources