Getting and calculating data from 3 unrelated mysql tables

I am busting my head over (what seems to be quite) simple solution but couldn't get it to work.

I have 3 different tables I need to get information from. They can be related with id column. What I want to do is:

1. Check how many times id appears in table1 and SUM() all values of corresponding table1.data fields as 开发者_运维知识库sum1

2. Check how many times id appears in table2 and SUM() all values of corresponding table2.data fields as sum2

3. Check how many times id appears in table3 and SUM() all values of corresponding table3.data fields as sum3

I could use 3 different queries to solve this but would realy love to have a single MySQL query to return results.

well, i think the idea of one query is definitely possible. try something like this:

select c1.id
     , sum(c1.val) as sum1, count(distinct(c1.id)) as cnt1
     , sum(c2.val) as sum2, count(distinct(c2.id)) as cnt2
     , sum(c3.val) as sum3, count(distinct(c3.id)) as cnt3
from c1, c2, c3
where c1.id = c2.id
and c2.id = c3.id
group by id

you may need outer joins if your data is not fully populated in all tables.

Guy is right, what you are trying to achieve isn't really possible with one query. You will need to look at using count functions on distinct IDs, e.g.

SELECT COUNT(DISTINCT t1.[ID]) FROM t1 SELECT COUNT(DISTINCT t2.[ID]) FROM t2 SELECT COUNT(DISTINCT t3.[ID]) FROM t3

etc.

It doesn't make any sense putting all these queries into one:

1. the data does not relate with each other
2. any possible approach will still contain 3 queries; the question remains only is either you will express two of them as sub-queries of the first query, or call each query separately.