|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
September 29th, 2003, 05:23 PM
|
|||
|
|||
|
Need help with this SQL
I have 3 tables that are all related via primary keys.
the first table is my baskets table. It used basketID the 2nd table is content_list and it uses itemID has its key the 3rd table uses both and contains basketID and itemID as the only fields, both being primary keys. Seems like a perfect match so far I am in the process of putting a page together that will do the following: 1. User will come to the page via a link specifying a basketID, such as basketID=5. 2. That id gets passed to the next page, the page I am working on. 3. I want my query to output any items that are associated with that particular basketID. 4. Those items come from itemID/itemname in the content_list. Seems pretty simple, I hope to someone like you, but for me, this is like working on the human brain. So in a nutshell, if the basketID =5, then where basketID=5 in the basket_content table, any itemID number associated with basketID=5 should be shown. Now, instead of showing the itemID number, I want it to show the itemname instead. It wouldn't make much sense to the user if it was a number, would it?  Here is my current sql Code:
<cfquery name="list_contents" datasource="something"> SELECT basketID, itemID, itemname FROM baskets INNER JOIN basket_content USING (basketID) INNER JOIN content_list USING (itemID) ORDER by itemname </cfquery> My output, which is in coldfusion, is like such Code:
<CFOUTPUT query="list_contents" group="basketID">#itemname#</CFOUTPUT> Just so you know for additional information, I do have another sql statement on top of the page to query for the basketID, it is like this Code:
<cfquery name="basket_items" datasource="something"> SELECT basketID, basketname FROM baskets WHERE basketID=#URL.basketID# </cfquery> Now, I don't know where my logic is wrong, what needs to be changed for this whole IDEA to work. I would really appreciate your input on what I need to change to make it work Thanks Bryan
|
|
#2
September 29th, 2003, 06:47 PM
|
|||
|
|||
|
I have tried this option as well
Code:
<cfquery name="list_contents" datasource="something"> SELECT basketID, itemID, itemname FROM baskets INNER JOIN basket_content ON baskets.basketID=basket_content.basketID INNER JOIN content_list ON basket_content.itemID=content_list.itemID ORDER by itemname </cfquery> but it doesn't work either
|
|
#3
September 29th, 2003, 08:25 PM
|
||||
|
||||
|
if you've already got the two queries, leave them the way they are
an alternative would have been to combine them into one query, but since you have two, leave them however, since you're getting basket information in the first query, you don't need to get it again in the second so your second query should be Code:
<cfquery name="list_contents" datasource="something">
SELECT content_list.itemID
, content_list.itemname
FROM basket_content
INNER
JOIN content_list
on basket_content.itemID = content_list.itemID
WHERE basket_content.basketID=#URL.basketID#
ORDER
by content_list.itemname
</cfquery>
Code:
<CFOUTPUT query="list_contents">#itemname#</CFOUTPUT> http://r937.com/
|
|
#5
September 29th, 2003, 08:53 PM
|
||||
|
||||
|
it is the same comma as in this sentence --
SELECT A,B FROM THETABLE sheesh bryan, you gotta slow down and take it easy, man ;o)
|
|
#6
September 29th, 2003, 08:53 PM
|
|||
|
|||
|
GENIOUS, PURE GENIOUS RUDY. If I owned a business and made alot of money, I would have you on my staff
Here is a screen shot I get http://www.btkdesigns.com/screenshot.gif Thanks alot. I am sure another question will be coming. Bryan
|
|
#7
September 29th, 2003, 09:19 PM
|
|||
|
|||
|
Hey, when I spend the whole day trying to get something like this to work, and then someone like yourself bestows upon me the key to life (ok, not the key to life, but getting my code to work
), I get all excited and knowledge hungryI will tell you what. I don't officially have a job right now, but I did just get hired and start monday. When I have some funds accumulated and have a few bucks to have leeway with, I am going to send you $50 for all your hard work you have put into this project of mine. I know its not much, but I am 22 and just getting a job. I really am appreciative of the work you have done
|
|
#8
September 29th, 2003, 09:40 PM
|
||||
|
||||
|
well, that'd be nice, but i was a student once too, hang onto your cash and pass the good will forward, help somebody else out in turn, okay?
rudy
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Need help with this SQL |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
