Simple query Mysql

Asked

Viewed 60 times

0

Have the consultation:

SELECT * 
  FROM cliente,
       categoria_cliente,
       categoria
 WHERE categoria_cliente_cliente_id = cliente_id 
   and categoria_id = categoria_cliente_categoria_id
   and (categoria_cliente_categoria_id = 1 OR categoria_cliente_categoria_id = 2)
 GROUP BY cliente_id

If I do it with the OR in the WHERE, works perfectly, but if I change the OR for AND no record comes.

What I need in question is this:

Bring customers who are on the table categoria_cliente, in this table, has the column categoria_cliente_cliente_id and categoria_cliente_categoria_id, with the OR I am bringing customers who are in category 1 or 2, but with the AND i want to bring only customers who are with category 1 and 2.

mysql
  • 4

    Good thing, because if the categoria_cliente_categoria_id for 1 and for 2 at the same time (AND) we have a problem in the universe.

  • Now, if you can explain a little bit better about the type of field, if it’s numerical, if it has text, etc., we can try to help in a more objective way, of course. For example, if category is text, separated with commas, it can no longer be with =. But for this, it would be good if you [Edit] the question giving more details. Mysql has FIND_IN_SET if it is for multiple values. Example: FIND_IN_SET( 1, categoria_cliente_categoria_id) AND FIND_IN_SET( 2, categoria_cliente_categoria_id)

  • I assume that 1 customer may per more than one category, that is, you have entries in categoria_cliente with the same categoria_cliente_cliente_id, however different categoria_cliente_categoria_id correct? In that case you would like the query to return only customers associated with both categories, that’s it?

1 answer

4


With the following query, you are returning all clients already related to all categories:

SELECT cli.*, cat.*
  FROM cliente cli
  JOIN categoria_cliente cc
    ON cc.categoria_cliente_cliente_id = cli.cliente_id
  JOIN categoria cat
    ON cat.categoria_id = cc.categoria_cliente_categoria_id
 ORDER BY cli.cliente_id

If you want customers who are in the shitters 1 OR 2, gets:

SELECT cli.*, cat.*
  FROM cliente cli
  JOIN categoria_cliente cc
    ON cc.categoria_cliente_cliente_id = cli.cliente_id
  JOIN categoria cat
    ON cat.categoria_id = cc.categoria_cliente_categoria_id
   AND cat.categoria_id IN (1, 2)
 ORDER BY cli.cliente_id

If you want customers who are in the shitters 1 And 2, gets:

SELECT cli.*, cat.*
  FROM cliente cli
  JOIN categoria_cliente cc
    ON cc.categoria_cliente_cliente_id = cli.cliente_id
  JOIN categoria cat
    ON cat.categoria_id = cc.categoria_cliente_categoria_id
   AND cat.categoria_id IN (1, 2)
 WHERE EXISTS(SELECT * 
                FROM categoria_cliente cc1 
               WHERE cc1.categoria_cliente_cliente_id = cli.cliente_id
                 AND cc1.categoria_cliente_categoria_id = 1)
   AND EXISTS(SELECT * 
                FROM categoria_cliente cc2
               WHERE cc2.categoria_cliente_cliente_id = cli.cliente_id
                 AND cc2.categoria_cliente_categoria_id = 2)
  • 1

    +1. That reply from Soen lists another 12 options on how to do this type of query - some unfortunately only work in Postgre. Yours would fit model number 5. I usually do this with model number 6, that is, multiple joins to categoria_cliente with aliases filtered by categoria_cliente_categoria_id .

Browser other questions tagged mysql

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