CODEWITHSUNDEEP
mysqlsql
IUissopretty
IUissopretty

Reputation: 81

Join Counts of Different Attributes

Suppose I have 3 tables:

T1(A, B, CountX)

T2(A, B, CountY)

T3(A, B, CountZ)

And I want to create the following table:

T123(A, B, CountX, CountY, CountZ)

where T1, T2, and T3 are joined by the tuple A, B being the same for all 3 tables. How would I accomplish this?

EDIT:

Sorry, I didn't specify this earlier, but instead of inner join, what about outer join? There could be counts of 0 for X, Y, and Z instead of deleting them from the table. For example, I wish an entry such as this:

a, b, 2, 0, 1

to exist in the table, but T2 may not contain an entry with a, b, 0. Some tuple of a, b might not exist in T1, but it might exist in T2. In T1, T2, and T3, there are no tables with counts = 0

Upvotes: 0

Views: 55

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

I would recommend union all and aggregation:

select a, b, max(countx) as countx, max(county) as county, max(countz) as countz
from ((select a, b, countx, null as county, null as countz
       from t1
      ) union all
      (select a, b, null as countx, county, null as countz
       from t2
      ) union all
      (select a, b, null as countx, null as county, countz
       from t3
      )
     ) t123
group by a, b;

Upvotes: 1

zip
zip

Reputation: 4061

Try this below:

Select a.A, a.B, T1.CountX, T2.CountY, T3.CountZ int T123
from 
(Select distinct A, B from T1 union Select distinct A, B from T2 union Select distinct A, B from T3) a  
inner join T1 on a.A = T1.A and a.B = T1.B
inner join T2 on a.A = T2.A and a.B = T2.B
inner join T3 on a.A = T3.A and a.B = T3.B

Let me know

Upvotes: 0

dan1st
dan1st

Reputation: 16447

Just select the values from the tables where (inner join on) A and B is the same:

SELECT T1.A as A, T1.B as B, CountX, CountY, CountZ
FROM T1,T2,T3
WHERE T1.A=T2.A AND T2.A=T3.A
    AND T1.B=T2.B AND T2.B=T3.B

Upvotes: 0

GMB
GMB

Reputation: 222582

You want simple joins:

select t1.*, t2.CountY, t3.CountZ
from t1
inner join t2 on t2.A = t1.A and t2.B = t1.B
inner join t3 on t3.A = t1.A and t3.B = t1.B

You can turn this into a create table statement as follows:

create table t123 as
select t1.*, t2.CountY, t3.CountZ
from t1
inner join t2 on t2.A = t1.A and t2.B = t1.B
inner join t3 on t3.A = t1.A and t3.B = t1.B

Beware that creating tables like this does not allow defining pripary keys, foreign keys constraints, indexes or other such table attributes. It is generally a better practice to create the table first, and then populate it, using the insert ... select ... syntax.

Upvotes: 0

Related Questions