CODEWITHSUNDEEP
sqlsql-serverselectjoincount
astrakid
astrakid

Reputation: 31

SQL join on different select count result-tables

I need some help on sql-joins. I have 3 select statements, which give me an output like this:

select-statement A:
location amount_A
7234     17
7456     2

select-statement B:
location number_x
7234     4455
7456     555

select-statement C:
location errors
7234     1
7456     44537

I want to have the result in one table like this:

location Amount_A  number_x  errors
7234     17        4455      1
7456     2         555       44537

what is the best and/or easiest way to achieve this? Each select-statement uses other tables.?!

these are the statements:

A: select substring(column_a for 4) location, count(*) Amount_A from table_a where column_a like '7%' group by location  ;
B: select substring(e.column_xy for 4), count(*) number_x from table_b b, table_e e , table_c c where b.stationextension_id = e.id and b.id = c.id and ( c.column_h in ( 'value_a', 'value_b' ) ) group by substring(e.column_xy for 4)  ;
C: select substring(name from 1 for 4), count(*) from errors group by substring(name from 1 for 4) ;

Upvotes: 1

Views: 428

Answers (4)

Praveen
Praveen

Reputation: 9355

Try this:

select 
  a.location, a.amount_A,
  b.number_x,
  c.errors
from (
  select_satement_A
) as a
join (
  select_satement_B
) as b on a.location = b.location
join (
  select_satement_C
) as c on a.location = c.location

If all data from select_satement_A needs to be retrieved use left join. All data from select_satement_A will be retrieved with corresponding data fromselect_satement_B and select_satement_C. If no match for the join condition found null will be substituted.

   select 
      a.location, a.amount_A,
      b.number_x,
      c.errors
    from (
      select_satement_A
    ) as a
    left join (
      select_satement_B
    ) as b on a.location = b.location
    left join (
      select_satement_C
    ) as c on a.location = c.location

For all data to be retrieved use full join.

Upvotes: 1

Steven Powell
Steven Powell

Reputation: 36

You could always combine into one statement with some left outer joins. Locations may not have any errors i suppose.

Without a better understanding of the base data it's a bit tricky to get the joins in the correct order but i hope this points you in the right direction.

select  substring(e.column_xy for 4) location, 
        count(a.column_xy)           Amount_A, 
        count(e.ie)                  number_x, 
        count(e2.name)               errors
  from  table_b b
  inner join  table_e  e on b.stationextension_id        = e.id
  inner join  table_c  c on b.id                         = c.id
  left  join  table_a  a on substring(e.column_xy for 4) = substring(a.column_a from 1 for 4)
                        and a.column_a                like '7%'
  left  join  errors  e2 on substring(e.column_xy for 4) = substring(e2.name    from 1 for 4)
  where c.column_h in ( 'value_a', 'value_b' ) 
  group by substring(e.column_xy for 4);

Upvotes: 0

A Hocevar
A Hocevar

Reputation: 726

You can join all three queries into one

SELECT s1.location,
       s1.Amount_A,
       s2.number_x,
       s3.errors
  FROM (SELECT SUBSTRING (column_a FOR 4) AS location,
    COUNT(*) AS Amount_A
  FROM table_a
    WHERE column_a LIKE '7%'
  GROUP BY location) s1
  JOIN ( SELECT SUBSTRING(e.column_xy FOR 4) AS location,
    COUNT(*) AS number_x
  FROM table_b b
    JOIN table_e e ON b.stationextension_id = e.id
    JOIN table_c c ON b.id = c.id
  WHERE c.column_h IN ( 'value_a', 'value_b' )
    GROUP BY SUBSTRING(e.column_xy FOR 4)) s2
    ON s1.location = s2.location
  JOIN (SELECT SUBSTRING(name FROM 1 FOR 4) AS location,
  COUNT(*) AS errors
  FROM errors
    GROUP BY SUBSTRING(name FROM 1 FOR 4)) s3 ON s1.location = s3.location

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93754

Use Inner Join to connect all the three queries.

Also use Inner Join syntax to join two tables which is more readable then old style comma separated join.

Substring function has some different arguments hope those are example in original query you need pass proper values to substring function

select a.location,Amount_A ,number_x,errors from
(
SELECT substring(column_a for 4) location, count(*) Amount_A FROM table_a WHERE column_a LIKE '7%' GROUP BY location  
) A 
inner join
(
SELECT substring(e.column_xy for 4) location , count(*) number_x FROM table_b b inner join table_e e on  b.stationextension_id = e.id and b.stationextension_id = e.id 
inner join  table_c c on  b.id = c.id 
where c.column_h IN ( 'value_a', 'value_b' ) ) GROUP BY substring(e.column_xy FOR 4)  ;
) B on a.location = b.location 
inner join 
(
SELECT substring(name from 1 FOR 4) location, count(*) errors FROM errors GROUP BY substring(name FROM 1 FOR 4) ;
)
C on c.location = b.location

Upvotes: 1

Related Questions