Thread: Query help

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    82
    Rep Power
    10

    Query help


    I have a table that stores transactions/receipts in the form

    sale item / receipt # / member number
    water / 1000 /
    coke / 1000 /
    Total / 1000 / 1234

    so each item sold has its own row in the table and then there is a total row where everything is summed up and we record the member number in the total row. How would we go about writing a query to pull all the rows that match the receipt number for the member number we're looking for?

    so if we're essentiall looking for "select * from transaction where member_number = '1234'" but also pull the rest of the rows with the same receipt number.

    thanks for any help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    Code:
    SELECT receipt.*
      FROM transactions AS member
    INNER 
      JOIN transactions AS receipt  
        ON receipt.receipt_number = member.receipt_number
     WHERE member.member_number = 1234

    Comments on this post

    • Jacques1 disagrees : I would have expected some kind of comment on the data model and not just the usual "Here's your free solution"...
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2004
    Posts
    82
    Rep Power
    10
    thanks
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,930
    Rep Power
    1045
    Hi,

    this is not a good data model. Your current problem is actually a symptom of this, and you should view it as a warning sign.

    You seem to confuse SQL tables with tables in Excel or something. But those are two completely different concepts. In Excel, it's perfectly fine to have a list of transactions and then a total row at the bottom of the table. In SQL, however, a table expresses a certain relation. Something like "user x writes post y in thread z". In other words, each row makes a certain statement.

    Your table isn't anything like that. It's a mixture of different information. One half of the rows describe transactions (of an unspecified member), others describe the sum of an receipt. It's basically two tables merged into one, misusing the "item" column as a kind of flag.

    Now, you can do that in the sense of "MySQL doesn't complain". But you won't be happy with it if your data is important or your application grows:

    • You have no data integrity whatsoever. Since you cannot set up a foreign key on the "item" column, it can contain anything. If your application inserts "ä%1&" as an "item", your table will happily accept it.
    • You can easily end up with conflicting data. What if the member of a transaction is different from the member of the corresponding receipt?
    • Every single query on the table requires you to filter the rows first, depending on which half you're interested in. For example, you cannot simply do a COUNT(*) to get the number of transactions. You first have to exclude the "total" rows.
    • The model is confusing and simply not elegant. Thousands of empty "member" entries just to leave space for the total rows?


    I strongly recommend that you fix the data model. Then the simple query you wrote down will actually work, and you don't need others to write complicated workarounds.

    Comments on this post

    • r937 disagrees : i would have expected some kind of suggestion for a new data model and not just the usual diatribe/rant about what's wrong with the current one
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,930
    Rep Power
    1045
    Originally Posted by r937
    r937 disagrees: i would have expected some kind of suggestion for a new data model and not just the usual
    diatribe/rant about what's wrong with the current one
    I said it's two tabled merged into one. Split them, and there's your new data model.

    I won't write down the exact queries, because I don't believe in copypasta.

    Comments on this post

    • paulh1983 agrees : if someone says why the current model is wrong then I think it is good enough advice.
    The 6 worst sins of securityHow to (properly) access a MySQL database with PHP

    Why can’t I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,029
    Rep Power
    4210
    Originally Posted by Jacques1
    Split them, and there's your new data model.
    quatsch und blödsinn

    Comments on this post

    • Jacques1 disagrees : Get your vanity under control and start helping people. Thanks.
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo