Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    26

    Unanswered: Joining two columns together to be searched as one

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    (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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    (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".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Oct 2005
    Posts
    26
    Many thanks guys,

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

  5. #5
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    btw, your design is denormalized... Is there a reason you don't store make and model in a seperate table?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  6. #6
    Join Date
    Oct 2005
    Posts
    26
    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.

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •