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

Join 2 tables with Sub Query and Union in MySQL

I have table 1 and table 2, where Table 1 is master table and Table 2 is child table. Relationship of both table has been implemented by primary key of table 1(id) with table_1_id column in table 2.

I need to fetch data from table 1 by check data in table with below conditions in single query:

If table 1 data does not exist in child table 2

  • fetch data from table 1

If table 1 data exist in table 2 and status=0 in table 2,

  • fetch data from table 1

If table 1 data exist in table 2 and status=1 in table 2,

  • Ignore data from table 1

Below is my SQL, but it always return same number of records and not considering 2nd and 3rd condition:

select t1.* from table_1 as t1
where t1.id IN(
SELECT t1.id
            from table_1 as t1
            LEFT JOIN table_2 AS t2 ON t2.table_1_id=t1.id
            UNION ALL
            SELECT table_1_id
            from table_2 as t2 where t2.deleted = "0"'
)

I have tried EXIST in where clause instead of t1.id IN in above query but no luck, Can someone guide me what is wrong in this query and how can correct my query to achieve records from table?

Thank you.

Tags:

2

Answers


  1. 0

    I think a simple (anti) JOIN should do what you need:

    SELECT t1.*
FROM table_1 AS t1
LEFT JOIN table_2 AS t2 ON 
    t1.id = t2.table_1_id
    AND t2.status = 1
WHERE t2.table_1_id IS NULL

    For each row in table_1, we look for a matching row in table_2 that also has status = 1. The WHERE clause ensures we exclude any such rows – ie table_1 rows are only included if there is no matching row in table_2 (condition 1), or if the matching row has some status that’s different to 1 (conditions 2 and 3).

    Login or Signup to reply.
  2. 0

    You are using the word "exists" a lot in your description. Use it in your query as well. Maybe you made the task more complicated than it actually is, in your head. 🙂

    SELECT *
FROM table_1 AS t1
WHERE NOT EXISTS
(
  SELECT NULL
  FROM table_2 AS t2
  WHERE t2.table_1_id = t1.id
  AND t2.status = 1
);

    I suppose that doesn’t require any explanation. The query simply says "give me all t1 rows for whch not exists a t2 row with status 1".

    It doesn’t really matter what we select in the subquery, as this is all about whether a row exists or not. Some use NULL like in my query, some use *, some use 1, and some people even use a string like ‘matching t2 row with status 1’ to get this as readable as possible. 🙂

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

Back To Top
Search