Mysql – SQL Get All Data With The Given IDs And With The Same Data On Single Column

mariadbMySQLquery

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).

Best Answer

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

Related Question