0

Table 1 - Static Data

ID 1 | Color 1 | Status 1 | ID 2

____|_______|_______|______

5555 | Blue | New | 7777

5555 | Blue | New | 3333

5555 | White | New | 7777

5555 | Gray | New | 7777

Table 2 - My Data

ID 2 | Color 2 | Status 2 | ID 1

___ |_______|_______|______

7777 | White | New | 5555

7777 | Gray | Old | 5555

Table 3: - Output

ID 1 | ID 2 | Color 1 | Color 2 | Status 1 | Status 2

____|____|______|________|_______|_______|

I basically want table 3 to tell me I missed Blue for ID2 7777, as well as the other colors that match.

I've been using code like

    from table 1
    Inner Join table 2 on t1.ID1 = t2.ID1 and t1.Color1 = t2.Color 2

Notes, table 1 will always have more data than table 2.

Simple joins dont seem to produce the results I want.

I tried inner on ID and Color, but it doesn't output everything. it wont say I missed blue

if I join on just the ID, the status will be in correct.

I tried Outer join, but it seems to output the wrong status.

e.g. it will out put (Color 1 - Color 2) Blue - Gray, Blue - White, Blue - Blue and return the wrong status for color 2

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
RSqI93
  • 55
  • 2
  • 8
  • 1
    Could you show us your expect result? – D-Shih Aug 03 '18 at 21:17
  • I was in the process of posting an answer for this when the question was closed. Here's the short version: It sounds like you want a LEFT OUTER JOIN to me. Instead of an INNER JOIN where you call for the rows that are common between the two tables, you call for the rows that only exist in the left table (table 1, in this scenario), but do not exist in the right table (Table 2). Look into that further and see if you can piece a query together – PausePause Aug 03 '18 at 21:32
  • What do you mean be wrong status? If status should match simply add it to the join clause as you do for colors and change INNER JOIN to LEFT JOIN – Livius Aug 03 '18 at 21:35

0 Answers0