joining tables without duplicate rows / NULL values

Question

so I have multiple tables which I have to merge when query but both join and union doesn't seems to achieve what my object is

Union:

mysql> SELECT NAME, currentstatus, NULL AS username, NULL AS status FROM statustable WHERE Name like '%ahsan%' UNION ALL SELECT NULL AS NAME, NULL AS currentstatus, username, status from awsaccount WHERE username like '%ahsan%';
+--------------+-------------------+----------+--------------------------------+
| NAME         | currentstatus     | status   | username                         |
+--------------+-------------------+----------+--------------------------------+
| Ahsan Naseem | activated         | NULL     | NULL                           |
| NULL         | NULL              | ACTIVE   | ahsan.naseem       |
| NULL         | NULL              | INACTIVE | ahsantest                      |
| NULL         | NULL              | ACTIVE   | ahsantest2                     |
| NULL         | NULL              | ACTIVE   | ahsantestuserprofileupdate     |
| NULL         | NULL              | INACTIVE | ahsantestuserprofileupdate_cli |
| NULL         | NULL              | INACTIVE | decent.ahsan         |
| NULL         | NULL              | INACTIVE | decent.ahsan_cli               |
+--------------+-------------------+----------+--------------------------------+
8 rows in set (0.00 sec)

Join:

mysql> select * from (select * from statustable where Name like '%ahsan%') as ab   JOIN  (select * from awsaccount where  username like '%ahsan%') as asb;                                                                       
    +--------------+-------------------+--------------------------------+----------+
    | Name         | currentstatus | username                       | status   |
    +--------------+-------------------+--------------------------------+----------+
    | Ahsan Naseem | activated         | ahsan.naseem                    | ACTIVE   |
    | Ahsan Naseem | activated         | ahsantest                      | INACTIVE |
    | Ahsan Naseem | activated         | ahsantest2                     | ACTIVE   |
    | Ahsan Naseem | activated         | ahsantestuserprofileupdate     | ACTIVE   |
    | Ahsan Naseem | activated         | ahsantestuserprofileupdate_cli | INACTIVE |
    | Ahsan Naseem | activated         | decent.ahsan         | INACTIVE |
    | Ahsan Naseem | activated         | decent.ahsan_cli               | INACTIVE |
    +--------------+-------------------+--------------------------------+----------+
    7 rows in set (0.00 sec)

What I am trying to achieve is :

+--------------+-------------------+----------+--------------------------------+
| NAME         | currentstatus    | status   | username                       |
+--------------+-------------------+----------+--------------------------------+
| Ahsan Naseem | Active            | ACTIVE   | ahsan.naseem        |
| NULL         | NULL              | INACTIVE | ahsantest                      |
| NULL         | NULL              | ACTIVE   | ahsantest2                     |
| NULL         | NULL              | ACTIVE   | ahsantestuserprofileupdate     |
| NULL         | NULL              | INACTIVE | ahsantestuserprofileupdate_cli |
| NULL         | NULL              | INACTIVE | decent.ahsan        |
| NULL         | NULL              | INACTIVE | decent.ahsan_cli               |
+--------------+-------------------+----------+--------------------------------+

Is this really possible? I am new to sql and will be grateful for your advice and suggestion

Answer 1

Please provide table definitions and sample data in future, it makes it much more likely your question will get a useful answer quickly. Below is the data I've made up for these purposes with a few changes to allow me to answer your question:

DROP TABLE IF EXISTS statustable;
CREATE TABLE statustable(`NAME` VARCHAR(32), currentStatus VARCHAR(32));

INSERT INTO statustable VALUES('Ahsan Naseem', 'activated');

DROP TABLE IF EXISTS awsaccount;
CREATE TABLE awsaccount(`NAME` VARCHAR(32), username VARCHAR(32), `status` VARCHAR(32), logEntry DATETIME);

INSERT INTO awsaccount VALUES('Ahsan Naseem', 'ACTIVE', 'ahsan.naseem', '2000-01-07'),
    ('Ahsan Naseem', 'INACTIVE', 'ahsantest', '2000-01-06'),
    ('Ahsan Naseem', 'ACTIVE', 'ahsantest2', '2000-01-05'),
    ('Ahsan Naseem', 'ACTIVE', 'ahsantestuserprofileupdate', '2000-01-04'),
    ('Ahsan Naseem', 'INACTIVE', 'ahsantestuserprofileupdate_cli', '2000-01-03'),
    ('Ahsan Naseem', 'INACTIVE', 'decent.ahsan', '2000-01-02'),
    ('Ahsan Naseem', 'INACTIVE', 'decent.ahsan_cli', '2000-01-01');

As far as I can tell from your sample queries there are two issues here, firstly there doesn't appear to be a common key in both tables, this is a value that is in common with rows to be matched (joined / JOIN) in both tables and is the same for rows that relate to each other. (Incidentally these should also be of the same data type or queries can take a long time to execute). I added a NAME column to the awsaccount table to make a JOIN possible in this context. From this the results are as so:

SELECT A.`NAME`, A.currentStatus, B.username, B.`status` FROM
  statusTable A 
RIGHT OUTER JOIN 
  awsaccount B 
ON A.`NAME` = B.`NAME`;

+--------------+---------------+----------+--------------------------------+
| NAME         | currentStatus | username | status                         |
+--------------+---------------+----------+--------------------------------+
| Ahsan Naseem | activated     | ACTIVE   | ahsan.naseem                   |
| Ahsan Naseem | activated     | INACTIVE | ahsantest                      |
| Ahsan Naseem | activated     | ACTIVE   | ahsantest2                     |
| Ahsan Naseem | activated     | ACTIVE   | ahsantestuserprofileupdate     |
| Ahsan Naseem | activated     | INACTIVE | ahsantestuserprofileupdate_cli |
| Ahsan Naseem | activated     | INACTIVE | decent.ahsan                   |
| Ahsan Naseem | activated     | INACTIVE | decent.ahsan_cli               |
+--------------+---------------+----------+--------------------------------+
7 rows in set (0.00 sec)

Which is closer to what you require but not quite there, as the NAME key is common to all entries in both tables. To produce an output as in your question a few more steps are necessary and I've made the following assumptions:

• That the awsaccout table is some sort of history of the users previous accout statues
• To this end I've added a DATE field which can be used to control the order of entries into this table

This means we can JOIN on the NAME field, using a subquery (table C) in the ON clause to only return a match from the statusTable in the case the entry is the latest. As the data in your example inclused the NULLcolumns on the LEFT, I've used a RIGHT OUTER JOIN here which is one of the less common JOINSencountered in the code I come across. Basically it says return all the data from the second table (B) and any matches from the first (A), otherwise these columns are NULL

Here are the results:

SELECT A.`NAME`, A.currentStatus, B.username, B.`status` FROM 
  statusTable A 
RIGHT OUTER JOIN 
  awsaccount B 
ON A.`NAME` = B.`NAME` AND B.logEntry = (SELECT MAX(logEntry) FROM awsaccount WHERE `NAME` = A.`Name`);

+--------------+---------------+----------+--------------------------------+
| NAME         | currentStatus | username | status                         |
+--------------+---------------+----------+--------------------------------+
| Ahsan Naseem | activated     | ACTIVE   | ahsan.naseem                   |
| NULL         | NULL          | INACTIVE | ahsantest                      |
| NULL         | NULL          | ACTIVE   | ahsantest2                     |
| NULL         | NULL          | ACTIVE   | ahsantestuserprofileupdate     |
| NULL         | NULL          | INACTIVE | ahsantestuserprofileupdate_cli |
| NULL         | NULL          | INACTIVE | decent.ahsan                   |
| NULL         | NULL          | INACTIVE | decent.ahsan_cli               |
+--------------+---------------+----------+--------------------------------+
7 rows in set (0.00 sec)

As you mentioned your new to SQL, here is an image that explains the different kinds of JOINs visually (taken from here).

Regards,

James