CODEWITHSUNDEEP
mysqlindexing
Andurit
Andurit

Reputation: 5762

Foreign key in MySQL query

Can someone help me to understand how to use foreign key in MySQL Query? Let's say I have 2 tables

table VinDatabase
+-------+----------+------------+
| case_id| car_model|    vin    |
+-------+----------+------------+
|  1     |    VW    |  ABCDEFG  |
+-------+----------+------------+
|  2     |    VW    |  GFEDCBA  |     
+-------+----------+------------+
table cases
+-------+----------+------------+
|case_id|   date   |    price   |
+-------+----------+------------+
|   1   |08.04.2015|    11000   |
+-------+----------+------------+
|   2   |02.03.2015|     7000   |
+-------+----------+------------+

With INDEX: VIN in VinDatabase table and case_id in cases table

Because on my site users always search just by VIN number, how query can look to get to DATE for example?

Right now my query looks like:

SELECT * FROM VinDatabase WHERE vin = :vin

from where I get case_id and after that make new query:

SELECT * FROM cases WHERE case_id = :case_id

Is there some better way how to do it? Also how query will looks like if VIN / case_id are not uniqe?

EDIT: How it works from proces site:

  1. User come on page and add VIN to input ( let's say ABCDEFG )
  2. Server take VIN and need to find case_id in VINDatabase table
  3. With case_id it go to search to cases table.
  4. Output from both, VinDatabase and cases table

Upvotes: 1

Views: 55

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

You can do a JOIN on the two tables like this:

SELECT * FROM vinDatabase v INNER JOIN cases c
ON v.case_id = c.case_id
WHERE v.vin = 'ABCDEFG' AND c.case_id = 1

Output:

+--------+----------+------------+----------+------------+
| case_id| car_model|    vin     |   date   |    price   |
+--------+----------+------------+----------+------------+
|   1    |    VW    |  ABCDEFG   |08.04.2015|    11000   |
+--------+----------+------------+----------+------------+

Update:

In the event that multiple records exist for just a single given VIN number, you would have to modify the query to get back a single result. To get the most recent single entry for a given VIN number (with potentially multiple cases) you could use:

SELECT * FROM vinDatabase v INNER JOIN cases c
ON v.case_id = c.case_id
WHERE v.vin = 'ABCDEFG'
ORDER BY c.date DESC
LIMIT 1;

Upvotes: 1

Related Questions