join results of two queries of same table (one of which contains an inner join with a second table)

[stevieontario]

Morning freaks,

 

I have two tables, T1 and T2. I'm trying to perform two queries, both on T1, but the second one gets information from T2 using an inner join. I want to join these two queries to get a single set of results.

 

The queries work individually, but I was wondering if I can combine them into a single query.

 

This is T1:

filename hour unitname type production cost filename1.xls 1 unit_A x 10 100 filename1.xls 2 unit_A x 10 101 filename1.xls 1 unit_B y 20 50 filename1.xls 2 unit_B y 15 50 filename1.xls 1 unit_C y 30 89 filename1.xls 2 unit_C y 15 88 filename1.xls 1 unit_D y 30 54 filename1.xls 2 unit_D y 15 88

 

 

... and this is T2:

 

unitname type subtype unit_A x h unit_B y h unit_C y k unit_D y k

 

I want to query these tables so I get a result like this:

 

filename hour x_total y_total k_total filename1.xls 1 10 80 60 filename1.xls 2 10 45 30

 

I have been trying to somehow combine two queries. The first query is 

 

 

SELECT filename, hour,

       SUM(IF(type='x', production, 0)) AS x_total,

       SUM(IF(type='y', production, 0)) AS y_total

from T1

group by filename, hour

 

... and the second query is 

 

SELECT filename, hour ,

SUM( production ) AS k_total,

FROM T1

INNER JOIN T2 ON T2.unitname = T1.unitname

WHERE T2.subtype =  'k'

GROUP BY filename, hour

 

Each query works on its own, but I am just stumped over how to combine them into a single query. I have tried to join them but cannot get around the complication raised by the inner join in the second one.

 

Any ideas?

[Psycho]

I don't know what your actual data looks like, but I *think* the issue you are facing is probably due to the WHERE clause on the second query

 

WHERE T2.subtype =  'k'

 

Since that is not part of the first query, the second query is likely excluding data that is included in the first query. You should be able to solve this by implementing a LEFT JOIN and putting the unitname filter as part of the JOIN criteria

 

Give this a try

 

SELECT filename, hour,
       SUM(IF(type='x', production, 0)) AS x_total,
       SUM(IF(type='y', production, 0)) AS y_total,
       SUM(production) AS k_total,
 
FROM T1
LEFT JOIN T2
    ON T2.unitname = T1.unitname
    AND T2.subtype =  'k'
 
GROUP BY filename, hour

[stevieontario]

hmm.... the tables were nicely formatted when I posted this, but now (viewing on a different machine with a different browser) they appear to not be formatted at all. Let me try again.

 

This is T1

 

and this is T2

 

and I need a result that looks like this:

 

and the first query is 

SELECT filename, hour,

       SUM(IF(type='x', production, 0)) AS x_total,

       SUM(IF(type='y', production, 0)) AS y_total

from T1

group by filename, hour

 

and the second query is:

SELECT filename, hour ,

SUM( production ) AS k_total,

FROM T1

INNER JOIN T2 ON T2.unitname = T1.unitname

WHERE T2.subtype =  'k'

GROUP BY filename, hour

[stevieontario]

psycho, thanks -- your suggestion is getting me close to the mark I think -- it's producing a results table similar in structure to what I need. But the totals are not working. If I just include the "sum(if(type =....  " and exclude the "sum(output)..." then it gives the right sums, same if I include the "sum(output)..." and exclude the "sum(if(type...." 

 

I gave a confusing list of tables, can't figure out this forum's formatting to show the tables clearly. Let me paste as pdfs, give me a minute...

[stevieontario]

okay, tables and hoped-for results attached as jpgs.

 

thanks

[Psycho]

The queries you provided are referencing fields that do not exist in the tables you provided. Can't really help if the information provided is inacurate. Specifically, I don't see the following fields in the tables: production, unitname, & subtype