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

2 Answers 2

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
    Commented Feb 7, 2021 at 8:52
  • @Fahmi you should eplained that a bit earlier, i updated the query to fit yur wishes
    – nbk
    Commented Feb 7, 2021 at 10:39
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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