-1

I've already got 2 select statements that create temp tables for Part A and Part B.

  • Temp tables
Part A Location
10001 A
10001 B
10001 C
Part B Location
10001 Z
10001 Y

I want it then to be able to join distinct matches of Part A with Part B, such that there's no duplication of the possible Locations.

  • Current results (I don't want every combination):
Part A Location Part B Location
10001 A 10001 Z
10001 A 10001 Y
10001 B 10001 Z
10001 B 10001 Y
10001 C 10001 Z
10001 C 10001 Y
  • Expected results (only one match for each Part/Location):

Which ones match up in this case do not matter.

Part A Location Part B Location
10001 A 10001 Z
10001 B 10001 Y

OR

Part A Location Part B Location
10001 A 10001 Y
10001 B 10001 Z

OR

Part A Location Part B Location
10001 A 10001 Z
10001 C 10001 Y

OR

Part A Location Part B Location
10001 A 10001 Y
10001 C 10001 Z

OR

Part A Location Part B Location
10001 B 10001 Z
10001 C 10001 Y

OR

Part A Location Part B Location
10001 B 10001 Y
10001 C 10001 Z

Current query with actual data. I'd like to have it match the two highlighted lines on left, with ANY two of the highlighted lines on the right. They will match because the 'WO' for both is '10538989'. We know the two on the left are distinct because they have different locations, and the three on the right are distinct because they have different barcode numbers.

Current query with actual data

5
  • MySQL or SQL server and use simply a cross join
    – nbk
    Commented May 23 at 10:45
  • @nbk you know that cross join will duplicate the rows , right ? Which is the contrary what OP is asking Commented May 23 at 10:58
  • why is location C not included in the desired results Commented May 23 at 11:46
  • Because there's only 2 available Part Bs to match. I'll edit the 'Expected Results' with more 'OR' options, because it could be matched I guess.
    – AJ Sadler
    Commented May 23 at 12:11
  • Since everything has "10001", get rid of that column for your Question, it is distracting.
    – Rick James
    Commented May 24 at 21:35

1 Answer 1

1

Enumerate rows (in CTEs) then join by both table column and row number.

WITH 
cte1 AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY {...}) rn FROM table1
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY {...}) rn FROM table2
)
SELECT * 
FROM cte1
JOIN cte2 ON t1.column1 = t2.column2 AND t1.rn = t2.rn

Output rows amount will be equal to those in smaller table.

10
  • Sorry, it's not quite working as I would like. I've put an image of the SQL query using this enumeration in the original post. (Apologies, not quite sure how to get it embedded within the comment)
    – AJ Sadler
    Commented May 23 at 13:54
  • @AJSadler Please look carefully at the joining condition! Where is the condition 'Door WO' = 'Frame WO'?
    – Akina
    Commented May 23 at 14:49
  • Yes, I did that originally, but doing "ON a.ROWNUM = b.ROWNUM and a.[Door WO] = b.[Frame WO]" returns zero results because the matches that I want to make are on different rows. I'm not sure how the ROW_NUMBER() is going to help, because I don't want arbitrary matches.
    – AJ Sadler
    Commented May 23 at 15:27
  • @AJSadler Add according PARTITION BY clause into the window definition.
    – Akina
    Commented May 23 at 16:20
  • I've had a look at the PARTITION BY clause and it sort of makes sense what it does, but I can't figure out how to get it to fit into the statement that I currently have.
    – AJ Sadler
    Commented May 24 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.