Questions

Query on multiple tables

Tags: Web Development
+
0 Votes

Query on multiple tables

rrezartamuja
Hi, I need to create a single query that reads from three tables in the same db.

Tables:

1. cm_players
player_id
card_no
club_id
surname
name
birthdate

2. cm_clubs
club_id
prefix
name
address
place
stadium_id
telephone
fax
email
web
contact_person
president
secretary
press_officer
status
image

3. cm_registrations
player_id
surname
name
birthdate
birthplace
club_id
date_from
date_to
amateur
status

So, I need to display

cm_players.name,
cm_players.surname,
cm_clubs.name,
cm_players.birthdate,
cm_players.card_no,

join cm_players with cm_clubs to get the name of the club, and check in cm_registrations if the user is active.....

i have tried something like:

SELECT cm_players.name, cm_players.surname, cm_clubs.name, cm_players.birthdate, cm_players.card_no, cm_registrations.amateur FROM cm_players INNER JOIN cm_clubs on cm_players.club_id = cm_clubs.club_id, cm_registrations WHERE cm_players.name LIKE '%$name%' AND cm_players.surname LIKE '%$surname%' AND cm_players.player_id = cm_registrations.player_id AND cm_registrations.amateur = 0 ORDER BY cm_players.player_id ASC

but no luck...

can anyone help me to build this query?

Member Answers