SQL get Distinct rows from joined tables-CodePudding

I'm trying to determine the following from the joining of two tables:

Sample TableA: Contains 1 column consisting of unique IDs

ID    Name    Department
1     John    IT
2     Jason   Sales
3     Dany    IT
4     Mike    HR
5     Alex    HR

Sample TableB: Contains multiple columns, including ID from TableA. For example:

ID    AccountNumber    WebID
1     10725            ABC1
1     10726            ABC1
1     10727            ABC1
2     20100            ABC2
2     20101            ABC2
3     30100            ABC3
4     40100            NULL

I want to get the following results:

 ID   Name    WebID
 1    John    ABC1
 2    Jason   ABC2
 3    Dany    ABC3
 4    Mike    NULL
 5    Alex    NULL

I tried the following query, which is returning the correct rows for these sample tables:

Select count(a.ID), a.ID, a.Name, b.WebID from TableA a
left join TableB b on a.ID = b.ID
group by a.ID, a.Name, b.WebID

But my Actual Database tables, this query does not return correct number of rows: (30992)

TableA contains 29066 rows and TableB contains 23033 rows

The query should return 29066 rows, as it is Left Join.

When I checked the IDs that are in TableA, but not in TableB, there were 6033 rows:

Select * from TableA where ID not in (Select ID from TableB)

Am I missing something in the query?

CodePudding user response：

TABLE B has duplicates of the ID column... the code below should work (but might not be the results you expect since I just do a max on the webid column which is fine if it is always the same but I need a rule if not)

I just saw you had a count... I added that in.

   SELECT *
   FROM TABLEA A
   LEFT JOIN (
      SELECT ID, MAX(WebID) AS WebID, count(*) as CNT
      FROM TABLEB
      GROUP BY ID
   ) B ON A.ID = B.ID

CodePudding user response：

I think your query is as simple as that:

 select a.ID,a.Name,b.WebID 
 from TableA a 
 left join TableB b on a.ID = b.ID