Help, I have query that return this

id user_id host_id data is_accepted
1     2       1    lorem     true
2     3       1    ipsum     true
3     4       1    dolor     false
7     1       1    amet      false

My question how can i return only id 1,2,7 ( the point is i want return all data related to the host but the is_accepted = 0 is for only specific user)

CodePudding user response：

You can use WHERE to filter out data (1,2,7) and you can use AND to join them together

CodePudding user response：

You can use where in to do this. Fiddle: https://www.db-fiddle.com/f/6aSWwcyVb5usq2BW4Urdc3/0

CREATE TABLE test (
  id INT,
  user_id int,
  host_id int,
  data varchar(50),
  is_accepted int
);
INSERT INTO test (id, user_id, host_id, data, is_accepted) 
VALUES 
(1,2,1,'lorem', 1),
(2,3,1,'ipsum', 1),
(3,4,1,'dolor', 0),
(7,1,1,'amet', 0);

SELECT *
FROM test
WHERE test.id in (1,2,7)

Result:

Then, obviously you can add other things such as "And( (user_id = 2 and is_accepted = true) or (user_id != 2)) to accept ALL users regardless of is_accepted, except for user_id 2 which has to be is_accepted as true.

Example of this in this fiddle: https://www.db-fiddle.com/f/6aSWwcyVb5usq2BW4Urdc3/2

CREATE TABLE test (
  id INT,
  user_id int,
  host_id int,
  data varchar(50),
  is_accepted int
);
INSERT INTO test (id, user_id, host_id, data, is_accepted) 
VALUES 
(1,2,1,'lorem', 1),
(2,3,1,'ipsum', 1),
(3,4,1,'dolor', 0),
(7,1,1,'amet', 0);

SELECT *
FROM test
WHERE test.id in (1,2,7)
And( (test.user_id = 1 and test.is_accepted = 1) or (test.user_id != 1))

Result: