Update query to use joined table

[oxhey]

I have this query :

    $sql ="SELECT * FROM card c JOIN driver d ON c.referred_as=d.referred_as WHERE d.ID='$id'";

It needs to be updated to include the 3rd table which is a joined table containing the driver and card id's from their respective tables.

Table 1 is called "card" . The fields that are important are:



"state_id" - This table has 3 values (1,2,3)

"associated_driver" - called "referred_as" on driver table // Not actually part of the table . Created by the 3rd table

"referred_as" - called "associated_card" on driver table

Table 2 is called "driver". The fields that are important are:



"ID" - The auto incremented value of the table

"associated_card" - Has a value , normally some number e.g 123555 // Not actually part of the table . Created by the 3rd table

"referred_as" - The name of the driver () called "associated_driver" on card table

Forgot to add this table :



Table 3 is called "card_driver". The fields that are important are:



"driver_id" - The id from the driver table that links to the card

"card_id" - The id from the card table that links to the driver

What i want to happen :

 

When a user enters their id from the driver table, it will compare a field that both tables have i.e the 'associated card' field (called referred_as on the card table). 

 

The associated card is from the joined table which i dont know how to get into the query.

 

Any help is welcomed. If you need me to explain it more , i will.

Edited February 28, 2014 by Zane

[Psycho]

I was going to try and help and started analyzing the information you have provided, but the information is all over the place and impossible to follow.
 
For example,
 
You have the following in your information
 
 

"driver_id" - The id from the driver table that links to the card
"card_id" - The id from the card table that links to the driver

Yet you show no such fields existing in those tables.
 
In describing the fields for table 'driver' you have the following statement
 

"associated_card" - Has a value , normally some number e.g 123555 // Not actually part of the table . Created by the 3rd table

I'm confused. Is that a field in the table or not.
 
Take a step back and look at what you have as someone who has not been working on the problem and figure out what information you would need to understand it. It seems to me that your "explanation" probably makes perfect sense to you, but you're not looking at it as someone with no prior knowledge of what you are doing (i.e. US).

Edited February 28, 2014 by Psycho

[Barand]

Psycho,

 

I was equally confused by his first topic on this problem.