Hi all,

I have three questions to ask that hopefully can be answered. My database server is MySQL 4.1 and my database table contains the following example data:

Id | Store | Make | Model | Price
---------------------------------------------------
1 | Store1 | JVC | AV36D502 | 100.00
2 | Store2 | JVC | AV36D502 | 400.00
3 | Store1 | Panasonic | CT32HX41 | 250.00
4 | Store2 | Panasonic | CT32HX41 | 350.00
5 | Store1 | Sony | KV32S42 | 400.00
6 | Store2 | Sony | KV32S42 | 500.00

(1) Is it possibe to have in my select statement the following: "WHERE ((Make, Model) = 'Sony KV32S42')", I have already tried this but it throws up errors. What I'm trying to do is to some how join together the Make and Model columns and then to see if it matches the search term.

(2) If I was to "SELECT DISTINCT Make" or equivalent, rather than returning 3 rows is there anyway to return just the one row but with the value of "JVC Panasonic Sony" i.e. a string of all Makes, or is this impossible.

and (3) I have another select statement "SELECT DISTINCT Make, MIN(Price), MAX(Price)" with the output shown below. If I "ORDER BY Price ASC" the default is that it orders it by the maximum price, what I want to do is to order it by the minimum price only. "ORDER BY MIN(Price) ASC" has errors or again is this impossible.

Make | MIN(Price) | MAX(Price)
---------------------------------------
JVC | 100.00 | 400.00
Panasonic | 250.00 | 350.00
Sony | 400.00 | 500.00

I know I'm asking a lot but again I just can't find any good information on the net. Thanking you in advance.

(1) where concat_ws(' ',make.model) = 'Sony KV32S42'

(2) with the GROUP_CONCAT function

(3) you can't use DISTINCT here

select Make, MIN(Price) as minprice, MAX(Price) as maxprice
from yourtable group by Make order by minprice

(1) WHERE make = 'Sony' AND model = 'KV32S42'.
(2) AFAIK, this is impossible with "standard" SQL.
(3) See r937's solution, or alternatively "ORDER BY 2".

Many thanks guys,

(1) and (3) work perfectly now, (2) will work once I upgrade to mySQL 4.1 or 5.0

btw, your design is denormalized... Is there a reason you don't store make and model in a seperate table?

I'm sorry I don't understand, why would you want to put make and model in seperate tables, surely that would make the design more complex? Or would it be better (more efficient) if I just had the one column called "product" which would store the make and model, i.e. Sony KV32S42.

It would make the model slighly more "complex" in that you will have an extra table, sure. However it will make the model much simpler in that you will have far more flexibility with a "normalized" design. Read up at this site. It has some good models for what I'm talking about.

You do have the right general idea though. If you were to put make and model in a seperate table with a unique key, you could then use that key to store prices for competitors. When it comes time to report, your queries become far easier and more intuitive, with less margin for error.