0

So, basically, I have this table

+-------------------
|id  | col1 | name |
|----|------|------|
| 1  |  a   |  la  |
| 2  |  b   |  ria |
| 3  |  w   |  la  |
| 4  |  q   |  la  |
| 5  |  y   |  ria |
|____|______|______|

And, what I want to do is, I want to get all the data based on id 1, 3 and 5 but with the same name. So, since id 5 is different, and id 1 and 3 is the same, I would like to exclude the id 5. I tried with ...HAVING COUNT(name) >=1 but it only shown 1 data. Is it possible? If so, how to achieve this? Thank you

EDIT: So, what I mean is, I would like to get the result with the same name. Like:

+-------------------
|id  | col1 | name |
|----|------|------|
| 1  |  a   |  la  |
| 3  |  w   |  la  |
|____|______|______|

But, if, for instance in the future, there are case like

"select all data from table where id [1,2,3,5] with the same data in name column"

this will cause a conflict (i guess). the point is, I would like to get the data with the same name where id is given (because I have php app, and the input from the user is only id, and user don't want the different name appear in the result).

0

It is not absolutely clear what you expect as la should yield te result f 1,3,4 see query below

CREATE TABLE table1
    (`id` int, `col1` varchar(6), `name` varchar(6))
;
    
INSERT INTO table1
    (`id`, `col1`, `name`)
VALUES

    ('1', 'a', 'la'),
    ('2', 'b', 'ria'),
    ('3', 'w', 'la'),
    ('4', 'q', 'la'),
    ('5', 'y', 'ria');
SELECT id 
FROM table1 
WHERE `name` IN (SELECT `name` FROM table1  GROUP BY `name` HAVING COUNT(`name`) > 2)
AND id IN (1,2,3,5)
| id |
| -: |
|  1 |
|  3 |
SELECT `name` FROM table1  GROUP BY `name` HAVING COUNT(`name`) > 2
| name |
| :--- |
| la   |

db<>fiddle here

2
  • I already tried with this query, but it only display one data. I updated the post too. Thanks!
    – Fahmi
    Feb 7 at 8:52
  • @Fahmi you should eplained that a bit earlier, i updated the query to fit yur wishes
    – nbk
    Feb 7 at 10:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.