CODEWITHSUNDEEP
mysql
Iswariya Suthakar
Iswariya Suthakar

Reputation: 41

Query to fetch multiple fields in same column in mysql

I am using mysql db.

     id | comment       
--------+--------------- 
   1121 |    Accounting
   1121 |    Shipping  
   1121 |    Receiving
   1121 |    Testing
   1122 |    Accounting
   1122 |    Receiving

I want to write such a query so that o/p will be like this-:

     id | comment       
--------+--------------- 
   1121 |    Accounting
   1121 |    Shipping  
   1121 |    Receiving
   1121 |    Testing

So I want Accounting, Shiping, Receiving and testing comments.

Can anybody plz guide me??

Upvotes: 0

Views: 158

Answers (2)

eggyal
eggyal

Reputation: 125835

  • If you only want the id of records that contain all four comments, you can group by id and filter such groups for those that contain the requisite number of records:

    SELECT   id
FROM     my_table
WHERE    comment IN ('Accounting', 'Shipping', 'Receiving', 'Testing')
GROUP BY id
HAVING   COUNT(*) = 4

    See it on sqlfiddle.

  • If you want the complete records for such (id, comment) pairs, you can join the result with your original table:

    SELECT * FROM my_table NATURAL JOIN (
  SELECT   id
  FROM     my_table
  WHERE    comment IN ('Accounting', 'Shipping', 'Receiving', 'Testing')
  GROUP BY id
  HAVING   COUNT(*) = 4
) t
WHERE comment IN ('Accounting', 'Shipping', 'Receiving', 'Testing')

    See it on sqlfiddle.

Note that if your data model does not guarantee uniqueness of (id, comment) pairs, you will need to replace COUNT(*) with (the less performant) COUNT(DISTINCT comment) in the above queries.

Upvotes: 1

Mentallurg
Mentallurg

Reputation: 44

Do you want to select all IDs, that have all 4 comments? In your example 1121 has 4 different comments (it's like a "state", right?), and 1122 has only 2 states (comments).

select T1.id from mytable T1, mytable T2, mytable T3, mytable T3 where T2.id=T1.id and T3.id=T1.id and T4.id=T1.id and T1.comment='Accounting' and T2.comment='Shipping' and T3.comment='Receiving' and T4.id='Shipping'

Upvotes: 0

Related Questions