Search code examples
mysqlsqljoininner-joindistinct

Add a column based on the query value for the distinct with INNER JOIN query

I have these two tables

TABLEA

   ID             SQLUID                       GNO  etype   someoID   prevVal        TBUID
    1   637F284D-EA28-406D-84A2-5367972A32FA    33    1        7       3        F3F3875C-4E34-4FA1-83DE-AA087495BDE8    
    2   F3F3875C-4E34-4FA1-83DE-AA087495BDE8    33    1        7       3        F3F3875C-4E34-4FA1-83DE-AA087495BDE8

TABLEB

ID   CID GNO   STANCE someoID  AadharNO   PanID   VoterID timestamp      EndTime    Name  Status       TBGUID
7    5   33     1      7        0          7       7      1640286493    1640286496  Hari   8     48E587DD-9835-438F-95F7-E254803E5702
8    5   33     1      7        0          7       7      1640286694    1640286697  Prasad 0     637F284D-EA28-406D-84A2-5367972A32FA
10   5   33     1      7        0          1       1      1640325568    1640325588  Verma  4     B1895540-2806-463E-A651-C3785ECBC18D

Now I am doing a INNER JOIN on these two tables which gives me

SELECT TABLEA.TBUID, 
       TABLEA.someoID, 
       TABLEB.GNO, 
       TABLEA.SQLUID, 
       TABLEB.TBGUID, 
       TABLEA.etype 
FROM TABLEA 
INNER JOIN TABLEB ON TABLEA.someoID = TABLEB.someoID

RESULT

    TABLEA.TBUID                 TABLEA.someoID     TABLEB.GNO     TABLEA.SQLUID                    TABLEB.TBGUID                        TABLEA.etype
F3F3875C-4E34-4FA1-83DE-AA087495BDE8    7            33       637F284D-EA28-406D-84A2-5367972A32FA  637F284D-EA28-406D-84A2-5367972A32FA    1
F3F3875C-4E34-4FA1-83DE-AA087495BDE8    7            33       637F284D-EA28-406D-84A2-5367972A32FA  B1895540-2806-463E-A651-C3785ECBC18D    1
F3F3875C-4E34-4FA1-83DE-AA087495BDE8    7            33       637F284D-EA28-406D-84A2-5367972A32FA  48E587DD-9835-438F-95F7-E254803E5702    1
F3F3875C-4E34-4FA1-83DE-AA087495BDE8    7            33       F3F3875C-4E34-4FA1-83DE-AA087495BDE8  637F284D-EA28-406D-84A2-5367972A32FA    1
F3F3875C-4E34-4FA1-83DE-AA087495BDE8    7            33       F3F3875C-4E34-4FA1-83DE-AA087495BDE8  B1895540-2806-463E-A651-C3785ECBC18D    1
F3F3875C-4E34-4FA1-83DE-AA087495BDE8    7            33       F3F3875C-4E34-4FA1-83DE-AA087495BDE8  48E587DD-9835-438F-95F7-E254803E5702    1

Now what I need is DSITINCT ON TABLEA.TBUID,TABLEA.someoID,TABLEB.GNO,TABLEA.SQLUID, TABLEA.etype and the status value present in TABLEB for TABLEA.SQLUID

So my result would be

TABLEA.TBUID                     TABLEA.someoID     TABLEB.GNO     TABLEA.SQLUID                    TABLEB.status TABLEA.etype
F3F3875C-4E34-4FA1-83DE-AA087495BDE8    7               33       637F284D-EA28-406D-84A2-5367972A32FA   0           1
F3F3875C-4E34-4FA1-83DE-AA087495BDE8    7               33       F3F3875C-4E34-4FA1-83DE-AA087495BDE8   NULL        1

As you can see the status for F3F3875C-4E34-4FA1-83DE-AA087495BDE8 is NULL as this value is not present in TABLEB.TBGUID

I tried using this way, but this does not works for me

SELECT DISTINCT TABLEA.TBUID,
                TABLEA.someoID,
                TABLEB.GNO,
                TABLEA.SQLUID,
                TABLEB.status,
                TABLEA.etype 
FROM TABLEA 
INNER JOIN TABLE ON TABLEA.someoID = TABLEB.someoID
Solution

  • You can have as many conditions in the on statement as you need and a left join is more appropriate

    SELECT TABLEA.TBUID, 
       TABLEA.someoID, 
       TABLEB.GNO, 
       TABLEA.SQLUID, 
       tableb.status,
         TABLEA.etype 
FROM TABLEA 
left JOIN TABLEB ON TABLEA.someoID = TABLEB.someoID and tbguid = sqluid;

+--------------------------------------+---------+------+--------------------------------------+--------+-------+
| TBUID                                | someoID | GNO  | SQLUID                               | status | etype |
+--------------------------------------+---------+------+--------------------------------------+--------+-------+
| F3F3875C-4E34-4FA1-83DE-AA087495BDE8 |       7 |   33 | 637F284D-EA28-406D-84A2-5367972A32FA |      0 |     1 |
| F3F3875C-4E34-4FA1-83DE-AA087495BDE8 |       7 | NULL | F3F3875C-4E34-4FA1-83DE-AA087495BDE8 |   NULL |     1 |
+--------------------------------------+---------+------+--------------------------------------+--------+-------+
2 rows in set (0.001 sec)

    Note gno has to be null for the same reason that status is null