Combine 2 Select queries with different conditions

Question

i current have 2 Select queries at mySQL.

SELECT Provider Name, Country, GroupUpdateID FROM provider WHERE Country="Vietnam" AND Provider Name="Provider A"
SELECT GROUP_CONCAT(DISTINCT Country) AS result FROM provider WHERE GroupUpdateID="Group1"

How can i combine these 2 queries such that i am able to display the provider Name, Country, groupUpdateID and a new column named Result (which will contain country names which share the same GroupUpdateID)?

I am stuck because i have no idea on how to produce a single query since they both have different WHERE criteria.

This has been answered here: stackoverflow.com/questions/542705/… — David Biga
– David Biga, Commented Feb 23, 2017 at 6:50

Thorsten Kettner · Accepted Answer · 2017-02-23 07:06:24Z

0

Modify your second query such as to get the country list per GroupUpdateID (i.e. use GROUP BY):

SELECT
  p.ProviderName, 
  p.Country, 
  p.GroupUpdateID,
  c.result
FROM provider p
JOIN
(
  SELECT GroupUpdateID, GROUP_CONCAT(DISTINCT Country) AS result
  FROM provider 
  GROUP BY GroupUpdateID
) c ON c.GroupUpdateID = p.GroupUpdateID
WHERE p.Country = 'Vietnam'
AND p.ProviderName = 'Provider A';

Or use a correlated subquery in the SELECT clause:

SELECT
  ProviderName, 
  Country, 
  GroupUpdateID,
  (
    SELECT GROUP_CONCAT(DISTINCT c.Country)
    FROM provider c
    WHERE c.GroupUpdateID = p.GroupUpdateID
  ) AS result
FROM provider p
WHERE Country = 'Vietnam'
AND ProviderName = 'Provider A';

edited Feb 23, 2017 at 7:06

answered Feb 23, 2017 at 6:58

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tan Stanley Over a year ago

Great @Thorsten Kettner, you seriously have been of great help. I tweak a little, however adopted your correlated subquery method! Thank you!

Collectives™ on Stack Overflow

Combine 2 Select queries with different conditions

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related