Jump to content


Photo

MySQL Query: One of each


  • Please log in to reply
2 replies to this topic

#1 ldsmike88

ldsmike88
  • Members
  • PipPipPip
  • Advanced Member
  • 168 posts
  • LocationFlorida

Posted 26 March 2006 - 03:45 AM

I have a MySQL database with a whole lot of products in it. Each product has it's own row. Each product has a make, model, year, description etc. There are over 20 products with the same make but I need to query the database and display each make only once. Anyone know how to do this? Thanks!

Mike

#2 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 26 March 2006 - 05:24 AM

Look into the use of "group by" clause or "distinct".

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/gro...-modifiers.html[/a]

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/distinct-optimization.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/dis...timization.html[/a]

You also want to add the "order by" clause to have it sorted by what you want (make).


#3 ldsmike88

ldsmike88
  • Members
  • PipPipPip
  • Advanced Member
  • 168 posts
  • LocationFlorida

Posted 27 March 2006 - 05:01 AM

AWESOME! The GROUP BY worked perfect. Now I have one more question. Some of my products are compatible with multiple makes, such as the Chevy Silverado and the GMC Sierra. Most of their parts can fit the either truck. So in this case I set the make to "Chevorlet, GMC" istead of having two different rows for the same product and my query says WHERE make LIKE '%Chevorlet%'. With the Chevorlet and GMC products when I run the GROUP BY query it looks like this:

Audi
Buick
Cadillac
Chevorlet
Chevorlet, GMC
Chrysler
Chysler
Ford
GMC
Honda

Is there a way I can get everything inbetween the commas or everything before and after a comma and then erase the comma? Thanks! And thanks again toplay for the GROUP BY documentation.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users