asked on

Query syntax

Hi,

I need help from EEs to get the the query. I have two tables.
Table A has ID that can link to Table B's ID.

I need to bring everything from Table A without duplication. As you can see in Table B, it contains everything from TableA
and some with duplications.

The logic is to bring everything from Table A and PID from Table B

The end result should look like this

ID PID
1545043 1-001
10018849 8-001
10038557 32
10032746
10555277 10-001
10010026 6-001
10039886 58-5
10037757
10734379 85

If there is no duplication in Table B, get the PID; if there is duplication in Table B, get the one reads "001"

thanks
test.xlsx

ASKER CERTIFIED SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

>>"As you can see in Table B, it contains everything from TableA and some with duplications. "

The term duplicate in SQL applies to the whole row, so I do not see any "duplications" in your tableB.

There is information in TableB that does not exist in TableA:

+----------+-----------+--------+
|  tableA  |        tableB      |
|    ID    |    ID     |  PID   |
+----------+-----------+--------+
|  1545043 |   1545043 | 1-001  |
|          |   1545043 | 1-002  | << this is NOT a duplicate 
| 10010026 |  10010026 | 6-001  |
|          |  10010026 | 6-002  |<< this is NOT a duplicate 
| 10018849 |  10018849 | 8-001  |
|          |  10018849 | 8-002  |<< this is NOT a duplicate 
|          |  10018849 | 8-003  |<< this is NOT a duplicate 
|          |  10018849 | 8-004  |<< this is NOT a duplicate 
|          |  10018849 | 8-005  |<< this is NOT a duplicate 
| 10032746 |  10032746 |        |
| 10037757 |  10037757 |        |
| 10038557 |  10038557 | 32     |
| 10039886 |  10039886 | 58-5   |
|          |  10256263 |        |
| 10555277 |  10555277 | 10-001 |
|          |  10555277 | 10-002 |
| 10734379 |  10734379 | 85     |
|          |  12015846 | 54     |
|          | 896413254 | 9-001  |
|          | 896413254 | 9-002  |<< this is NOT a duplicate 
+----------+-----------+--------+

Open in new window

but an INNER JOIN will ignore any rows where the IDs are unmatched

inner join tableA A  = tableB  B on A.ID = B.ID
|    ID    |    ID    |  PID   |  
+----------+----------+--------+
|  1545043 |  1545043 | 1-001  | 
|  1545043 |  1545043 | 1-002  |
| 10010026 | 10010026 | 6-001  | 
| 10010026 | 10010026 | 6-002  | 
| 10018849 | 10018849 | 8-001  |
| 10018849 | 10018849 | 8-002  | 
| 10018849 | 10018849 | 8-003  |
| 10018849 | 10018849 | 8-004  | 
| 10018849 | 10018849 | 8-005  | 
| 10032746 | 10032746 |        |
| 10037757 | 10037757 |        | 
| 10038557 | 10038557 | 32     |
| 10039886 | 10039886 | 58-5   |
| 10555277 | 10555277 | 10-001 |
| 10555277 | 10555277 | 10-002 |
| 10734379 | 10734379 | 85     |
+----------+----------+--------+-------+

Open in new window

and as johnsone identifies using minimum will produce this result:

|    ID    |    ID    |  PID   |       |
+----------+----------+--------+-------+
|  1545043 |  1545043 | 1-001  | <<min |
| 10010026 | 10010026 | 6-001  | <<min |
| 10018849 | 10018849 | 8-001  | <<min |
| 10032746 | 10032746 |        | <<min |
| 10037757 | 10037757 |        | <<min |
| 10038557 | 10038557 | 32     | <<min |
| 10039886 | 10039886 | 58-5   | <<min |
| 10555277 | 10555277 | 10-001 | <<min |
| 10734379 | 10734379 | 85     | <<min |
+----------+----------+--------+-------+

Open in new window

johnsone

I meant to note this in my original post.

There is no way given the data provided to determine if an outer join is needed or not. The wording of the question says it would be needed, but the data isn't conclusive. I left it as inner join because I don't like to use an outer join if it isn't needed.

PortletPaul

@johnsone
I agree, it wasn't initially clear to me either, but implied I think through the expected result. So, I believe you chose correctly.

mcrmg

ASKER

thank you very much