I have the MySQL table called table.
| ID |item_code| item_name |
| 1 | 12345 | abc |
| 2 | 12345 | xyz |
| 3 | 11221 | abc |
| 4 | 19261 | www |
| 5 | 12345 | abc |
| 6 | 62898 | abc |
| 7 | 92648 | xxx |
| 8 | 45678 | xxx |
| 9 | 1234 | pqrs |
| 10 | 2345 | defg |
| 11 | 1234 | pqrs |
I want the query which will results the below table:
> |item_code | item_name |
> | 12345 | abc |
> | 12345 | xyz |
> | 11221 | abc |
> | 62898 | abc |
> | 92648 | xxx |
> | 45678 | xxx |
I want the result considering of:
item_code mapped to multiple item_name. Here item_code 12345 is mapped with abc and xyz. item_code 1234 is mapped to pqrs.
item_name mapped to multiple item_code. Here item_name abc is mapped with three items: 12345,11221,62898. item_name xxx is mapped to 92648 and 45678.
Here is the SQL statement I tried:
(select a.item_code,a.item_name from table a
join ( select item_code
from table
group by item_code
having count(*) > 1 ) b
on a.item_code = b.item_code)
union
(select a.item_code,a.item_name from table a
join ( select Item_name
from table
group by Item_name
having count(*) > 1 ) b
on a.Item_name = b.Item_name);
But this SQL query does not generate the bi directional mapped keys. Can someone help me generate these bi-directional mappings?
SELECT DISTINCT t.item_code, t.item_name
FROM mytable t
INNER JOIN
(SELECT item_code, COUNT(DISTINCT item_name) num FROM mytable GROUP BY item_code) code
ON (t.item_code = code.item_code)
INNER JOIN
(SELECT item_name, COUNT(DISTINCT item_code) num FROM mytable GROUP BY item_name) name
ON (t.item_name = name.item_name)
WHERE code.num > 1 OR name.num > 1
The subqueries used in the two JOIN clauses are used to attach the counts for which each item code or name is repeated. The joined result set is filtered using the WHERE clause at the outermost level to include only those DISTINCT combinations where the code and/or the name was associated with multiple values for the opposite field.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments