Nested join Query

Nested join Query



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"),





2 Answers
2



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


MAX


MIN


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'.


'yes'


'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



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 :


a.COUNTRY, a.CITY, a.SITE, a.Address, b.Remark


max()


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;



Required, but never shown



Required, but never shown






By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

-

Popular posts from this blog

Malaysia to Papua New Guinea by motorcycle?

Image
10 Is it possible to ride from Malaysia to Papua New Guinea by motorcycle. Any help would be much appreciated. transportation malaysia motorcycles papua-new-guinea share | improve this question edited Dec 12 '16 at 8:01 Mark Mayo ♦ 129k 76 569 1289 asked Dec 12 '16 at 7:26 Gareth Gareth 54 1 3 13 Um, no not unless your motorcycle flies. There's water in the way. – Roddy of the Frozen Peas Dec 12 '16 at 7:42 I presume the route is somehow Malaysia-East Timor-Darwin Australia and on. But even these fine fellas abikejourney.com have flown from Singapore to Australia. With the increasing difficulty in freighter tourism, this will be at least very challenging. – chx Dec 12 '16 at 10:01 add a comment  |  10 Is it possible to ride from Malaysia to Papua New Guinea by motorcycle. Any help would be much appreciated. transportation malaysia moto...
Read more

.name

Image
.name From Wikipedia, the free encyclopedia Jump to navigation Jump to search name Introduced 2001 TLD type Generic top-level domain Status Active Registry Verisign Sponsor None Intended use Personal sites of individuals Actual use Mostly used as intended. Registration restrictions No prior restriction on registration, but registrations can be challenged if not by or on behalf of individual with name similar to that of domain, or fictional character in which registrant has rights Structure Originally registrations had to be at third level, in form john.smith.name , but later direct second-level registrations were allowed Documents ICANN registry agreement Dispute policies UDRP, Disputes Registry Website Verisign .name Registry The domain name is a generic top-level domain (gTLD) in the Domain Name System of the Internet. It is intended for use by individuals for representation of their personal name, nicknames, screen names, pseudonyms, or...
Read more

List of Bhojpuri films

Image
List of Bhojpuri films From Wikipedia, the free encyclopedia Jump to navigation Jump to search List of Bhojpuri films : Contents 1 1950 to 1970 2 1980-89 3 1990-99 4 2000-2013 5 2014 6 2015 7 2016 8 2018 9 See also 10 External links 1950 to 1970 [ edit ] Aael Basant Bahar Balma Bara Nadan Bhouji (1965) Bidesiya (1963) Bidhana Nach Nachawe Ganga (1965) Ganga Maiyya Tohe Piyari Chadhaibo (1962) Goswami Tulsi Das Hamar Sansar Jekra Charanwa Me Lagale Paranwa Kab Hoihen Gawanwa Hamar Laagi Nahi Chhute Ram (1963) Loha Singh (1966) Nagpanchami Naihar Chhootal Jay Saiyan Se Bhaele Milanwan Sita Maeya Solhon Singar Kare Dulhaniya Toofan ayo langdo bhaago Hamar Betwa 1980-89 [ edit ] Aanchara Ke Laaj Aangan Ki Laxmi Babua Hamar Bahina Tohare Khatir Bahuria Bairi Kangana (1983) Bairi Sawan Baje Shahanai Hamar Angna Balma Nadan Bansuriya Baje Gangateer Bhaiya Bhaiya Dooj Bihari Babu Biraha Ki Raat Bitiya Bhail Sayan Bitiya Chalal Sasu...
Read more