[RESOLVED] Merge 2 result sets with common columns

dmacman1962

Hi All,

I am trying to merge 2 results from queries into one array (so I can iterate thru it and show all the results in one set [note: some of the rows from one query may not have a match in the other query]).

Example query A:
Subject-Grade-Item_Class_Count (query results)

Accounting ---- K-3 ---- 26
Accounting ---- 4 ------- 3
Accounting ---- 5 ------- 23
Agriculture ---- K-3 ---- 10
Agriculture ---- 5 ------- 11

Example Query B:
Subject-Grade-Class_Count (query results)

Accounting ---- K-3 ---- 11
Accounting ---- 4 ------- 2
Agriculture ---- K-3 ---- 7
Agriculture ---- 5 ------- 2
Agriculture ---- 6 ------- 11

What I would like to return (with the matches on either side, some may only have results on one side of the joined array)

Subject-Grade-Item_Class_Count_Class_Count
Accounting ---- K-3 ---- 26 ---- 11
Accounting ---- 4 ------- 3 ------ 2
Accounting ---- 5 ------- 23 (no matching results from Query 😎
Agriculture ---- K-3 ---- 10 ----- 7
Agriculture ---- 5 ------- 11 ----- 2
Agriculture ---- 6 ------- ------ 11 (no matching results from Query A)

I tried to merge the 2 queries, but ended up with incorrect results.

Query A: (Subject-Grade-Item_Class_Count)

select s.value as 'SUBJECT', g.value as 'GRADE', count(*) as 'ITEM CLASS COUNT'
from  itemusedfor as i
join subjectarea as s
on s.id = i.subjectareaid
join gradelevel as g
on g.id = i.gradelevelid
group by s.value, g.weight

Query B: (Subject-Grade-Class_Count)

select s.value as 'SUBJECT', g.value as 'GRADE', count(*) as 'CLASS COUNT'
from  teachesclass as t
join subjectarea as s
on s.id = t.subjectarea
join gradelevel as g
on g.id = t.gradelevelid
group by s.value, g.weight

As always, I appreciate the assistance.

Regards,
Don

laserlight

Just wondering, but how did you end up with two tables like this in the first place? It might be easier to just re-design the database schema.

dmacman1962

I don't have an exact answer for you. Our DBA programmer designed the database.

I do know that it was setup with a 'teachesclass' table that stores all the grade-subject combination's that each teacher teaches. And that the 'itemsused' relates the items used in teaching that class and the grade-subject records.

I do know that it was setup that way for reports that he needs to run which he felt would not let him return the results he needed, and another reason, which I don't recall at the moment.

Is it possible to merge the 2 results or will I have to display them in 2 separate lists?

Thanks,
Don

laserlight

Ah, but that makes it sound like the design is a correct one.

What are the two queries, independently? A LEFT JOIN might be able to solve this.

dmacman1962

Here are the 2 queries, which return all the correct records independently:

Query A:

select s.value as 'SUBJECT', g.value as 'GRADE', count(*) as 'ITEM CLASS COUNT'
from  itemusedfor as i
join subjectarea as s
on s.id = i.subjectareaid
join gradelevel as g
on g.id = i.gradelevelid
group by s.value, g.weight

And query B:

select s.value as 'SUBJECT', g.value as 'GRADE', count(*) as 'CLASS COUNT'
from  teachesclass as t
join subjectarea as s
on s.id = t.subjectarea
join gradelevel as g
on g.id = t.gradelevelid
group by s.value, g.weight

What I would like to do is match up the Subject - Grade for each, and show the results for the distinct columns (even if one query has a result and the other does not, like my example shows).

Does that make sense?

laserlight

Yes, that makes sense. A very simple solution comes to mind, if your DBA allows it: create two views, one with each of these queries. Then select from one of the views left join with the other on the subject and grade.

dmacman1962

Thanks LL,

I talked to him and he just did the same thing for another task, and it slipped his mind. He said he should have thought of that, and said to thank you. I will close the thread after I get his result set back.

Don

dmacman1962

I resolved this like laserlight suggested, with 2 views, then queried the views.

vwclasscount view

select `s`.`Value` AS `SUBJECT`,
`g`.`Value`AS `GRADE`,
count(0) AS `CLASS COUNT`
from ((`teachesclass` `t`
join `subjectarea` `s` on((`s`.`Id` = `t`.`SubjectArea`)))
join `gradelevel` `g` on((`g`.`Id` = `t`.`GradeLevelId`)))
group by `s`.`Value`,`g`.`Weight`

vwitemclasscount view

select `s`.`Value` AS `SUBJECT`,`g`.
Value` AS `GRADE`,
count(0) AS `ITEM CLASS COUNT`
from ((`itemusedfor` `i`
join `subjectarea` `s` on((`s`.`Id` = `i`.`SubjectAreaId`)))
join `gradelevel` `g` on((`g`.`Id` = `i`.`GradeLevelId`)))
group by `s`.`Value`,`g`.`Weight`

Then queried them to get my desired results:

$query = "select c.SUBJECT, c.GRADE, c.`Class Count`, i.`Item Class Count`
from vwClassCount as c
join vwItemClassCount as i
on c.SUBJECT = i.SUBJECT
and c.GRADE = i.GRADE";

Thanks laserlight, that did the trick!

Don

laserlight

You're welcome 🙂

By the way, it looks like you are using an inner join rather than a left join in the last snippet. Does that actually satisfy your requirements?