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

Question

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

Answer 1

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

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

Answer 2

You can define a variable for the id values and query to get the name as per your query criteria. For example, using mysql client:

SET @ids = '1,3,5'; -- or SET @ids = '1,2,3,5';
SELECT DISTINCT(name)
FROM t1
WHERE
name IN (
 SELECT name 
 FROM t1 
 WHERE FIND_IN_SET(TRIM(CAST(id AS CHAR(5))), @ids) 
 GROUP BY name
 HAVING COUNT(name) > 1
)
AND FIND_IN_SET(TRIM(CAST(id AS CHAR(5))), @ids);

The result depending upon the @ids input variable.

+------+
| name |
+------+
| la |
+------+
+------+
| name |
+------+
| la |
| ria |
+------+