close

Privacy guaranteed - Your email is not shared with anyone.

Welcome to Glock Talk

Why should YOU join our Glock forum?

  • Converse with other Glock Enthusiasts
  • Learn about the latest hunting products
  • Becoming a member is FREE and EASY

If you consider yourself a beginner or an avid shooter, the Glock Talk community is your place to discuss self defense, concealed carry, reloading, target shooting, and all things Glock.

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.