Using a part number to query corresponding data from separate tables
Good Morning Everyone! I'm having a mental issue on how i can get the data i need based on a part number that Iâm retrieving from one table. In the one table i have the top 200 sellers, in my other three tables i have all of the information that goes with the parts. I have written three queries that go to each of the three tables. Question 1: In all the tables the part number may fit 7 different applications, therefore the part number can be in there multiple times on my results. I have used the "DISTINCT" attribute to get the distinct number from the table that hosts the information. How can i pull all of the corresponding info (Year, Make, Model, etc) while Iâm using the distinct clause? Question 2: If i wanted to get all information from any of the three tables based on a single part number without duplicates, how would i write that. I'm not sure is i should be doing joins, or sub-queries, and/or unions. Here are my 3 queries: SELECT DISTINCT PartNumber FROM GriffinDataRevised INNER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number WHERE Top200.part_number = GriffinDataRevised.PartNumber SELECT DISTINCT Dominator.part_number FROM Dominator INNER JOIN Top200 ON Top200.part_number = Dominator.part_number ORDER BY Dominator.Part_Number ASC SELECT DISTINCT PartNumber FROM GriffinRaceDataNew INNER JOIN Top200 ON GriffinRaceDataNew.PartNumber = Top200.Part_Number WHERE Top200.part_number = GriffinRaceDataNew.PartNumber How can i simplify this process :) Thank you! Aaron ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341975 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
Hello Ian and thank you! I have tried both the left and right joins, the RIGHT join brought back the 15k results again, the LEFT join was bringing back so many that i killed the browser before it hurt something :) JOINS: SELECT * FROM GriffinDataRevised LEFT OUTER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number --This one seemed like a infinite loop was happening, crashed browser from results. SELECT * FROM GriffinDataRevised RIGHT OUTER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number 15k results again So if i know that its true that the Top200 table has 200 distinct part numbers and that the information table may have all 200 part numbers why how would i accomplish just getting the matching results for the 200 part numbers in the GriffinData table? It may be possible that the 98 results are the only parts that exist in the larger information table. But my query was still running wrong, correct? Thanks! ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341795 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
SQL Join Woes
Hello All! I'm trying my first Join of two tables and not having the best of luck... One table holds just a part number off our top 200 products, the second table holds a part number along with all the information that accompanies that part. I'm trying to pull back all the data from the information table that has a matching part number in my top 200 table. Here are a few ways i have tried : SELECT DISTINCT PartNumber FROM GriffinDataRevised WHERE PartNumber IN (SELECT part_number FROM Top200) -- This one brings back 15k results with tons of duplicates SELECT * FROM GriffinDataRevised WHERE PartNumber = (SELECT Part_Number FROM Top200 WHERE Top200.part_number = 'GriffinDataRevised.PartNumber') -- This one, no results shown SELECT DISTINCT PartNumber FROM GriffinDataRevised INNER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number WHERE Top200.part_number = GriffinDataRevised.PartNumber -- This one returns 98 results when there should be 200. I'm still trying to confirm if there are duplicate PN's in the list. Thank you, Aaron ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:341792 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
CF Permanent Redirect (301)???
Hello Everyone! I've been optimizing my site and checking it in google's webmaster tools, fixing it all to be a SEO sensation (LOL). I keep getting the advice to use a permenant redirect from my www.griffinrad.com to griffinrad.com. Here is what its saying to do: <.cfheader statuscode="301" statustext="Moved permanently"> <.cfheader name="Location" value="http://www.new-url.com";> My issue is i only have the one site (griffinrad.com), there is no old or new site that i'm redirecting. I'm just trying to get the people that put www before the url. When i look at my CTR and traffic, the WWW is hurting bad, but the non www url is doing well. How do i use this option? Do i really just put it at the top of all my pages and tell it to re-direct back to its self? That doesn't make allot of sense... Thank you in advance! Aaron P.S. Has anyone used http://websitegrader.com/ by HubSpot? I was curious of the validity of the information and advice. ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:340988 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
cfdirectory -- Element ROWCOUNT is undefined in MYFILELIST.
Hello Everyone! I'm trying to use the cfdirectory to see if a image file exists on my remote server. Here is my code: do something do something else The site is hosted on a shared server, i took the above path (hoping its my absolute path) from the error it threw. I'm trying to get the hosting company to supply me with the path. Does the above code look correct? Could it be my directory path? Thanks! Aaron ~| Order the Adobe Coldfusion Anthology now! http://www.amazon.com/Adobe-Coldfusion-Anthology/dp/1430272155/?tag=houseoffusion Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:339309 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
