Home » Open Source » MySQL » how to retrive all COUNTRY from a table (MYSQL Workbench 6.3)
| how to retrive all COUNTRY from a table [message #666709] |
Tue, 21 November 2017 03:19  |
 |
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
|
Member |
|
|
 Hi All,
Am working on purchasing application. Here we are search the supplier with filter and without filter. Please check screen shot.
when am not filter anything, it will give all COUNTRY---- it's fine
client search with a particular COUNTRY that time it will show that particular country but client want all the country with that particular country.
S1:- Search Without filter
S2:- Result of S1 Search
S3:- Search With filter
S4:- Result of S3 Search
this is the SQL.
select sup.FO_DUNS_ID DUNS_ID,
sup.SUPPLIER_NAME SUP_NAME, GROUP_CONCAT(DISTINCT entCntry.COUNTRY_FK) COUNTRY from SUPPLIER sup
LEFT OUTER JOIN TURNOVER trn ON sup.PROCUBE_SEQ_ID = trn.SUPPLIER_FK
LEFT OUTER JOIN TMP_ENTITY_COUNTRY entCntry ON entCntry.ENTITY_FK = trn.LEGAL_ENTITY_ID
where sup.PROCUBE_SEQ_ID = '006173082';
output:
DUNS_ID
'001007830',
SUP_NAME
'DRAPER BROTHERS COMPANY',
COUNTRY
'US,AR,BE,CZ,FR,GB,BR,MX,TR,CN,SE,DE,NL,DK,IT,IN,PL,ES,CA,NO,FI,AU,JP,CH,AT,IE,PT,RO,AE,QA,EE,ZA,HU,KR,EG,CO,LU,SG,MY,ID,TH,TW,NZ,MA, SK,LV,OM,VN,LT,KW,LB,RU,SA,JO,VE,RS,CL,HK,GR,UA,BT'
My question is that what changes should i make in SQL so i'll get all the country with that particular country.
Am not able to upload screenshot it's showing file size more that 1024kb but my screen shot size is less than 1024kb
Thanks,
Amit
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: how to retrive all COUNTRY from a table [message #666719 is a reply to message #666718] |
Tue, 21 November 2017 04:09  |
|
ajamitjain129@gmail.com
Messages: 36
Registered: July 2014
Location: bangalore
|
Member |
|
|
Am sorry... i don't know how to connect Db through sql plus. Please check complete SQl with and without filter.
With filter SQL
SELECT DISTINCT
(DUNS_ID),
SUP_NAME,
COUNTRY,
SIGNED,
CHARTVERSION,
CHARTER_VERSION_ID,
SIGN_DATE,
SEND_DATE,
RELAUNCH_DATE,
ACTIVITY,
ISGRPHEADER,
SUPPLIER_ID,MYSTATUS2
HASATIVECONTACT,
AMOUNT,
SUPADDR,
WCA_RATING,
GLOBAL_RATING,
CSRAVERAGE,
IS_TO_SEND,
STATUS,
SUBSIDIARIES,
IRRELEVANT,
APPROVE,
CSR_ASSESSMENT_SENT
FROM
((SELECT
sup.FO_DUNS_ID DUNS_ID,
sup.SUPPLIER_NAME SUP_NAME,
GROUP_CONCAT(DISTINCT entCntry.COUNTRY_FK) COUNTRY,
sign.SIGNED SIGNED,
charVersion.CHARTER_VERSION CHARTVERSION,
charVersion.CHARTER_VERSION_ID CHARTER_VERSION_ID,
sign.SIGNATURE_DATE SIGN_DATE,
sign.SEND_DATE SEND_DATE,
sign.RELAUNCH_DATE RELAUNCH_DATE,
GROUP_CONCAT(DISTINCT ssc.SUBCATEGORY_FK) ACTIVITY,
wca.WCA_RATING AS WCA_RATING,
csrInt.GLOBAL_RATING AS GLOBAL_RATING,
ecovadis.GLOBAL_RATING AS CSRAVERAGE,
sup.ISGRPHEADER AS ISGRPHEADER,
sup.PROCUBE_SEQ_ID AS SUPPLIER_ID,
(SELECT
MIN(CONTACT_ID)
FROM
CONTACT
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID
AND ACTIVE = 1) AS HASATIVECONTACT,
sup.AMOUNT,
sup.ADDRESS SUPADDR,
sign.IS_TO_SEND IS_TO_SEND,
st.STATUS STATUS,
si.IRRELEVANT IRRELEVANT,
pv.APPROVE APPROVE,
ca.ASSESSMENT_ID AS CSR_ASSESSMENT_SENT
FROM
SUPPLIER sup
LEFT OUTER JOIN SIGNATURE sign ON sup.PROCUBE_SEQ_ID = sign.SUPPLIER_FK
AND sign.SIGNATURE_ID = (SELECT
MAX(SIGNATURE_ID)
FROM
SIGNATURE
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN CHARTER_VERSION charVersion ON sign.CHARTER_VERSION_FK = charVersion.CHARTER_VERSION_ID
AND charVersion.CHARTER_VERSION_ID = (SELECT
MAX(CHARTER_VERSION_ID)
FROM
CHARTER_VERSION
WHERE
ACTIVE = 1)
LEFT OUTER JOIN SUPPLIER_QUESTIONNAIRE suppquest ON sup.PROCUBE_SEQ_ID = suppquest.SUPPLIER_FK
LEFT OUTER JOIN ECOVADIS_RATING ecovadis ON sup.PROCUBE_SEQ_ID = ecovadis.SUPPLIER_FK
AND ecovadis.ECOVADIS_ID = (SELECT
MAX(ECOVADIS_ID)
FROM
ECOVADIS_RATING
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN WCA_RATING wca ON sup.PROCUBE_SEQ_ID = wca.SUPPLIER_FK
AND wca.WCA_ID = (SELECT
MAX(WCA_ID)
FROM
WCA_RATING
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN CSR_INTERNAL_AUDIT csrInt ON sup.PROCUBE_SEQ_ID = csrInt.SUPPLIER_FK
AND csrInt.CSR_ID = (SELECT
MAX(CSR_ID)
FROM
CSR_INTERNAL_AUDIT
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN CSR_ASSESSMENT ca ON sup.PROCUBE_SEQ_ID = ca.SUPPLIER_FK
AND ca.ASSESSMENT_ID = (SELECT
MAX(ASSESSMENT_ID)
FROM
CSR_ASSESSMENT
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN CAP_STATUS cap ON sup.PROCUBE_SEQ_ID = cap.SUPPLIER_FK
LEFT OUTER JOIN SUPPLIER_SUBCATEGORIES ssc ON sup.PROCUBE_SEQ_ID = ssc.SUPPLIER_FK
LEFT OUTER JOIN TURNOVER trn ON sup.PROCUBE_SEQ_ID = trn.SUPPLIER_FK
LEFT OUTER JOIN TMP_ENTITY_COUNTRY entCntry ON entCntry.ENTITY_FK = trn.LEGAL_ENTITY_ID
LEFT OUTER JOIN SUPP_STATUS st ON st.SUPPLIER_ID = sup.PROCUBE_SEQ_ID
LEFT OUTER JOIN SUPP_IRRELEVANT si ON si.SUPPLIER_ID = sup.PROCUBE_SEQ_ID
LEFT OUTER JOIN PURCHASER_VALIDATION pv ON pv.SUPPLIER_FK = sup.PROCUBE_SEQ_ID
AND pv.APPROVE = - 1
WHERE
sup.AMOUNT > 0.0
AND (si.IRRELEVANT <> 1
OR si.IRRELEVANT IS NULL)
GROUP BY sup.PROCUBE_SEQ_ID
HAVING COUNTRY IN ('PT')
LIMIT 500) QR1
LEFT OUTER JOIN (SELECT
st.SUPPLIER_ID AS SUPPLIER_ID1,
GROUP_CONCAT(TRIM(su.SUPPLIER_NAME), ' - ', IF(sg.SIGNATURE_DATE IS NOT NULL, 'SIGNED', 'NOT SIGNED'), ',') SUBSIDIARIES
FROM
SUPP_STATUS st
INNER JOIN SUPPLIER_HIERARCHY sh ON st.SUPPLIER_ID = sh.GROUP_HEADER_ID
AND sh.GROUP_HEADER_ID != sh.PROCUBE_SEQ_ID
LEFT OUTER JOIN SUPPLIER su ON su.PROCUBE_SEQ_ID = sh.PROCUBE_SEQ_ID
INNER JOIN SIGNATURE sg ON sg.SUPPLIER_FK = sh.PROCUBE_SEQ_ID
WHERE
st.STATUS = 'VS'
AND sg.SIGNATURE_DATE IS NOT NULL
GROUP BY SUPPLIER_ID1) QR2 ON QR1.SUPPLIER_ID = QR2.SUPPLIER_ID1)
ORDER BY ISGRPHEADER , SUP_NAME , AMOUNT , CHARTER_VERSION_ID DESC;
without filter :--
SELECT DISTINCT
(DUNS_ID),
SUP_NAME,
COUNTRY,
SIGNED,
CHARTVERSION,
CHARTER_VERSION_ID,
SIGN_DATE,
SEND_DATE,
RELAUNCH_DATE,
ACTIVITY,
ISGRPHEADER,
SUPPLIER_ID,
HASATIVECONTACT,
AMOUNT,
SUPADDR,
WCA_RATING,
GLOBAL_RATING,
CSRAVERAGE,
IS_TO_SEND,
STATUS,
SUBSIDIARIES,
IRRELEVANT,
APPROVE,
CSR_ASSESSMENT_SENT
FROM
((SELECT
sup.FO_DUNS_ID DUNS_ID,
sup.SUPPLIER_NAME SUP_NAME,
GROUP_CONCAT(DISTINCT entCntry.COUNTRY_FK) COUNTRY,
sign.SIGNED SIGNED,
charVersion.CHARTER_VERSION CHARTVERSION,
charVersion.CHARTER_VERSION_ID CHARTER_VERSION_ID,
sign.SIGNATURE_DATE SIGN_DATE,
sign.SEND_DATE SEND_DATE,
sign.RELAUNCH_DATE RELAUNCH_DATE,
GROUP_CONCAT(DISTINCT ssc.SUBCATEGORY_FK) ACTIVITY,
wca.WCA_RATING AS WCA_RATING,
csrInt.GLOBAL_RATING AS GLOBAL_RATING,
ecovadis.GLOBAL_RATING AS CSRAVERAGE,
sup.ISGRPHEADER AS ISGRPHEADER,
sup.PROCUBE_SEQ_ID AS SUPPLIER_ID,
(SELECT
MIN(CONTACT_ID)
FROM
CONTACT
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID
AND ACTIVE = 1) AS HASATIVECONTACT,
sup.AMOUNT,
sup.ADDRESS SUPADDR,
sign.IS_TO_SEND IS_TO_SEND,
st.STATUS STATUS,
si.IRRELEVANT IRRELEVANT,
pv.APPROVE APPROVE,
ca.ASSESSMENT_ID AS CSR_ASSESSMENT_SENT
FROM
SUPPLIER sup
LEFT OUTER JOIN SIGNATURE sign ON sup.PROCUBE_SEQ_ID = sign.SUPPLIER_FK
AND sign.SIGNATURE_ID = (SELECT
MAX(SIGNATURE_ID)
FROM
SIGNATURE
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN CHARTER_VERSION charVersion ON sign.CHARTER_VERSION_FK = charVersion.CHARTER_VERSION_ID
AND charVersion.CHARTER_VERSION_ID = (SELECT
MAX(CHARTER_VERSION_ID)
FROM
CHARTER_VERSION
WHERE
ACTIVE = 1)
LEFT OUTER JOIN SUPPLIER_QUESTIONNAIRE suppquest ON sup.PROCUBE_SEQ_ID = suppquest.SUPPLIER_FK
LEFT OUTER JOIN ECOVADIS_RATING ecovadis ON sup.PROCUBE_SEQ_ID = ecovadis.SUPPLIER_FK
AND ecovadis.ECOVADIS_ID = (SELECT
MAX(ECOVADIS_ID)
FROM
ECOVADIS_RATING
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN WCA_RATING wca ON sup.PROCUBE_SEQ_ID = wca.SUPPLIER_FK
AND wca.WCA_ID = (SELECT
MAX(WCA_ID)
FROM
WCA_RATING
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN CSR_INTERNAL_AUDIT csrInt ON sup.PROCUBE_SEQ_ID = csrInt.SUPPLIER_FK
AND csrInt.CSR_ID = (SELECT
MAX(CSR_ID)
FROM
CSR_INTERNAL_AUDIT
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN CSR_ASSESSMENT ca ON sup.PROCUBE_SEQ_ID = ca.SUPPLIER_FK
AND ca.ASSESSMENT_ID = (SELECT
MAX(ASSESSMENT_ID)
FROM
CSR_ASSESSMENT
WHERE
SUPPLIER_FK = sup.PROCUBE_SEQ_ID)
LEFT OUTER JOIN CAP_STATUS cap ON sup.PROCUBE_SEQ_ID = cap.SUPPLIER_FK
LEFT OUTER JOIN SUPPLIER_SUBCATEGORIES ssc ON sup.PROCUBE_SEQ_ID = ssc.SUPPLIER_FK
LEFT OUTER JOIN TURNOVER trn ON sup.PROCUBE_SEQ_ID = trn.SUPPLIER_FK
LEFT OUTER JOIN TMP_ENTITY_COUNTRY entCntry ON entCntry.ENTITY_FK = trn.LEGAL_ENTITY_ID
LEFT OUTER JOIN SUPP_STATUS st ON st.SUPPLIER_ID = sup.PROCUBE_SEQ_ID
LEFT OUTER JOIN SUPP_IRRELEVANT si ON si.SUPPLIER_ID = sup.PROCUBE_SEQ_ID
LEFT OUTER JOIN PURCHASER_VALIDATION pv ON pv.SUPPLIER_FK = sup.PROCUBE_SEQ_ID
AND pv.APPROVE = - 1
WHERE
sup.AMOUNT > 0.0
AND (si.IRRELEVANT <> 1
OR si.IRRELEVANT IS NULL)
GROUP BY sup.PROCUBE_SEQ_ID
LIMIT 500) QR1
LEFT OUTER JOIN (SELECT
st.SUPPLIER_ID AS SUPPLIER_ID1,
GROUP_CONCAT(TRIM(su.SUPPLIER_NAME), ' - ', IF(sg.SIGNATURE_DATE IS NOT NULL, 'SIGNED', 'NOT SIGNED'), ',') SUBSIDIARIES
FROM
SUPP_STATUS st
INNER JOIN SUPPLIER_HIERARCHY sh ON st.SUPPLIER_ID = sh.GROUP_HEADER_ID
AND sh.GROUP_HEADER_ID != sh.PROCUBE_SEQ_ID
LEFT OUTER JOIN SUPPLIER su ON su.PROCUBE_SEQ_ID = sh.PROCUBE_SEQ_ID
INNER JOIN SIGNATURE sg ON sg.SUPPLIER_FK = sh.PROCUBE_SEQ_ID
WHERE
st.STATUS = 'VS'
AND sg.SIGNATURE_DATE IS NOT NULL
GROUP BY SUPPLIER_ID1) QR2 ON QR1.SUPPLIER_ID = QR2.SUPPLIER_ID1)
ORDER BY ISGRPHEADER , SUP_NAME , AMOUNT , CHARTER_VERSION_ID DESC;
my question is very simple..one supplier belong to multiple countries....
for example:
A is supplier who belongs to
INDIA
NEW ZEALAND
AUSTRALIA
NETHERLANDS
LUXEMBOURG
BELGIUM
URUGUAY
PARAGUAY
CHILE
NOW if i search supplier with country ='INDIA' then it should return all countries with india.
Thanks,
Amit
|
|
|
|
| Re: how to retrive all COUNTRY from a table [message #666721 is a reply to message #666719] |
Tue, 21 November 2017 04:29 |
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Again all the countries with india is: india.
saying you want all the countries with the country specified will never make sense.
You need to explain in detail the relationship between the countries you want in the output to the country specified in the filter.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 11:56:25 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
