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.
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)?
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)?
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?
[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.