Select ID by two different conditions on two column on a table-CodePudding

I have a table that looks like this:

user_id	method	car_brand
123456	car	honda
345678	walk
567890	walk
567890	car	toyota

I want to get the user_id, where the method is walk and the car_brand is not toyota.

The expected output is:

user_id	method	car_brand
345678	walk

Since the user 567890 has toyota in the car_brand.

What query do I know to get this result?

Thanks in advance!

CodePudding user response：

you could use a query like below

select t1.* 
from yourtable t1 where
method='walk'
and not exists 
(
select 1 from yourtable t2
where t2.user_id=t1.user_id and t2.car_brand ='toyota')

CodePudding user response：

This is certainly not the most efficient method, but it shows how you may 'pivot' the table to be in a form that's easier to query for the type of question you're asking:

Schema (MySQL v5.7)

CREATE TABLE t (
  `user_id` INTEGER,
  `method` VARCHAR(4),
  `car_brand` VARCHAR(6)
);

INSERT INTO t
  (`user_id`, `method`, `car_brand`)
VALUES
  ('123456', 'car', 'honda'),
  ('345678', 'walk', NULL),
  ('567890', 'walk', NULL),
  ('567890', 'car', 'toyota');

Pivot Query

SELECT user_id
     , MAX(CASE WHEN method = 'car' THEN 'car' END) car
     , MAX(CASE WHEN method = 'walk' THEN 'walk' END) walk
     , MAX(car_brand) car_brand
FROM t
GROUP BY user_id;

user_id	car	walk	car_brand
123456	car		honda
345678		walk
567890	car	walk	toyota

Desired Output

SELECT user_id
     , walk method
     , car_brand
FROM (
  SELECT user_id
       , MAX(CASE WHEN method = 'car' THEN 'car' END) car
       , MAX(CASE WHEN method = 'walk' THEN 'walk' END) walk
       , MAX(car_brand) car_brand
  FROM t
  GROUP BY user_id
) t2
WHERE COALESCE(car_brand, '') <> 'toyota'
  AND walk IS NOT NULL;

user_id	method	car_brand
345678	walk

View on DB Fiddle