'checking different data value in a same column mysql query?

id_detail_item id_item   id_detail_item_name
1              1         abc 
2              1         abcd
3              1         cde
4              3         zki
5              3         zkr

How to check if there are two different data in "id_detail_item_name" in the same "id_item"?

I've tried the following, but got an error: 

SELECT id_item FROM table_detail_item WHERE id_detail_item_name='abc' AND id_detail_item_name='abcd';
mysql


Solution 1:[1]

This should list all entries with at least two different id_detail_item_name for id_item

SELECT id_item, MIN(id_detail_item_name) nam1,max(id_detail_item_name) nam2 
FROM table_detail_item 
GROUP by id_item HAVING MIN(id_detail_item_name) nam1<MAX(id_detail_item_name)

Solution 2:[2]

That's because the AND operator means both conditions must be met.

Try:

SELECT * FROM table_detail_item 
WHERE id_item = '1' 
AND id_detail_item_name IN ('abc', 'abcd');

Solution 3:[3]

try

select id_item
  from (
              select min(id_detail_item_name) mmin
                   , max(id_detail_item_name) mmax
                from table_detail_item
            group by id_item
       ) agg
 where agg.mmin <> agg.mmax
     ;

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Carsten Massmann
Solution 2
Solution 3 collapsar

Related Questions