Query to fetch multiple fields in same column in mysql

Question

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??

Which id do you want for comments that appear multiple times? — eggyal
– eggyal, Commented Sep 30, 2012 at 15:51
for id 1121 i need the comments.based on these comments i have to update another table — Iswariya Suthakar
– Iswariya Suthakar, Commented Sep 30, 2012 at 16:03
@Iswariya Suthakar: Why did you decide for "Accounting" to select 1121 and not 1122? May be select 1122 instead of 1121? What is criteria? — Mentallurg
– Mentallurg, Commented Sep 30, 2012 at 16:07
I need to close the proccess for particular id if its having all comments which i said — Iswariya Suthakar
– Iswariya Suthakar, Commented Sep 30, 2012 at 16:16
Then why not simply specify a filter criterion using a WHERE clause: e.g. SELECT * FROM my_table WHERE id = 1121? — eggyal
– eggyal, Commented Sep 30, 2012 at 16:21

eggyal · Accepted Answer · 2012-10-02 10:48:34Z

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.

Mentallurg · Accepted Answer · 2012-09-30 16:22:38Z

0

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'

answered Sep 30, 2012 at 16:22

Mentallurg

442 bronze badges

3 Comments

Mentallurg Over a year ago

"no. i want for that particular id" - then it's trivial: SELECT * FROM mytable WHERE id=1121

Iswariya Suthakar Over a year ago

if i use this sometimes for that id it have only accounting and shipping or accounting, shipping like that but i want all the four comments. if all four comments are there i need to update status as closed in another table

Mentallurg Over a year ago

Then my SQL above solves this problem. It finds only the IDs that have ALL 4 comments. Just replace "mytable" with your table name: 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'

Collectives™ on Stack Overflow

Query to fetch multiple fields in same column in mysql

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related