MySQL Join Query

Question

I need to create a join query for the following:

Table supplier:

• id
• name

Table supplier_vehicles

• id
• supplier_id
• vehicle_id

A supplier can have multiple vehicles.

On my front-end form I have a checkbox list - a user can select multiple vehicles. The back end script needs to do a search and bring back all suppliers that contain any of the specified vehicle id's.

The checkbox list name is vehicle_type[] and will end up in the $_POST array as (for example):

Array
(
    [0] => 1
    [1] => 4
    [2] => 6
)

At the moment I could do with with some SQL examples so that I can test the query using different values.

Answer 1

If you know the vehicle ID's, you can use an IN clause:

SELECT *
  FROM supplier s
     , supplier_vehicles v
 WHERE s.id = v.supplier_id
   AND v.vehicle_id IN (1, 4, 6)

If you just want the distinct supplier ID's, you could use DISTINCT.

SELECT DISTINCT s.supplier_id
  FROM supplier s
     , supplier_vehicles v
 WHERE s.id = v.supplier_id
   AND v.vehicle_id IN (1, 4, 6)

Answer 2

First create a string that holds all of the elements in your array separated by commas. Not sure what language you are doing this in but you can either use a JOIN function or loop through the array.

According to your example the string would be equal to 1,4,6

The for your SQL query :

"SELECT s.name
FROM supplier AS s
JOIN supplier_vehicles AS v
ON s.id = v.supplier_id
WHERE v.vehicle_id IN (" + stringName + ")"