Join two queries that are grouped by same column from same tables but different parameters to join

Question

I have 3 tables with items, owners and statuses and I need to show the count of items that were sold/discarded grouped by every owner for the year passed as parameter.

I am able to get ownername and soldcount as one query and ownername and discardcount as second query but is there a way to structure so that ownername, soldcount and discardcount come in one query?

declare @QueryYear integer = 2020
    --SOLD
    select O1.pk_owner_id,count(P1.pk_Property_ID) as [SaleCount]
    from
        Item P1, Owner O1, Status S1
    WHERE    
        (C1.fkl_owner_ID = O1.pk_owner_ID and C1.fkl_item_ID=P1.pk_item_ID and O1.isactive=1 and year(P1.dtList_Date)=@QueryYear and P1.fkl_status_ID=1)
    group by 
        O1.pk_owner_id

    --DISCARD
    select O2.pk_owner_id,count(P2.pk_item_ID) as [DiscardCount]
    from
        item P2, owner O2, status C2
    WHERE    
        (C2.fkl_Owner_ID = O2.pk_owner_ID and C2.fkl_item_ID=P2.pk_item_ID and O2.isactive=1 and year(P2.dtList_Date)=@QueryYear and P2.fkl_item_status_ID=2)
    group by 
        O2.pk_owner_id

I used a Union and it gives answer in 2 columns only.

Select columns from a join on equality of a subrow that could be declared PK in both. X INNER JOIN Y ON c is rows with a subrow in X & a subrow in Y & where c. You want SOLD s INNER JOIN DISCARD d ON s.oid = d.oid since those are rows where (s.oid,s.sc) in s & (d.oid,d.dc) in d & s.oid=d.oid. But since the groupings are done on columns of the same 3-table cross join you could just summarize both columns conditionally with one grouping of the 3-table cross join. — philipxy, Jan 23 '20 at 23:57

pwilcox · Accepted Answer · 2020-01-24T16:21:02.697

1

Move your status filter to case statements in your select clause.

select      o.pk_owner_id,
            SaleCount = count(case when i.fkl_status_ID = 1 then 1 end),
            DiscardCount = count(case when i.fkl_item_status_ID = 2 then 1 end)
from        Status s 
join        Item i on s.fkl_item_ID = i.pk_item_ID
join        Owner o on s.fkl_owner_ID = o.pk_owner_ID                   
where       o.isactive = 1 
and         year(i.dtList_Date) = @QueryYear 
group by    o.pk_owner_id

Also, use relational operators to express relationships between tables, don't use the where clause. In this case, because the nature of the relationship is 'horizontal' in that each row in one table matches to each row in another table, you're looking for an (inner) join.

Finally, if you have more status types than '1' and '2', then you can add another condition to your joining of status with item, or put it in your where statement. Namely, you can do something like:

and i.fkl_status_ID in (1,2)

But I notice that the status_id columns have different names for SaleCount and DiscardCount. So if that's not an error, you'll need to do a parenthesized or version. But the main point is that your query will be more efficient if the processor knows to ignore statuses that are not '1' or '2'.

edited Jan 24 '20 at 16:21

answered Jan 23 '20 at 23:11

pwilcox

5,542
1
19
31

You might want to add a join predicate on `Item` like: `fkl_item_status_ID in (1, 2)` to make the query more efficient (there might be more than 2 possible values for this column). – GMB Jan 23 '20 at 23:25
It would be helpful if you would explain why this is an appropriate solution given the nature of the given subselects. See my comment on the question. Also your last sentence really is not clear. Especially since tables (and hence the subexpressiona whose values they are) represent relation(ship)s/associations, so "relationships between tables" is not clear. (UNION & JOIN both, and every table expression, generate tables of rows satisfying relationships in terms of base table relationships.) (Especially since also FKs get--wrongly--called "relationships between tables".) – philipxy Jan 24 '20 at 03:21
@GMB, I think I can find a way to work in your comment into my answer. Stand by for that. – pwilcox Jan 24 '20 at 16:01
@philipxy, when you say 'tables represent relationships', if you're saying that `select x, y from Z` expresses a relationship between x objects and y objects, then you're not wrong, but that would be relationships within tables, not between tables. 'Union' is definitely between tables. So to avoid limiting the OP's understanding of what this is, I can certainly broaden my statement to: `use relational operators, such as joins, ...`. Stand by for that. Thanks. – pwilcox Jan 24 '20 at 16:07
A table expression's value is the rows satisfying an associated predicate/function characterizing a relation(ship)/association. So we can reasonably say an expression or table denotes a relationship. Yes any operator has an associated relationship on its inputs & output. But your use of "relationship" & your writing are very unclear. (Try to define it.) And beg the question of what relational operator choice has to to with desired results. That's explained by [table-as-relationship](https://stackoverflow.com/a/33952141/3404097) & each table operator having an associated predicate operator. – philipxy Jan 25 '20 at 07:14
Say p is rows of values satisfying relation(ship)/association with characteristic predicate/function "person [p] has name [n] ..." & d is rows where "employee [e] works dept [d] ..." Then `p inner join d on c` is rows where "person [p.p] has name [p.n] ..." AND "employee [d.e] works dept [d.d] ..." AND c & `select p.p as e from p` is rows where EXISTS p.\* (p.p=e AND "person [p.p] has name [p.n] ...") & `(select p.p as e from p) union (select e from d)` is rows where EXISTS p.\* (p.p=e AND "person [p.p] has name [p.n] ...") OR EXISTS d.\* (d.e=e AND "employee [d.e] works dept [d.d] ..."). – philipxy Jan 25 '20 at 07:15
@philipxy, why don't you give a stab at a secondary answer with an explanation more towards what you have in mind. But keep in mind that the OP is a new user and clearly just getting started with SQL. Furthermore, anyone running into his problem would also most probably be a beginner. I've read your explanations, including your linked one, and I really can't imagine it being very intelligible to beginners without revision based on careful thought to pedagogical considerations in addition to technical ones. – pwilcox Jan 25 '20 at 16:00

Join two queries that are grouped by same column from same tables but different parameters to join

1 Answers1