Question 20282 in Mysql Question posted in
The official MySQL documentation can be found here.

Removing duplicate rows from different table in SQL – Mysql

I have 2 table and I would like to get only unique rows from one those tables.

On the following example, I need the list of application_id which have no duplicate name.

Table appName

app_id app_name
APP001 a
APP002 b
APP003 c
APP004 d
APP005 f

Table tbl_application_detail

app_id em_id
APP002 2424
APP003 2424

Table that I expectation

app_id app_name
APP001 a
APP004 d
APP005 f

I am new SQL I don’t know how make this code

SELECT application_id,application_name FROM appName 


SELECT application_id,em_id FROM tbl_application_detail WHERE em_id=2424
Tags:

2

Answers


  1. 0

    On MySQL 8+, we can try using the following left anti-join approach:

    WITH cte AS (
    SELECT *, COUNT(*) OVER (PARTITION BY em_id) cnt
    FROM tbl_application_detail
)

SELECT t1.app_id, t1.app_name
FROM appName t1
LEFT JOIN cte t2
    ON t2.app_id = t1.app_id
WHERE COALESCE(t2.cnt, 0) <= 1;

    screen capture from demo link below

    Demo

    Login or Signup to reply.
  2. 0

    You can try with Sub query (Mysql & SQL Sever) as well..

    select * from appName where app_id not in (select app_id from tbl_application_detail)

    or
    Joins (SQL Sever)

    select apn.* from appName apn full outer join tbl_application_detail tad 
on tad.app_id = apn.app_id 
where apn.app_id is null or tad.app_id is null

    Result

    result

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search