Discuss Query help in the MySQL Help forum on Dev Shed. Query help MySQL Help forum discussing administration, SQL syntax, and other MySQL-related topics. MySQL is an open-source relational database management system (RDBMS).
Time spent in forums: 2 Days 4 h 23 m 13 sec
Reputation Power: 10
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.
Time spent in forums: 2 Months 3 Weeks 4 Days 12 m 19 sec
Reputation Power: 1063
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.