CODEWITHSUNDEEP
mysql
sark9012
sark9012

Reputation: 5747

Select data based on another table

I have three tables, I'll just list the important columns

db_players

id | name

players

id | teamid | careerid

db_teams

The db_teams id links to the players teamid.

I need to run a query where I select rows from db_players as long as db_teams.id isn't in a row in players as teamid where the careerid = 1.

I've never attempted this type of query with mysql before, I know I could do two queries and involve php but I'm intrigued as to whether it's possible with a pure db query.

Thanks.

EDIT - simpler now.

SELECT dp.first_name
FROM tbl_foot_career_db_players dp
INNER JOIN tbl_foot_career_players p
ON p.playerid != dp.id
WHERE p.careerid = 1

The idea is that I want to return all rows from tbl_foot_career_db_players WHERE the id from that table isn't present in a row in tbl_foot_career_players in the column playerid. And the tbl_foot_career_players.careerid must also equal 1.

Upvotes: 0

Views: 96

Answers (2)

Arjan
Arjan

Reputation: 9884

List all db_players that are not in players with career = 1

SELECT d.*
FROM db_players d
LEFT JOIN players p
  ON p.player_id = d.id
  AND p.career = 1
WHERE p.id IS NULL

Upvotes: 1

GautamD31
GautamD31

Reputation: 28763

Try to JOIN them with db_players id is not in players teamid.

SELECT db_players.* FROM db_players
LEFT JOIN players ON players.id != db_players.id 
LEFT JOIN db_teams ON players.teamid = db_teams.id
WHERE careerid = 1

Upvotes: 1

Related Questions