Update query to use joined table

Question

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 don't know how to get into the query.

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

--
-- Table structure for table `card`
--

CREATE TABLE IF NOT EXISTS `card` (
  `id` int(11) NOT NULL auto_increment,
  `startdate` date NOT NULL,
  `enddate` date NOT NULL,
  `state_id` int(11) NOT NULL,
  `referred_as` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;


--
-- Table structure for table `card_driver`
--

CREATE TABLE IF NOT EXISTS `card_driver` (
  `card_id` int(11) NOT NULL,
  `driver_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


--
-- Table structure for table `driver`
--

CREATE TABLE IF NOT EXISTS `driver` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(40) default NULL,
  `supplier_id` int(11) NOT NULL,
  `referred_as` varchar(40) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

Some images :

Answer 1

Your card_driver table seems to implement a many-to-many relationship between rows in your card table and your driver table. Tables like this are usually called join tables.

Join tables like this show the relationship between two so-called detail tables, in this case card and driver.

I'm sorry, I don't understand what you mean when you describe your referred_as columns in your two detail tables.

I think you're asking for all the card items that are related to a particular driver. You can get that sort of result like this:

SELECT card.id, card. startdate, card.enddate, card.state_id, card.referred_as,
       driver.id AS driverid, driver.title, driver.supplier_id, 
       driver.referred_as
  FROM driver
  LEFT JOIN card_driver ON card.id = card_driver.driver_id
  LEFT JOIN card        ON card_driver.card_id = card.id
 WHERE driver.id = '$id'

This will give you one row per card assigned to that driver via the many-to-many relationship in the card_driver table. Each row will contain information about the driver and the card.

As you can see it works by joining the join table, and then joining the other detail table.

Pro tip: Don't use SELECT * in production software; you are much better off choosing the columns you need returned from the query explicitly.