How to get values in both queries?-CodePudding

I have a mysql database and i'm trying to get the results in both queries how do i do it?

SELECT distinct cliente.nome_cliente,marcas.nome 
FROM cliente 
inner join cliente_marcas on cliente.id = cliente_marcas.id_cliente 
inner join marcas on marcas.id = cliente_marcas.id_marca 
WHERE `status` = '1' and id_marca = 3 
union
SELECT distinct cliente.nome_cliente,marcas.nome 
FROM cliente 
inner join cliente_marcas on cliente.id = cliente_marcas.id_cliente 
inner join marcas on marcas.id = cliente_marcas.id_marca 
WHERE `status` = '1' and id_marca = 4;

I've tried where id_marca = 3 and id_marca = 4 but it doesnt work. This is what i'm getting but i only want the names that are in both queries. Thank you.

CodePudding user response：

You can join those two subqueries and match them by name. For example:

select
  a.*, b.*
from (
  -- query 1 here
) a
join (
  -- query 2 here
) b on a.nome_cliente = b.nome_cliente

If you need the full expanded query, it should take the form:

select
  a.*, b.*
from (
  SELECT distinct cliente.nome_cliente,marcas.nome 
  FROM cliente 
  inner join cliente_marcas on cliente.id = cliente_marcas.id_cliente 
  inner join marcas on marcas.id = cliente_marcas.id_marca 
  WHERE `status` = '1' and id_marca = 3 
) a
join (
  SELECT distinct cliente.nome_cliente,marcas.nome 
  FROM cliente 
  inner join cliente_marcas on cliente.id = cliente_marcas.id_cliente 
  inner join marcas on marcas.id = cliente_marcas.id_marca 
  WHERE `status` = '1' and id_marca = 4
) b on a.nome_cliente = b.nome_cliente