Nested join Query

Question

I have a SQL query:

SELECT  a.LAN_ABBR, a.COUNTRY, a.CITY, a.SITE, a.Address,
        c.Network_Priority, c.Dedicated_Network, c.Other_Network,
        c.LAN_One, c.LAN_two, c.LAN_three,
        c.LAN_four, c.LAN_five, c.LAN_six, c.LAN_seven,
        b.Remark
    FROM  server_data a
    LEFT OUTER JOIN  site_remark b  ON a.SITE = b.SITE_ABBR
    INNER JOIN       lan_service c  ON a.LAN_ABBR = c.LAN;

This Query is giving me output:

LAN_ABBR || COUNTRY || CITY || SITE || Address || Network_Priority || Dedicated_Network || Other_Network || LAN_One|| LAN_two || LAN_three || LAN_four || LAN_five || LAN_six || LAN_seven || Remark
==================================================================================================================================================================================================================
ABC1     || India   || Delhi|| XYZ1 || Delhi 1 || yes              || no                || no            || yes    || no      || yes       || yes      || yes      || no      || no        || Delhi Network 1 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABC2     || India   || Delhi|| XYZ1 || Delhi 1 || no               || no                || yes           || no     || yes     || no        || no       || no       || yes     || no        || Delhi Network 1 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABC3     || India   || Delhi|| XYZ1 || Delhi 1 || yes              || no                || no            || yes    || no      || no        || no       || no       || no      || no        || Delhi Network 1 
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ABC4     || India   || Delhi|| XYZ2 || Delhi 1 || yes              || no                || yes           || no     || yes     || no        || no       || no       || yes     || yes       || Delhi Network 4 
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    

I want a result which looks like:

 COUNTRY || CITY || SITE || Address || Network_Priority || Dedicated_Network || Other_Network || LAN_One|| LAN_two || LAN_three || LAN_four || LAN_five || LAN_six || LAN_seven || Remark
==================================================================================================================================================================================================================
 India   || Delhi|| XYZ1 || Delhi 1 || yes              || no                || yes           || yes    || yes     || yes       || yes      || yes      || yes     || no        || Delhi Network 1 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 India   || Delhi|| XYZ2 || Delhi 1 || yes              || no                || yes           || no     || yes     || no        || no       || no       || yes     || yes       || Delhi Network 4 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------    

Logic:

If a.Site has a duplicate entry 

{

check(

c.Network_Priority (Single entry for yes no: If even single "yes" then complete "yes" else "no"), 

c.Dedicated_Network (Single entry for yes no: If even single "yes" then complete "yes" else "no"), 

c.Other_Network (Single entry for yes no: If even single "yes" then complete "yes" else "no"), 

c.LAN_One (Single entry for yes no: If even single "yes" then complete "yes" else "no"), 

c.LAN_two (Single entry for yes no: If even single "yes" then complete "yes" else "no"), 

c.LAN_three (Single entry for yes no: If even single "yes" then complete "yes" else "no"), 

c.LAN_four (Single entry for yes no: If even single "yes" then complete "yes" else "no"), 

c.LAN_five (Single entry for yes no: If even single "yes" then complete "yes" else "no"), 

c.LAN_six (Single entry for yes no: If even single "yes" then complete "yes" else "no"), 

c.LAN_seven (Single entry for yes no: If even single "yes" then complete "yes" else "no"),

} 

Answer 1

You may use the following with grouping by a.COUNTRY, a.CITY, a.SITE, a.Address, b.Remark, and use max() for related cases as follows :

SELECT a.COUNTRY, a.CITY, a.SITE, a.Address, 
       (case when max(c.Network_Priority)='yes' then
        else 'no'
        end) as Network_Priority, 
       (case when max(c.Dedicated_Network)='yes' then
        else 'no'
        end) as Dedicated_Network,
       (case when max(c.Other_Network)='yes' then
        else 'no'
        end) as Other_Network,
       (case when max(c.LAN_one)='yes' then
        else 'no'
        end) as LAN_one,
       (case when max(c.LAN_two)='yes' then
        else 'no'
        end) as LAN_two,
       (case when max(c.LAN_three)='yes' then
        else 'no'
        end) as LAN_three,
       (case when max(c.LAN_four)='yes' then
        else 'no'
        end) as LAN_four,
       (case when max(c.LAN_five)='yes' then
        else 'no'
        end) as LAN_five,
       (case when max(c.LAN_six)='yes' then
        else 'no'
        end) as LAN_six,
       (case when max(c.LAN_seven)='yes' then
        else 'no'
        end) as c.LAN_seven,
       b.Remark
  FROM server_data a 
  LEFT OUTER JOIN site_remark b 
    ON a.SITE = b.SITE_ABBR 
 INNER JOIN lan_service c 
    ON a.LAN_ABBR = c.LAN
GROUP BY a.COUNTRY, a.CITY, a.SITE, a.Address, b.Remark;

Answer 2

You can try to use MAX and MIN function to get your expect result.

SELECT  a.COUNTRY, 
        a.CITY, 
        a.SITE, 
        a.Address, 
        MAX(c.Network_Priority), 
        MAX(c.Dedicated_Network), 
        MAX(c.Other_Network), 
        MAX(c.LAN_One),
        MAX(c.LAN_two), 
        MAX(c.LAN_three), 
        MAX(c.LAN_four), 
        MAX(c.LAN_five), 
        MAX(c.LAN_six), 
        MAX(c.LAN_seven), 
        MIN(b.Remark) Remark
FROM server_data a 
LEFT OUTER JOIN site_remark b ON a.SITE = b.SITE_ABBR 
INNER JOIN lan_service c ON a.LAN_ABBR = c.LAN
GROUP BY a.COUNTRY,a.CITY,a.SITE,a.Address

Here is a simple

CREATE TABLE T(
  LAN_ABBR VARCHAR(50), 
  COUNTRY VARCHAR(50), 
  CITY VARCHAR(50), 
  SITE VARCHAR(50), 
  Address  VARCHAR(50), 
  Network_Priority  VARCHAR(50), 
  Dedicated_Network  VARCHAR(50), 
  Other_Network  VARCHAR(50), 
  LAN_One VARCHAR(50),
  LAN_two  VARCHAR(50), 
  LAN_three  VARCHAR(50), 
  LAN_four  VARCHAR(50), 
  LAN_five  VARCHAR(50),
  LAN_six  VARCHAR(50),
  LAN_seven  VARCHAR(50), 
  Remark VARCHAR(50)
);

INSERT INTO T VALUES ('ABC1','India','Delhi','XYZ1','Delhi 1','yes','no','no','yes','no','yes','yes','yes','no','no','Delhi Network 1'); 
INSERT INTO T VALUES ('ABC2','India','Delhi','XYZ1','Delhi 1','no','no','yes','no','yes','no','no','no','yes','no','Delhi Network 1'); 
INSERT INTO T VALUES ('ABC3','India','Delhi','XYZ1','Delhi 1','yes','no','no','yes','no','no','no','no','no','no','Delhi Network 1'); 
INSERT INTO T VALUES ('ABC4','India','Delhi','XYZ2','Delhi 1','yes','no','yes','no','yes','no','no','no','yes','yes','Delhi Network 4'); 

Query 1:

SELECT  COUNTRY, 
        CITY, 
        SITE, 
        Address, 
        MAX(Network_Priority) Network_Priority, 
        MAX(Dedicated_Network) Dedicated_Network, 
        MAX(Other_Network) Other_Network, 
        MAX(LAN_One) LAN_One,
        MAX(LAN_two) LAN_two, 
        MAX(LAN_three) LAN_three, 
        MAX(LAN_four) LAN_four, 
        MAX(LAN_five) LAN_five, 
        MAX(LAN_six) LAN_six, 
        MAX(LAN_seven) LAN_seven, 
        MIN(Remark) Remark 
FROM T
GROUP BY COUNTRY,CITY,SITE,Address

Results:

| COUNTRY |  CITY | SITE | Address | Network_Priority | Dedicated_Network | Other_Network | LAN_One | LAN_two | LAN_three | LAN_four | LAN_five | LAN_six | LAN_seven |          Remark |
|---------|-------|------|---------|------------------|-------------------|---------------|---------|---------|-----------|----------|----------|---------|-----------|-----------------|
|   India | Delhi | XYZ1 | Delhi 1 |              yes |                no |           yes |     yes |     yes |       yes |      yes |      yes |     yes |        no | Delhi Network 1 |
|   India | Delhi | XYZ2 | Delhi 1 |              yes |                no |           yes |      no |     yes |        no |       no |       no |     yes |       yes | Delhi Network 4 |

NOTE

this query assumes there is only three two type in your columns 'yes' or 'no'.

If there are other states in your columns,you can try to use condition aggregate function

SELECT  COUNTRY, 
        CITY, 
        SITE, 
        Address, 
        MAX(Network_Priority) Network_Priority, 
        MAX(CASE WHEN Dedicated_Network='yes' then 'yes' else 'no' end) Dedicated_Network, 
        MAX(CASE WHEN Other_Network='yes' then 'yes' else 'no' end) Other_Network, 
        MAX(CASE WHEN LAN_One='yes' then 'yes' else 'no' end) LAN_One,
        MAX(CASE WHEN LAN_two='yes' then 'yes' else 'no' end) LAN_two, 
        MAX(CASE WHEN LAN_three='yes' then 'yes' else 'no' end) LAN_three, 
        MAX(CASE WHEN LAN_four='yes' then 'yes' else 'no' end) LAN_four, 
        MAX(CASE WHEN LAN_five='yes' then 'yes' else 'no' end) LAN_five, 
        MAX(CASE WHEN LAN_six='yes' then 'yes' else 'no' end) LAN_six, 
        MAX(CASE WHEN LAN_seven='yes' then 'yes' else 'no' end) LAN_seven, 
        MIN(Remark) Remark 
FROM T
GROUP BY COUNTRY,CITY,SITE,Address