SQL While Loop Duplicates

Question

I got this in my SQL while loop:

PartID:1 Year:2003 ModelID:1375
PartID:1 Year:2004 ModelID:1375
PartID:1 Year:2005 ModelID:1375
PartID:2 Year:1995 ModelID:1244
PartID:2 Year:1996 ModelID:1244
PartID:2 Year:1997 ModelID:1244
PartID:2 Year:1998 ModelID:1244
PartID:2 Year:1999 ModelID:1244
PartID:2 Year:2000 ModelID:1244
PartID:2 Year:2001 ModelID:1244
PartID:2 Year:1996 ModelID:2361
PartID:2 Year:1997 ModelID:2361
PartID:2 Year:1998 ModelID:2361
PartID:2 Year:1999 ModelID:2361
PartID:2 Year:2000 ModelID:2361

But I need it like this for database insert (PartId, Start_Year, End_Year, ModelId):

PartID:1 Start Year:2003 End year:2005 ModelID:1375
PartID:2 Start Year:1995 End year:2001 ModelID:1244
PartID:2 Start Year:1996 End year:2000 ModelID:2361

Do you have any idea how to do that in SQL While Loop or in SQL query. These are tables:

model2year

id | model_id | year
1          1        1966
2          2        1973
3          2        1972
4          2        1971
5          2        1970

model2year2part

id | model2year_id | part_id
1             9521              1
2             9520              1
3             9519              1
4             8637              2
5             8636              2

These are example tables:

http://milversite.net/model2year.zip
http://milversite.net/model2year2part.zip

This is sql query for tables above

$result = mysql_query("SELECT * FROM model2year JOIN model2year2part ON model2year2part.model2year_id = model2year.id");

while($row = mysql_fetch_array($result))
{
   $part_id = $row['part_id'];
   $year = $row['year'];
   $model_id = $row['model_id'];
   echo "PartID:$part_id Year:$year ModelID:$model_id<br>";
}

but I need to insert that in this result table without duplicated model_id and instead of that with years range from min. to max for model_id:

result_table

part_id | model_id | start_year | end_year

Thank you!

Welcome to StackOverflow: if you post code, XML or data samples, PLEASE highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! That way, you don't need a flood of messy <br> and   tags, either!! — marc_s
– marc_s, Commented May 26, 2013 at 14:44

Oleksandr Fedorenko · Accepted Answer · 2013-05-26 11:09:19Z

1

Use GROUP BY clause with aggregate functions

SELECT Part_ID, Model_id,
       MIN(YEAR) AS Start_Year, MAX(YEAR) AS End_Year
FROM model2year JOIN model2year2part 
  ON model2year2part.model2year_id = model2year.id
GROUP BY Model_ID, Part_ID

edited May 26, 2013 at 11:09

answered May 26, 2013 at 9:50

Oleksandr Fedorenko

17k6 gold badges42 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Nebojsa Stankovic Over a year ago

Thank you Alexander, Can you please test it with this tables because i got error "mysql_fetch_array() expects parameter 1 to be resource, boolean given in" ... These are example tables: 178.238.230.233/model2year.zip 178.238.230.233/model2year2part.zip

Nebojsa Stankovic Over a year ago

Thank you. It probably works but its extremely slow becuse of 500MB table "model2year2part". is there any way to LIMIT query to test it. I tried with LIMIT 100 at the and but it still loading?

Andriy M Over a year ago

@NebojsaSta: This query is very simple and straightforward: on the one hand, no part of it is redundant, on the other, there is nothing missing here. It declares just everything the server needs to "know" to solve the problem (as laid out in your question). The performance is quite a different issue and, in my opinion, should be asked about as a new question. It's likely that you might just need to add proper indexing. I'm not sure whether it is so or which indices would work best, but you could ask that at Database Administrators, which to my knowledge is so far the best place to ask this kind of questions.

Collectives™ on Stack Overflow

SQL While Loop Duplicates

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related