asked
JOIN WITH %LIKE%
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.
Hello Grant, I need your help once again, due to change in requirement. Logic: The Only difference between BasePart and detailed part is last two character. For eg:- Basepart = MCNW31-> DetailedPart-> MCNW31BL Or Basepart = MCNW31-> DetailedPart-> MCNW31RD Here BL or RD is missing from Base part. Now we need to check other attributes are fully same for both MCNW31BL and MCNW31RD. And if any of the attribute value is not same then it should appear in query result. Please help me Grant, Thanks a ton
So you're saying that if something ending in RD or something ending in BL have different attributes they need to be checked as the same object? Then don't bother using the detailed part in the joins. Instead us the base part number. You just have to adjust the query above a little bit for this.
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.
Thanks Grant for your valuable suggestion, but unfortunately client didn’t allow me to change anything on Database level. They just want report as follow:- Two Table suppose Table A and Table B Table A: MCNW10 Table B Year part# remarks CLASS line 2002 MCNW10GR Front CLASS FLOORMAT 2002 MCNW10GR Front CLASS FLOORMAT 2003 MCNW10GR Front CLASS FLOORMAT 2004 MCNW10GR Front CLASS FLOORMAT Here year is getting change for same Part#, so report need to contain TOP 1 record, avoiding duplicate. Report must appear like Year part# remarks CLASS line 2002 MCNW10GR Front CLASS FLOORMAT Similarly if Table A contain MCNW20 Then it look for similar part on table B and again any changes on attribute for the similar part, and if any need to be in report. Year part# remarks CLASS line Table A Part 2002 MCNW20GR Front CLASS FLOORMAT MCNW20 2002 MCNW20GR Front BULK FLOORMAT MCNW20 Please help me!!! :)
I don't understand where the second value is coming from and how does that relate back to your join. We seem to be all over the map on this data and structure. Is there nothing that indicates a change in values? Why are there tons and tons of identical rows with nothing differentiating them? You could try using the DISTINCT operation in the query, but that has performance issues too. Again, I'm having a hard time making suggestions because I don't understand the structure or the data.
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!!! :)
I think I have a rough understanding... maybe. But I don't see how you want to see the differences between MCNW10TN and MCNW10GR as changing attributes. Wouldn't it make more sense, based on what you described, to be getting MCNW20BL and noting that the attribute is changed from 2011 to 2019?
Sorry Grant, I think I confused you, I will try once again. Base number is having some attribute, and those attribute must be matched with detailnumber attribute, and any mismatch in any of the detailnumber attribute for a given base number attribute must be appear in result. the base numbers should be used just to get the detailedpart numbers, the part numbers can't be used in comparing the other attribute.
15 People are following this question.
