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.
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 Type1 Metal2 Metal3 Metal3 Metal4 Metal5 Metal5 Metal6 Metal7 Metal... I also have another table (Table B) that looks like this:Num Item1 Metal2 Metal3 Metal4 Metal4 Metal5 Metal6 Metal7 Metal7 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 SetTableACount TableBCount Num1 1 11 1 22 1 31 2 42 1 51 1 61 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.NumFROM( SELECT Num, cnt = COUNT(*) FROM TableA WHERE Type = 'Metal' GROUP BY Num) taINNER 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 Type8 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 Type8 MetalAnd 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 thisSELECT SUM(CASE WHEN Cat=1 THEN 1 ELSE 0 END) AS TableACount,SUM(CASE WHEN Cat=2 THEN 1 ELSE 0 END) AS TableBCount,NumFROM(SELECT Num,Type,1 AS CatFROM TableAUNION ALLSELECT Num,Type,2 FROM TableB)tGROUP BY Num[/code]this will count of num from both tables as well 0 in cases where its missing from a table. |
|
|
|
|
|
|
|