We need to take the base number (W31 for example) and compare it to other parts with color codes (W31GR)(in this case the color codes are 2 characters so the base number in the driver table would be entered as W31 with a double underscore). The report should look at the applications on the base number and each part number with color codes and will export applications that are different among the whole group. I will send an email with a detailed example. however, for some suppliers, the color code is not 2 characters at the end of the part. It could be that the base number is 9517 and the three colors available are 95171 for black, 95172 for tan and 95173 for gray and there is no existing part number equal to 9517 itself. so in this case, the report would compare 95171/ 95172/ 95173 based on the driver table base number of 9517 with one underscore after it.
I'm sure this is not 100% correct. And I know it's going to perform EXTREMELY slowly, but I think I've got your logic nailed. Or, if not, I'm awfully close: CREATE TABLE TableA (BasePart CHAR(6)) INSERT INTO dbo.TableA (BasePart) VALUES ('MCNW10'), ('MCNW20'); CREATE TABLE TableB ( DetailedPart CHAR(8), Attribute1 CHAR(4), Attribute2 CHAR(5), Attribute3 CHAR(8) ); INSERT dbo.TableB (DetailedPart, Attribute1, Attribute2, Attribute3) VALUES ('MCNW10GR', '2007', 'CLASS', 'FLOORMAT'), ('MCNW10TN', '2010', 'CLASS', 'FLOORMAT'), ('MCNW10TN', '2010', 'CLASS', 'FLOORMAT'), ('MCNW20BL', '2011', 'CLASS', 'FLOORMAT'), ('MCNW20BL', '2019', 'CLASS', 'FLOORMAT'), ('QRQR50XL', '1963', 'CLASS', 'FLOORMAT'); WITH JoinTable AS (SELECT *, ROW_NUMBER() OVER (ORDER BY tb.DetailedPart DESC) AS RowNum FROM dbo.TableA AS ta JOIN dbo.TableB AS tb ON ta.BasePart = LEFT(tb.DetailedPart,6) ) SELECT jt1.* FROM JoinTable AS jt1 JOIN JoinTable AS jt2 ON jt2.DetailedPart = jt1.DetailedPart AND (jt2.Attribute1 jt1.Attribute1 or jt2.Attribute2 jt1.Attribute2 or jt2.Attribute3 jt1.Attribute3) UNION SELECT jt1.* FROM JoinTable AS jt1 WHERE jt1.RowNum = (SELECT TOP 1 ijt1.RowNum FROM JoinTable AS ijt1 WHERE jt1.DetailedPart = ijt1.DetailedPart ORDER BY ijt1.RowNum) This returns four rows, two for the MCNW10 that almost match and two for the MCNW20 that match but have differences in the Attributes column. And it eliminates one of the partial matches in MCNW10 that has identical values in the Attributes columns. It's an ugly, ugly query, but from what I can tell, it's doing what you're asking of it.
These values you're using may be candidate keys, but they are not good for setting up relationships between tables. I would strongly recommend examining the design and coming up with better primary keys, even if you have to add an artificial key to the table. Not only are the JOIN operations going to be exceedingly difficult to write, the performance is going to be a complete horror. My best answer for you is to redesign your database.
Ok. I will try to give explanation in detail 1. There are two table getting data pull from warehouse 2. Warehouse contains data like this. 3. List item > BasePart Detailed Part Attribute1 Attribute2 Attribute3 > MCNW10 MCNW10GR 2007 CLASS FLOORMAT > MCNW10 MCNW10TN 2010 CLASS FLOORMAT > MCNW10 MCNW10BL 2011 CLASS FLOORMAT For a given base number there is multiple detailed part with colorcode at the end Two table are pull data from warehouse i.e, (Table A and Table B) Table A and Table B are of different structure Table A Only 1 column. i.e, > BasePart > MCNW10 > MCNW20 And Table B contain 4 column > Detailed Part Attribute1 Attribute2 Attribute3 > MCNW10GR 2007 CLASS FLOORMAT > > MCNW10TN 2010 CLASS FLOORMAT > > MCNW20BL 2011 CLASS FLOORMAT > > MCNW20BL 2019 CLASS FLOORMAT Now in first query :- Base part from Table A look into table B Detailed part column And if it found close matched in table B, then it look for how many close match it does. Then it will check any difference in attributes for a all closest match part > Attribute1 Attribute2 Attribute3 > > MCNW10GR 2007 CLASS FLOORMAT > > MCNW10TN 2010 CLASS FLOORMAT > > MCNW20BL 2011 CLASS FLOORMAT > > MCNW20BL 2019 CLASS FLOORMAT > In this case for a base part MCNW10 we got two closed match details part i.e, MCNW10GR and MCNW10TN. And attribute 2 (Year is changing), so we need to have this in our output query (Report). Please help me to designed a query Thanks Grant!!! :)