Removing doubled record in group by 2 columns

I have a table like the below

code country1 country2
(1) Japan China
(2) France China
(3) Germany France
(4) Germany Japan
(5) China Japan
(6) France Germany
(7) Japan France[/code]I like to produce my target result below.

When keyCountry is Japan
I like to produce the below.

code China
(4) Germany
(7) France[/code]

Tthe (5) China is not produced because (1) China is already produced.

The following code is one of trials for it.

SELECT id, country1, country2 FROM myTable WHERE country1='Japan' or country2='Japan' GROUP BY country1,country2
The code above produces the result below.

(1) Japan/China (4) Germany/Japan (5) China/Japan (7) Japan/France How can I remove record (5) because it is doubled with record (1)?

SELECT 'Japan' , country2 FROM myTable WHERE country1 = 'Japan' UNION ALL SELECT 'Japan' , country1 FROM myTable WHERE country2 = 'Japan'

At first you need to get a list of countries where either country1 or country2 is equal to Japan

select id,
       case when country1 = 'Japan' then country2 else country1 end
  from t
 where 'Japan' in (country1,country2)

which gives

     id
     ==
      1 China
      4 Germany
      5 China
      7 France

From this list you should get only distinct values with the lowest id

select min(id) as id,
       country
  from (select id,
               case when country1 = 'Japan' then country2 else country1 end
          from t
         where 'Japan' in (country1,country2)) dt(id,country)
 group
    by country
 order
    by id

[code]
Thank you , swampBoogie.
I try to apply the code below by your help.
$list=mysql_query(“select min(id) as id,
country
from (select id,
case when country1 = ‘Japan’ then country2 else country1 end
from myTable
where ‘Japan’ in (country1,country2)) dt(id,country)
group
by country
order
by id”);

while($rows=mysql_fetch_array($list)) { $id=$rows[‘id’];
echo $id.‘
’;
}
[/code]The code above produces "
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource"

How can I fix it?
What does “dt” mean in dt(id, country)?

Thank you r937,
I try the code below.

SELECT 'Japan' , country2, id FROM myTable WHERE country1='Japan' UNION ALL select 'Japan' , country1, id
The code above produces the result below.

code China
(7) France
(4) Germany
(5) China[/code]The result above has still (5) China.
Since (5) China is doubled with (1) China, I like to remove it.
How can I remove (5) China?

do not include the id

[code]$list=mysql_query(“SELECT ‘Japan’
, country2
FROM myTable
WHERE country1=‘Japan’
UNION ALL
SELECT ‘Japan’
, country1
FROM myTable
WHERE country2=‘Japan’”);

while($rows=mysql_fetch_array($list)) { $country1=$rows[‘country1’]; $country2=$rows[‘country2’];
echo $country1.‘/’.$country2.‘
’;
}[/code]The code above produces the result below.

/China /France /Germany /China It still produces “China” 2 times.

my target result is like the following.

/China
/France
/Germany

oh i am so silly

please use UNION instead of UNION ALL

sorry :slight_smile: :slight_smile: :slight_smile:

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.