10 Replies - 1111 Views - Last Post: 11 May 2012 - 09:33 AM Rate Topic: -----

#1 mossman65  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 19-September 11

SQL in CF, Joining tables to get value to compare to third table

Posted 09 May 2012 - 11:40 AM

Hello Everyone,

Thank you in advance.

My company is switching part numbers. We would like to display the new part number along with the old part number for the next few months to get customers and vendors used to the new system of numbers.

The main issue is that the data that was provided to me is set up totally different than our original data...

I would like to know how to do the following join inside of a cfquery/logic.

I have three tables:
  • Original data (Year, make, model, description, etc all in their own columns)
  • The new data (in one row i now have a "69 coronet, 70 corvette, 75 Camaro going on for 570 columns!!!!" along with other columns that provide specs for the part. I'm using this to build my drop downs)
  • New Part Numbers and applications (vehicles they fit)


I would like to doing something similar to:
SELECT PartNumber FROM Apps WHERE EXISTS
(SELECT YMM FROM YMM WHERE Apps.App1 = '69 Coronet' OR Apps.App2 = '69 Coronet'
 OR Apps.App3 = '69 Coronet' OR Apps.App4 = '69 Coronet' OR Apps.App5 = '69 Coronet'
 OR Apps.App6 = '69 Coronet' OR Apps.App7 = '69 Coronet' OR Apps.App8 = '69 Coronet'
 OR Apps.App9 = '69 Coronet' OR Apps.App10 = '69 Coronet' OR Apps.App11 = '69 Coronet'
 OR Apps.App12 = '69 Coronet' OR Apps.App13 = '69 Coronet' OR Apps.App14 = '69 Coronet'
 OR Apps.App15 = '69 Coronet' OR Apps.App16 = '69 Coronet' OR Apps.App17 = '69 Coronet'
OR Apps.App18 = '69 Coronet' OR Apps.App19 = '69 Coronet' OR Apps.App20 = '69 Coronet'
OR Apps.App21 = '69 Coronet' OR Apps.App21 = '69 Coronet' OR Apps.App22 = '69 Coronet'
)



This returns any new part numbers that fit a 69 Coronet with specific engine configurations (12 results for this example).

At this point i would like query the old data for a "1969" "Dodge" "Coronet" bring back the results and compare the engine configurations from the new data, update the part number for any that will become custom builds, and also set a new part number for the ones that we will continue to carry in stock in a new column with the new part number.

I'm concerned that this is going to be crazy slow for a search...

I hope i wrote this alright... And any suggestions on a better way to approach this would be welcome.

Is This A Good Question/Topic? 0
  • +

Replies To: SQL in CF, Joining tables to get value to compare to third table

#2 Craig328  Icon User is offline

  • I make this look good
  • member icon

Reputation: 1924
  • View blog
  • Posts: 3,462
  • Joined: 13-January 08

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 09 May 2012 - 11:44 AM

Moved this to Databases as the question is primarily SQL
Was This Post Helpful? 0
  • +
  • -

#3 mossman65  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 19-September 11

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 09 May 2012 - 12:06 PM

View PostCraig328, on 09 May 2012 - 11:44 AM, said:

Moved this to Databases as the question is primarily SQL



Thank you.
Was This Post Helpful? 0
  • +
  • -

#4 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 09 May 2012 - 03:14 PM

Are you able to provide a screenshot of part of the new data? If I understand you correctly when you say "this data goes on for 570 columns", then my first impression would be that you're boned.
Was This Post Helpful? 0
  • +
  • -

#5 mossman65  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 19-September 11

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 10 May 2012 - 06:45 AM

View Poste_i_pi, on 09 May 2012 - 03:14 PM, said:

Are you able to provide a screenshot of part of the new data? If I understand you correctly when you say "this data goes on for 570 columns", then my first impression would be that you're boned.


Yeah, its pretty nasty data... Our products can fit multiple makes, models, and years. One of our radiators it fits 500+ vehicles spanning ten years.

Thank you!

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#6 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 10 May 2012 - 07:11 AM

Hmm, the headers would have been handy. Anyhow, I get the gist.

I take it column L onwards are the make/models that the part fits? You should be able to translate the data into a normalised format, but you'll need some sort of code to read the Excel file (or CSV, which you could save it as), interpret each line, and write separate lines to either a new CSV file or a database.

I could give you a good schema for the DB if you like, I'll need to know the headers of that image, but the data rewrite is another matter. That would need to be done in a programming language of some sort for best results.
Was This Post Helpful? 0
  • +
  • -

#7 mossman65  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 19-September 11

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 10 May 2012 - 07:18 AM

View Poste_i_pi, on 10 May 2012 - 07:11 AM, said:

Hmm, the headers would have been handy. Anyhow, I get the gist.

I take it column L onwards are the make/models that the part fits? You should be able to translate the data into a normalised format, but you'll need some sort of code to read the Excel file (or CSV, which you could save it as), interpret each line, and write separate lines to either a new CSV file or a database.

I could give you a good schema for the DB if you like, I'll need to know the headers of that image, but the data rewrite is another matter. That would need to be done in a programming language of some sort for best results.


I would love a schema, Thank you! I'm asking one of our off site excel gurus if he can parse the data into a new row for each make/model that fits a particular part number. I wouldn't know where to begin to translate the data into a normalized format. Hopefully he can.

I have attached a small spreadsheet for your review. Thank you very much!
Was This Post Helpful? 0
  • +
  • -

#8 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 10 May 2012 - 07:21 AM

Hmm, I can't see the attachment. I'm off to bed now anyhow, won't be able to look at it for another 8 hours or so. Catch you in the morning.
Was This Post Helpful? 0
  • +
  • -

#9 mossman65  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 19-September 11

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 10 May 2012 - 07:31 AM

View Poste_i_pi, on 10 May 2012 - 07:21 AM, said:

Hmm, I can't see the attachment. I'm off to bed now anyhow, won't be able to look at it for another 8 hours or so. Catch you in the morning.

Thanks man!

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#10 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 10 May 2012 - 03:41 PM

Okay, at a pinch, a reasonable setup would be this:

Applications
    ID (PK)
    Description
    MakeID (FK -> Makes.ID)


Makes
    ID (PK)
    Description


PartTypes
    ID (PK)
    Description


Parts
    ID (PK)
    PartNumber (Unique)
    PartTypeID (FK -> PartTypes.ID)
    CoreWidth
    CoreLength
    CoreThickness
    TubeSize
    Rows
    Outlets
    AdditionalInfo
    Exception


ApplicationParts
    ID (PK)
    PartID (FK -> Parts.ID)
    ApplicationID (FK -> Applications.ID)
    ApplicationYear


You wouldn't necessarily need the Makes table, but I put it in there for completeness. You could also move the ApplicationYear column from the ApplicationParts table to the Applications table, depending on what level of normalisation / convenience you're after, but I'd need to know a lot more about your setup.

There's a few small issues with this schema though:

Firstly, do all part types (e.g. - radiator) have the same attributes, such as CoreWidth, CoreLength, TubeSize, Rows, etc? If not, then you'll need to abstract out the attributes into a separate table / tables, which tends to get a bit messy. There's a couple of ways to do this that I know of.

Secondly, the column labelled AddInfo2 only seems to have two values - N/A and TOC. Are these the only two values for that column? What do they mean? You could potentially compress two rows that differ only in AddInfo2 by using a flag, or pair of flags.
Was This Post Helpful? 0
  • +
  • -

#11 mossman65  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 32
  • Joined: 19-September 11

Re: SQL in CF, Joining tables to get value to compare to third table

Posted 11 May 2012 - 09:33 AM

View Poste_i_pi, on 10 May 2012 - 03:41 PM, said:

Okay, at a pinch, a reasonable setup would be this:

Applications
    ID (PK)
    Description
    MakeID (FK -> Makes.ID)


Makes
    ID (PK)
    Description


PartTypes
    ID (PK)
    Description


Parts
    ID (PK)
    PartNumber (Unique)
    PartTypeID (FK -> PartTypes.ID)
    CoreWidth
    CoreLength
    CoreThickness
    TubeSize
    Rows
    Outlets
    AdditionalInfo
    Exception


ApplicationParts
    ID (PK)
    PartID (FK -> Parts.ID)
    ApplicationID (FK -> Applications.ID)
    ApplicationYear


You wouldn't necessarily need the Makes table, but I put it in there for completeness. You could also move the ApplicationYear column from the ApplicationParts table to the Applications table, depending on what level of normalisation / convenience you're after, but I'd need to know a lot more about your setup.

There's a few small issues with this schema though:

Firstly, do all part types (e.g. - radiator) have the same attributes, such as CoreWidth, CoreLength, TubeSize, Rows, etc? If not, then you'll need to abstract out the attributes into a separate table / tables, which tends to get a bit messy. There's a couple of ways to do this that I know of.

Secondly, the column labelled AddInfo2 only seems to have two values - N/A and TOC. Are these the only two values for that column? What do they mean? You could potentially compress two rows that differ only in AddInfo2 by using a flag, or pair of flags.



Shaweet, Thank you for all your help! It will make this allot easier now. I think our Excel guy is prepping the data. Once i get i will get it built and hopefully get your opinion. Have a great day!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1