CODEWITHSUNDEEP
sqlselectjoin
user5114459
user5114459

Reputation:

SQL Query Union with left join

i have one table (TableA) with some data and a foreign key to TableB 

TableA
 --------------------
| ID  | Name  | FK   |
 --------------------
   0     A      1
   1     B      3
   2     C      1
   3     D      2
   4     E      4
  ...

TableB
 ----------------
| PK  | Status  | 
 ----------------
   1    A21   
   2    A22   
   3    A23   
   4    A24

Now i want to Count how many entries in TableA have which Status from TableB... No problem so far:

SELECT Count(TableA.FK) AS COUNTA, TableB.Status AS Status
    FROM TableB
LEFT JOIN TableA ON TableB.PK = TableA.FK
    GROUP BY TableB.Status, TableB.PK;

Output:

 ------------------
| COUNTA  | Status | 
 ------------------
   2        A21   
   1        A22   
   1        A23   
   1        A24

Now ive got 3rd Table (TableC) with the same structure as TableA but different data.

TableC
 --------------------
| ID  | Name  | FK   |
 --------------------
   0     Ab      4
   1     Bb      3
   2     Cb      4
   3     Db      1
   4     Eb      1
  ...

i want now count TableA and TableC and output it in one table:

ex:

 -----------------------------
| COUNTA  | Status | COUNTC  |
 -----------------------------
   2        A21        2 
   1        A22        0
   1        A23        1
   1        A24        2

this is what i have so far:

SELECT        SELECT Count(TableA.FK) AS COUNTA, TableB.Status AS Status, Count(TableC.FK) AS COUNTC
FROM            ((TableB LEFT OUTER JOIN
                         TableA ON TableB.PK = TableA.FK) 
LEFT OUTER JOIN TableC ON TableB.PK = TableC.FK)
GROUP BY TableB.Status, TableB.PK;

Output is not correct, CountC ist the same as Count A :(

Upvotes: 1

Views: 68

Answers (2)

kedar kamthe
kedar kamthe

Reputation: 8178

select TempA.COUNTA , TempA.Status ,TempB.COUNTC from 
( SELECT Count(TableA.FK) AS COUNTA,TableB.Status AS Status ,TableA.FK as PK
     FROM TableB LEFT JOIN TableA ON TableB.PK = TableA.FK
 GROUP BY TableB.Status, TableB.PK) as TempA
 LEFT JOIN 
( SELECT Count(TableC.FK) AS COUNTC,TableA.FK as PK
     FROM TableC LEFT JOIN TableA ON TableC.PK = TableA.FK
 GROUP BY TableA.FK)  as TempB
 ON TempA.PK = TempB.PK)

Upvotes: 1

sagi
sagi

Reputation: 40481

You can add a correlated query to your first query to get the desired result like this:

SELECT Count(TableA.FK) AS COUNTA,
       TableB.Status AS Status,
       (SELECT COUNT(*) FROM TableC where TableC.FK = TableB.PK) as COUNTB
FROM TableB
LEFT JOIN TableA ON TableB.PK = TableA.FK
GROUP BY TableB.Status, TableB.PK;

Upvotes: 1

Related Questions