Simple Query MySQL [closed]

Question

Simple Query MySQL [closed]

Navigation

#1 by (4 votes)

0

Have the query:

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 OR in WHERE , it works perfectly, but if I change the OR by AND there is no record.

What I need in question is the following:

Bringing the clients that are in the categoria_cliente table, in this table, has the column categoria_cliente_cliente_id and categoria_cliente_categoria_id , with OR I'm bringing clients that are in category 1 or 2, but with AND I want to bring only customers who are in category 1 and 2.

mysql

asked by anonymous 26.01.2017 / 17:09

1 answer

Start application through windows service [closed] How to display an image hosted in my App?

score 4 · Accepted Answer

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

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 1 OU 2 cache, you will find:

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 1 E 2 cache, you will find:

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)