Tricky SQL query

[rsagona1]

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.

[CRANKLIN]

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?

[rsagona1]

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.

[rsagona1]

Hey Cranklin,
What are the specs on your AR? I read the 10 questions and didn't see the specs..

[CRANKLIN]

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.

[rsagona1]

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.
http://img116.imageshack.us/img116/243/invoicesdg3.jpg

This is our inventory table:


http://img116.imageshack.us/img116/5023/productcodesxo3.jpg

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.

[CRANKLIN]

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.

[rsagona1]

You're a genius!!!!! Thanks!! Casino cash on the way.

[CRANKLIN]

glad to help! :)