Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2011
    Posts
    5

    Unanswered: Simple Query help

    Hello all,

    I have a database:

    The database is called "example".
    There are two tables called "ticketlines" and "receipts".
    In "ticketlines" there are three fields "ticket", "product" and "units".
    In "receipts" there are two fields "id" and "datenew".
    "ticket" and "id" are the same. They are related.

    EXAMPLE
    -TICKETLINES
    --TICKET
    --PRODUCT
    --UNITS
    -RECEIPTS
    --ID
    --DATENEW

    I need to extract this:
    The amount of "units" of "product" sold in the last 24 hours.

    So far, my query is this:

    SELECT product, units, ticket, id, datenew
    FROM example.ticketlines, example.receipts
    WHERE timediff(now(), datenew) < '24:00:00';

    Which is a terrible failure and returns 1000 rows of data from a database that only has 43 rows.

    Help a simpleton out, please!

    Thanks,
    Christian

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you are SELECTing from two tables
    what you are missing is the association between a a ticket and when someone bought the ticket (effectively the recipt). although a receipt could comprise more than one ticket. its theoretically possible, depending on the business rules that there may be more than one payment (ie more than one receipt) for a ticket purchase, ferinstance there could be a deposit + balance, or it could be that the purchaser didn't or couldn't pay for the while transaction at one time

    so you need some mechanism to link a ticket to a receipt. the most flexible wayt to do that is through an association table which identifies what tickets have been paid for as part of what receipt. of course you could simplify that by defining your business rule to be a recipt can cover many tickets, and that no part payments or deposits are accepted, in which case you could store the receipt ID as part of the ticket entity
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Posts
    5
    Thanks for the fast response.

    Sorry, I didn't explain myself very clearly, as usual!

    "Ticket" from table "Ticketlines" is identical to "id" from table "Receipts".

    So, I want to join all the data together (using a query) and then look at the result.

    For example, if I join the two tables I should end up with a table with only 4 rows, because "ticket" and "id" are the same, so it should return:

    ticket/id, product, units, datenew

    And then I can say "please show me only the rows from this new table where "datenew" was in the last 24 hours.

    That way I can see which "products" and how many "units" were sold in the last 24 hours.

    Does that make sense?

    Thanks again!

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to JOIN the two tables
    you need to define the relationship in the query between the two tables
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Posts
    5
    Legendary stuff, here is my working query:

    SELECT product, units
    FROM example.receipts
    INNER JOIN example.ticketlines
    ON ticketlines.ticket=receipts.id
    WHERE timediff(now(), datenew) < '12:00:00';

    Thanks a lot!

  6. #6
    Join Date
    Aug 2012
    Location
    Pakistan
    Posts
    1
    Inner join a good solution in this case.

Tags for this Thread

Posting Permissions

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