View Full Version : Tricky SQL query
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.
#ID #Product #Price
1 Table $100
2 Chair $50
3 Keyboard $20
Then in a shopping cart of a client:
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.
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.
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?
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.
What are the specs on your AR? I read the 10 questions and didn't see the specs..
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.
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.
This is our inventory table:
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.
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.
You're a genius!!!!! Thanks!! Casino cash on the way.
glad to help! :)
vBulletin® v3.8.7, Copyright ©2000-2013, vBulletin Solutions, Inc.