GlockTalk.com
Home Forums Classifieds Blogs Today's Posts Search Social Groups



  
SIGN-UP
Notices

Glock Talk
Welcome To The Glock Talk Forums.

 
  
Reply
 
Thread Tools Display Modes
Old 12-15-2008, 01:13   #1
rsagona1
Hello
 
rsagona1's Avatar
 
Join Date: Jun 2006
Location: Somewhere in MD
Posts: 6,370


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.

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!

I'm not sure what to do? Any help is appreciated.
rsagona1 is offline   Reply With Quote
Old 12-15-2008, 06:21   #2
CRANKLIN
Senior Member
 
Join Date: Jun 2001
Location: North Hollywood, CA
Posts: 256
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?
__________________
He said to them, "But now if you have a purse, take it, and also a bag; and if you don't have a sword, sell your cloak and buy one." -- Luke 22:36


To view links or images in signatures your post count must be 10 or greater. You currently have 0 signatures.
CRANKLIN is offline   Reply With Quote
Old 12-15-2008, 20:36   #3
rsagona1
Hello
 
rsagona1's Avatar
 
Join Date: Jun 2006
Location: Somewhere in MD
Posts: 6,370


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 is offline   Reply With Quote
Old 12-15-2008, 20:41   #4
rsagona1
Hello
 
rsagona1's Avatar
 
Join Date: Jun 2006
Location: Somewhere in MD
Posts: 6,370


Hey Cranklin,
What are the specs on your AR? I read the 10 questions and didn't see the specs..
rsagona1 is offline   Reply With Quote
Old 12-15-2008, 21:07   #5
CRANKLIN
Senior Member
 
Join Date: Jun 2001
Location: North Hollywood, CA
Posts: 256
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.
__________________
He said to them, "But now if you have a purse, take it, and also a bag; and if you don't have a sword, sell your cloak and buy one." -- Luke 22:36


To view links or images in signatures your post count must be 10 or greater. You currently have 0 signatures.
CRANKLIN is offline   Reply With Quote
Old 12-16-2008, 02:12   #6
rsagona1
Hello
 
rsagona1's Avatar
 
Join Date: Jun 2006
Location: Somewhere in MD
Posts: 6,370


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.
Tech Talk

This is our inventory table:


Tech Talk

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 by rsagona1; 12-16-2008 at 03:01..
rsagona1 is offline   Reply With Quote
Old 12-16-2008, 05:42   #7
CRANKLIN
Senior Member
 
Join Date: Jun 2001
Location: North Hollywood, CA
Posts: 256
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.
__________________
He said to them, "But now if you have a purse, take it, and also a bag; and if you don't have a sword, sell your cloak and buy one." -- Luke 22:36


To view links or images in signatures your post count must be 10 or greater. You currently have 0 signatures.
CRANKLIN is offline   Reply With Quote
Old 12-16-2008, 23:21   #8
rsagona1
Hello
 
rsagona1's Avatar
 
Join Date: Jun 2006
Location: Somewhere in MD
Posts: 6,370


You're a genius!!!!! Thanks!! Casino cash on the way.
rsagona1 is offline   Reply With Quote
Old 12-17-2008, 00:06   #9
CRANKLIN
Senior Member
 
Join Date: Jun 2001
Location: North Hollywood, CA
Posts: 256
glad to help!
__________________
He said to them, "But now if you have a purse, take it, and also a bag; and if you don't have a sword, sell your cloak and buy one." -- Luke 22:36


To view links or images in signatures your post count must be 10 or greater. You currently have 0 signatures.
CRANKLIN is offline   Reply With Quote

 
  
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump




All times are GMT -6. The time now is 06:36.




Homepage
FAQ
Forums
Calendar
Advertise
Gallery
GT Wiki
GT Blogs
Social Groups
Classifieds


Users Currently Online: 682
236 Members
446 Guests

Most users ever online: 2,672
Aug 11, 2014 at 2:31