Re: SQL Join Woes
Thank you! I will research the UNION Clause. You have been a great help! On Tue, Feb 1, 2011 at 3:13 PM, Ian Skinner wrote: > > On 2/1/2011 11:35 AM, Aaron M Renfroe wrote: > > On another note, the > > query that brought back 98 results may have been working right, i found > > another 78 records in another table for race car radiators, i'm almost > > guessing that the last few are in another table that would make the total > > 200 records. > > Then you are probably looking at three SELECT statements to return all > the desired data for the record set. My first choice would probably to > do all three SELECTS in a single query with a UNION clause to combine > them into one record set. But this is not the only option available. > > > > ~| 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:341800 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
On 2/1/2011 11:35 AM, Aaron M Renfroe wrote: > On another note, the > query that brought back 98 results may have been working right, i found > another 78 records in another table for race car radiators, i'm almost > guessing that the last few are in another table that would make the total > 200 records. Then you are probably looking at three SELECT statements to return all the desired data for the record set. My first choice would probably to do all three SELECTS in a single query with a UNION clause to combine them into one record set. But this is not the only option available. ~| 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:341799 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
On 2/1/2011 11:35 AM, Aaron M Renfroe wrote: > But i'm now getting an error that the part_number field > is ambiguous. Ugh That just means that the field is in both (multiple) tables and the database wants you to tell it which table you want to use to get the value for this column to use in this record set. Just prepend that column name with a table name. You seem to be doing that in all the fields except the 'Make' field in the ORDER BY clause. Just add a table name to that field as well. ~| 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:341798 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
I guess it could be both. While some part numbers can fit multiple years, makes, and models of a vehicle others just fit one. The top 200 are our best selling radiators. In theory, i want to hit the master table, pull out all the information on the radiator based on the part number being supplied from the top 200. Here is a query with all the fields from the Master table, and the Top 200 table has nothing but a part number: SELECT GriffinDataRevised.PartNumber, GriffinDataRevised.Make, GriffinDataRevised.Model, GriffinDataRevised.Year, GriffinDataRevised.Engine, GriffinDataRevised.Edition, GriffinDataRevised.TransCooler, GriffinDataRevised.OilCooler, GriffinDataRevised.HorsePower, GriffinDataRevised.Comments, GriffinDataRevised.Outlets, GriffinDataRevised.TubeSize, GriffinDataRevised.Rows, GriffinDataRevised.CoreSize, GriffinDataRevised.Revision FROM GriffinDataRevised INNER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.part_number WHERE GriffinDataRevised.PartNumber = Top200.part_number ORDER BY Make DESC Does that help? Thank you so much for bearing with me. On another not, the query that brought back 98 results may have been working right, i found another 78 records in another table for race car radiators, i'm almost guessing that the last few are in another table that would make the total 200 records. But i'm now getting an error that the part_number field is ambiguous. Ugh Thank you! On Tue, Feb 1, 2011 at 1:47 PM, Ian Skinner wrote: > > On 2/1/2011 10:22 AM, Aaron Renfroe wrote: > > Hello Ian and thank you! > > > > But my query was still running wrong, correct? > > Not necessarily, maybe your data is wrong. You may need to provide some > more description on what data is in each of these tables and how you are > trying to utilize it before we can help much more. > > IS there a ONE TO ONE or a ONE TO MANY relationship between Top200 and > GriffinDataRevised? If ONE TO MANY, do you not want all the record from > the MANY side? IF not all the records, which record of the MANY is the > one that you want? > > ~| 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:341797 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
On 2/1/2011 10:22 AM, Aaron Renfroe wrote: > Hello Ian and thank you! > > But my query was still running wrong, correct? Not necessarily, maybe your data is wrong. You may need to provide some more description on what data is in each of these tables and how you are trying to utilize it before we can help much more. IS there a ONE TO ONE or a ONE TO MANY relationship between Top200 and GriffinDataRevised? If ONE TO MANY, do you not want all the record from the MANY side? IF not all the records, which record of the MANY is the one that you want? ~| 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:341796 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
Re: SQL Join Woes
a standard join looks like this.. select foo from a join b on a.id = b.id So yours would look something like SELECT PartNumber FROM GriffinDataRevised d JOIN Top200 t on d.partnumber = t.part_number You're not technically doing a join, you're doing a sub query. On Tue, Feb 1, 2011 at 11:41 AM, Aaron Renfroe wrote: > > 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:341794 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/groups/cf-talk/unsubscribe.cfm
Re: SQL Join Woes
On 2/1/2011 9:41 AM, Aaron Renfroe wrote: > Hello All! > > SELECT DISTINCT PartNumber FROM GriffinDataRevised > INNER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number > WHERE Top200.part_number = GriffinDataRevised.PartNumber INNER JOIN will enforce a filter that will only return records in a given 'partnumber' is in BOTH tables. The return of 98 recrods would indicate that there are only 98 values of 'partnumber' that are in both tables. If that is expected and known behavior then what you want is an OUTER JOIN that says return all records from one table PLUS any records from the other table IF they match. IE FROM GriffinDataRevised LEFT OUTER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number This will return all the records from the table on the LEFT side of the JOIN 'GriffinDataRevised' OR FROM GriffinDataRevised RIGHT OUTER JOIN Top200 ON GriffinDataRevised.PartNumber = Top200.Part_Number This will return all the recrods from the table on the RIGHT side of the JOIN, 'Top200' Some database management systems support the FULL OUTER JOIN that will return unmatched records from BOTH sides of the join. ~| 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:341793 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
