CODEWITHSUNDEEP
mysql
Nashie
Nashie

Reputation: 331

Checking different row while in a statement

i am trying to check if the player_id is in anywhere in "user_lineup" paired by team_id

SELECT player_id FROM user_players WHERE team_id = 134 AND NOT EXISTS (SELECT * FROM user_lineup)

SELECT player_id FROM user_players WHERE team_id = 134 AND player_id NOT IN user_lineup

SELECT player_id FROM user_players WHERE team_id = 134 NOT EXISTS (SELECT * FROM user_lineup WHERE team_id = 1)

i know i am missing something, but not sure what to do i have tried the above methods and it returns 0 rows, i am trying to see if player_id is in user_lineup where team_id are the same.

i have read about JOIN but is this needed if i am just checking if a row has a value the same as player_id.

user_players
id | player_id | player_name | team_id
1  | 343       |  example    |    1

user_lineup - (pos1 & pos2 are player_id)
id    | pos2   | pos1     | team_id
234   | 36367  | 234347   | 1

i need to see if player_id is is anywhere in user_lineup where the team_id is the same.

Please change the title of this question to something more relevant as i am not sure what i am asking.

thank you in advance

Upvotes: 1

Views: 41

Answers (1)

nbk
nbk

Reputation: 49375

If user_id is related to pos1 or pos2 you can use following query

CREATE TABLE user_players (
  `id` INTEGER,
  `player_id` INTEGER,
  `player_name` VARCHAR(7),
  `team_id` INTEGER
);

INSERT INTO user_players
  (`id`, `player_id`, `player_name`, `team_id`)
VALUES
  ('1', '343', 'example', '1');

CREATE TABLE user_lineup (
  `id` INTEGER,
  `pos2` INTEGER,
  `pos1` INTEGER,
  `team_id` INTEGER
);

INSERT INTO user_lineup
  (`id`, `pos2`, `pos1`, `team_id`)
VALUES
  ('234', '36367', '234347', '1'),
  ('235', '343', '234348', '1');

SELECT 
    up.*, ul.pos1, ul.pos2
FROM
    user_players up
        INNER JOIN
    user_lineup ul ON ul.`team_id` = up.`team_id`
        AND ( up.`player_id` = ul.`pos1` OR  up.`player_id` = ul.`pos2`)

id | player_id | player_name | team_id |   pos1 | pos2
-: | --------: | :---------- | ------: | -----: | ---:
 1 |       343 | example     |       1 | 234348 |  343

db<>fiddle here

Upvotes: 1

Related Questions