CODEWITHSUNDEEP
mysqllogic
Dan
Dan

Reputation: 87

Select a DISTINCT ID, then pull data from another table

I have 2 MySQL tables, one for parts, and one for years. I can't figure out how to make a table on stackoverflow.. keeps making headers so here's my weak attempt to explain what I need.

Table 1

id | part_id | years
====================
 0 |    15   | 1945
 1 |    15   | 1946
 2 |    16   | 1944
 3 |    16   | 1947
 4 |    16   | 1948
 5 |    17   | 1953

As you may have guessed, part_id is the id number of the part in the parts table. Now, I know I have this to pull out a distinct part id, based on the year. That part is easy.

SELECT DISTINCT part_id FROM `years` WHERE year BETWEEN 1945 AND 1949

This is just an example, but that works exactly like I want it to. Gives me

15 and 16. Just one time. Which is great.

Now, do I need to do a loop in php to get the information from parts? I'm not sure how to do a join here.

<?php
foreach($item_pulled_from_db as $newid) {
    $query = "SELECT * FROM 'parts' WHERE id = $newid";
} // I know there's more stuff to do in here, just a basic overview for you to look at
?>

Should I do the above code? Is there a way to select a DISTINCT part_id and then pull the data from another table for that ID in MySQL? Or do I have to do a loop like this?

Edit: I hope this isn't too confusing of a question. I'm not very good with words, which is why I like to program. :)

Upvotes: 0

Views: 314

Answers (3)

Ross Smith II
Ross Smith II

Reputation: 12189

This query give you the result you want:

SELECT DISTINCT
    p.*
FROM 
    years y
INNER JOIN
    parts p ON p.id = y.part_id
WHERE 
    y.year BETWEEN 1945 AND 1949

Upvotes: 0

drew010
drew010

Reputation: 69967

You could pull this off using a JOIN in a single query. Try:

SELECT `parts`.* FROM `parts`
INNER JOIN `years` ON `years`.`part_id` = `parts`.`id`
WHERE `years`.`year` BETWEEN 1945 AND 1949

Execute that single query from PHP and then fetch the result set. It should be the same as what you would get using the multiple queries.

Upvotes: 0

Barmar
Barmar

Reputation: 781716

Use a join:

SELECT parts.*
FROM parts
JOIN (SELECT DISTINCT part_id
      FROM years
      WHERE year BETWEEN 1945 AND 1949) years
ON parts.id = years.part_id

Upvotes: 1

Related Questions