close

Privacy guaranteed - Your email is not shared with anyone.

Tricky SQL query

Discussion in 'Tech Talk' started by rsagona1, Dec 15, 2008.

  1. rsagona1

    rsagona1 Hello

    2,394
    0
    Jun 21, 2006
    Somewhere in MD
    I'm stumped. If someone can help me I'll give you 10,000 GT casino dollars.

    I have 2 tables: inventory and shopping cart.

    Inventory:

    #ID #Product #Price
    1 Table $100
    2 Chair $50
    3 Keyboard $20


    Then in a shopping cart of a client:

    ClientNo #ID
    1 1
    1 2
    1 3
    2...
    3...

    Which means client 1 has purchased items 1,2, and 3.

    Now here's the problem. At the end of the user's checkout, I'd like to dispaly the price of each product he/she purchased.

    I can easily do a loop in the recordset and run two subqueries, i.e. get a set of all the products the user ordered, then for each record, do another query on the inventory database to return the price.

    However, I know from school that there is a way to do a subquery without a loop.

    Something like:

    Select price,product from Inventory where ID = 'theID'.

    The problem is, theID is a recordset, NOT an individual record!:faint:

    I'm not sure what to do? Any help is appreciated.
     
  2. SELECT * FROM shopping_cart LEFT JOIN inventory ON shopping_cart.id = inventory.id WHERE shopping_cart.ClientNo = 1;


    By the way, I don't really like the idea of using a database table as your shopping cart. Why not cookies or sessions (with a memcached session_handler or something) so you can avoid constant that disk read/write?
     

  3. rsagona1

    rsagona1 Hello

    2,394
    0
    Jun 21, 2006
    Somewhere in MD
    Thanks! I'm going to try it right now.

    Actually, regarding the cookies, that would be good but the example I made above was actually just an example for simplicity. My app is a bit different.
     
  4. rsagona1

    rsagona1 Hello

    2,394
    0
    Jun 21, 2006
    Somewhere in MD
    Hey Cranklin,
    What are the specs on your AR? I read the 10 questions and didn't see the specs..
     
  5. hey rsagona1,
    it's a california legal AR chambered in .223... 10rd magazine pinned to the frame (bullet button). This one is a mega receiver + stag lower parts kit.
     
  6. rsagona1

    rsagona1 Hello

    2,394
    0
    Jun 21, 2006
    Somewhere in MD
    Thanks for the info on the AR. Nice gun!

    Regarding the SQL, I may have confused myself by trying to make it easier (my fault, not yours). Anyway, here is the actual table, if you wouldn't mind having a look I'd appreciate it.


    This is the invoice table for all clients. Notice that IID can be repeating, since an invoice can have multiple products.
    [​IMG]

    This is our inventory table:


    [​IMG]

    So my goal would be to attach the 'price' column from the second table, to the first table.

    It's possible that your code works but I am having trouble translating it. I keep getting NULL values.
     
    Last edited: Dec 16, 2008
  7. Hey rsagona,
    I'm not sure what your table names are, but i'll just assume they are "invoice" and "inventory", respectively.

    SELECT * FROM invoice LEFT JOIN inventory ON invoice.pcode = inventory.pcode;

    This will join your 2 tables based on the common column "pcode".
    So if you want to filter your query according to "cid", you can simply append the WHERE clause at the end...

    SELECT * FROM invoice LEFT JOIN inventory ON invoice.pcode = inventory.pcode WHERE invoice.cid = '20';

    OR, if you want to further fine tune it according to "cid" and "IID"...

    SELECT * FROM invoice LEFT JOIN inventory ON invoice.pcode = inventory.pcode WHERE invoice.cid = '20' AND invoice.IID = '543430494090bed50d48.97560882';

    Now, given that there exists a row in the inventory table with the same cid and IID values, it should return a fully joined row. If there is no row on the inventory table with the same cid and IID values, it will still return a fully joined row but with NULL substituted for the missing values.
     
  8. rsagona1

    rsagona1 Hello

    2,394
    0
    Jun 21, 2006
    Somewhere in MD
    You're a genius!!!!! Thanks!! Casino cash on the way.