CODEWITHSUNDEEP
mysqljoin
Ulquiorra Schiffer
Ulquiorra Schiffer

Reputation: 219

Query that displays the data if they have the same data from table 1 to table 2

My target is to display the first name and last name with the same data on my target output. I have provided a table below for your reference.

Table 1: tbl_user

first_name last_name
roger paul
law marshall
steve fox
nina williams
sakura flower

Table 2: tbl_account

first_name last_name
roger paul
law marshall
steve fox
nina kicks
sakura hana

My target output:

first_name last_name
roger paul
law marshall
steve fox

As we can see on the target output, there is no "Nina williams" and "sakura flower". It happens because, on the first table and the second table, their first name and last name didn't match.

Any idea on how I can achieve my target? Thank you very much.

enter image description here

Upvotes: 0

Views: 29

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521639

A simple inner join should work here:

SELECT u.first_name, u.last_name
FROM tbl_user u
INNER JOIN tbl_account a
    ON a.first_name = u.first_name AND
       a.last_name = u.last_name;

Another way, using a union:

SELECT first_name, last_name
FROM
(
    SELECT first_name, last_name FROM tbl_user
    UNION ALL
    SELECT first_name, last_name FROM tbl_account
) t
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

Upvotes: 3

Related Questions