How to use GROUP_CONCAT in the WHERE clause, with numbers?

Asked

Viewed 206 times

2

Good morning.

I have the following tables in a Mysql database:

BOIS
------------
| id | boi |
------------
| 12 | 77  |
| 22 |  8  |
| 14 | 12  |
------------

BOIS_MANEJO
---------------------
| id | fk_boi| peso |
---------------------
|  1 |   14  |  217 |
---------------------
|  2 |   22  |  217 |
---------------------
|  3 |   14  |  250 |
---------------------

The following Query brings me all ids of oxen.

    SELECT *
FROM bois
WHERE 
    id NOT IN(
    SELECT CONCAT("'", GROUP_CONCAT(DISTINCT(m.fk_boi) SEPARATOR "','"), "'") FROM bois_manejo AS m
    )

I get ID 12 only with the following formulas.

    SELECT id 
FROM bois
WHERE 
    id NOT IN(14, 22)
# OU id NOT IN('14', '22')

How I can use GROUP_CONCAT with numbers, or how I can resolve this situation?

Thanks in advance.

mysql

  • What is the expected result?

  • @Constant, the expected result is ID 12. It is the only one not in the table bois_manejo.

  • I don’t understand why you try another way, since you already have the select that brings you the expected result..

  • @Constant, I don’t have the SELECT that brings the 12. If you’re talking about the SELECT that worked (the second one) it works just because I know the codes that are there. The goal of the query is to bring me the results as there is change in the table’s base/feed. The first select brings me all the Ids.

  • @E.Rabbit which entrance and exit you expect?

  • So, from what I understand, you need to make a select that brings a result based on a change from another table, in this case (bois_manejo), is that it? Can you be a little clearer in your doubt? :)

  • @Sorack, as you can see, I’m trying to get the WHERE clause to take all the Foreng Keys that exist in the table bois_manejo (with a DISTINCT) and give me all the Ids that are not in bois_manejo. In the example the only ID that is not in table bois_manejo is 12.

  • @Filipel.Constant fk_boi is a Foreng Key related to the ID of the table ox. I need to bring only the Ids of oxen that have not been weighed/handled yet. :)

  • All right, I understood the structure of the table I didn’t understand how I know they were or weren’t weighed/handled yet! hehe :)

  • @E.Rabbit do you have the need to use group_concat?? is there a problem using other alternatives??

  • Good morning, @Oliveira. I’m open to alternatives. So far I’m stuck in this. :)

Show 6 more comments

2 answers

2


The subquery returns the records that have relationship between the table bois and bois_manejo by id and fk_bois respectively by INNER JOIN. In this scenario the records that are in oxen but are not in ox_handling will not be in the query result and can compare with NOT IN, so id 12 will be presented.

SELECT * FROM bois WHERE id NOT IN(SELECT bois.id FROM bois INNER JOIN bois_manejo ON bois.id = bois_manejo.fk_boi)

  • Thank you very much, @Oliveira. I didn’t know that was possible. Does the IN clause read the values of a kind of returned array, or does it read only the numbers? Can I use this option when only dealing with numbers? Another thing: this model is more efficient when it comes to processing?

  • @E.Rabbit, in this case is compared only by the id column of the table oxen, the IN allows checking if the value of a column is present in a list of elements, but vc can compare with other columns, in this case the alternative Exists https operator://en.stackoverflow.com/questions/62925/not-in-or-not-exists-qual-usar/63279

  • Ball show, @Oliveira! Thank you so much for sharing your knowledge and helping us. All the best! (Ah... watch out for Obiwan - you said you’re just a padawan - because the guy lies quiet! hehehe)

0

You can do it in a simpler, more efficient way using the NOT EXISTS clause:

SELECT * FROM bois
WHERE NOT EXISTS (SELECT * FROM bois_manejo WHERE bois_manejo.fk_boi = bois.id);

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.