1

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.

Tan Stanley
  • 101
  • 2
  • 13
  • 1
    This has been answered here: http://stackoverflow.com/questions/542705/how-do-i-combine-2-select-statements-into-one – David Biga Feb 23 '17 at 06:50

1 Answers1

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';
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Great @Thorsten Kettner, you seriously have been of great help. I tweak a little, however adopted your correlated subquery method! Thank you! – Tan Stanley Feb 23 '17 at 07:19