SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard bronze trophy Tailslide's Avatar
    Join Date
    Feb 2006
    Location
    Bedford, UK
    Posts
    1,687
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Distinct and sub-queries?

    Hi all.

    I'm hoping you can point me in the general direction of what I need to do.

    I've got 2 tables (abbreviated):

    orders: order_id, id, sessionid
    products: id, title, ref

    What I need is a select query which will pull all the info from both but display only one row per orders.id.

    I think I need to use DISTINCT but I've read that it doesn't work with JOINs - is there a way of doing a subquery to pull the rest of the information out?

    I know this is wrong but something like:

    PHP Code:
    SELECT DISTINCT orders.id (SELECT orders.sessionidproducts.idproducts.title from ordersproductsWHERE sessionid='$sessionidnow' 
    Does that make any sense?

    Any suggestions welcomed!
    Little Blue Plane Web Design
    Blood, Sweat & Rust - A Land Rover restoration project

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    of course DISTINCT works with JOINs

    the part i'm having trouble with is understanding how the two tables are related

    perhaps you could give the real table layouts?

    also, if you want only one row per order, which one should it be?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy Tailslide's Avatar
    Join Date
    Feb 2006
    Location
    Bedford, UK
    Posts
    1,687
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You see! Never believe what you read!!

    Ok so, this is for an informal wish-list rather than a proper cart (no need for quantities or prices etc). The user hits an "add to list" button on various products and that adds a row in the order table which adds an order_id, the product id and the current session id of the user.

    So what should happen is when the user goes to the wish list page they get a list of the products they've added - but since they could in theory add a product multiple times I want the product to be listed only once.

    The tables are (With an example):

    Products:

    id | ref | title
    ----------------
    20 | abc1 | Chair
    21 | abc2 | Table
    22 | abc3 | bookcase

    Orders:

    order_id | id | sessionid
    -----------------------
    2 | 20 | 1234556676778888990etc
    3 | 20 | 1234556676778888990etc
    4 | 21 | 1234556676778888990etc

    The wish-list page would list each distinct product ordered for that session id.

    So I need a select query that will end up like this for the user's sessionid:

    Ref | Title
    --------------
    abc1 | Chair
    abc2 | Table

    Hopefully that makes a little more sense!
    Little Blue Plane Web Design
    Blood, Sweat & Rust - A Land Rover restoration project

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    yes it makes sense

    let me suggest something else instead of screening for duplicates on the way out of the database -- screen for duplicates on the way in
    Code:
    ALTER Orders ADD UNIQUE ( id , sessionid )
    that will prevent dupes automagically

    then to retrieve a user's wish list, just run this --
    Code:
    SELECT p.ref
         , p.title
      FROM Orders AS o
    INNER
      JOIN Products AS p
        ON p.id = o.id
     WHERE o.sessionid = 123093701230937012309370etc
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy Tailslide's Avatar
    Join Date
    Feb 2006
    Location
    Bedford, UK
    Posts
    1,687
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Rudy - thanks for this.

    I haven't come across ALTER before (yes I know - I really must put some time aside to learn this stuff properly!) so I had a google to try to figure it out.

    I thought that it would be best to re-create the table with the two unique fields - (didn't know you could have two uniques!). BUT now if the user tries to enter a product more than once they get the duplicate field error message.

    How would I stop that?

    Also - probably not an issue but since one of these uniques is a session id is there a possibility of exceeding max-length of the fields?
    Little Blue Plane Web Design
    Blood, Sweat & Rust - A Land Rover restoration project

  6. #6
    SitePoint Wizard bronze trophy Tailslide's Avatar
    Join Date
    Feb 2006
    Location
    Bedford, UK
    Posts
    1,687
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Edit: Rudy - I've found a way around this which generally makes life easier - before submitting the data to the table, checking if a row exists with the id and sessionid in there - if not then it runs the insert query. I don't know if this is a good approach or not but it seems to work! Thanks again for your help.
    Little Blue Plane Web Design
    Blood, Sweat & Rust - A Land Rover restoration project

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    checking first is not quite the best strategy, because that's twice as many SQL statements as you need

    if a user enters a product more than once, you ~want~ the database to return an error

    when your app detects the error, you can turn it into a user-friendly message that says something like "sorry, you've already entered that product"

    this would be the same message that you're displaying now after doing the SELECT, except you don't need to do the SELECT before the INSERT, just do the INSERT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Wizard bronze trophy Tailslide's Avatar
    Join Date
    Feb 2006
    Location
    Bedford, UK
    Posts
    1,687
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah ok - thanks Rudy, I'll have another go.
    Little Blue Plane Web Design
    Blood, Sweat & Rust - A Land Rover restoration project


Bookmarks

Posting Permissions

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