CODEWITHSUNDEEP
mysqlsql
http203
http203

Reputation: 851

Most efficient way to select multiple rows from multiple tables with a list of IDs using MySQL

Consider the following example:

http://sqlfiddle.com/#!3/b89b8/2

There are 3 tables: Car, Types, CarTypeMappings

I have a list of Car IDs (3,7) and a list of CarTypeMapping IDs (2,6,3,4) and I would like to retrieve a single table containing the friendly names for the cars and their types.

Here is my desired result:

CarName    CarType
-------    --------
VW         Jetta
VW         Golf     
Nissan     Altima
Nissan     Sentra

As you can see in my example, I have multiple queries to get one row at a time, but I'd like to do this in one shot.

I can create a stored procedure if necessary but I don't want to poll the database multiple times. What's the most efficient way to achieve this? Is it possible to use the WHERE id IN (..) syntax? I may have hundreds of IDs for one query. Thanks.

EDIT: Just to clarify, I have the following array of (CarIDs, CarTypeMappingIDs) pairs. For the example above, it looks like this:

(3,2)
(3,6)
(7,3)
(7,4)

This list can be very long so I'm looking for an efficient way to query the DB to get the names. My question was about how I can use those IDs as part of my query.

Upvotes: 0

Views: 238

Answers (1)

wils484
wils484

Reputation: 275

You can JOIN multiple tables at once:

SELECT 
Car.name,
Types.typeName
FROM
CarTypeMappings
JOIN Car ON CarTypeMappings.CarID = Car.ID
JOIN Types ON CarTypeMappings.TypesID = Types.ID

Upvotes: 1

Related Questions