CODEWITHSUNDEEP
mysqlsql
user3324467
user3324467

Reputation: 19

Left join and not exists

I have a scenario in which I have 5 tables with different columns.

If I join Table1 and table2 I get result A and joining table3, table4, table5 I get result B.

I need to compare result A with result B and display the results A which are not in result B

I tried this:

 SELECT X* 
    FROM 
    (SELECT .. 
     FROM table1
      JOIN TABLE 2)X
  LEFT JOIN 
     (SELECT ...
          FROM TABLE 3
           JOIN 4 
           JOIN 5) Y ON x.=y.
 WHERE x.Z=''

But this does not give me the desired result. I tried using not exists and I cannot do a not exists.

Can I get any assistance for this.

I tried the suggested query with the following modifications but i am still unable to make up to my requirement

> select x.column1,
         x.column2,
         x.column3,
         y.column4
>       from 
>     (select table1.column1 column1,table1.column2 column2,table2.column1 column3
>      from table1
>       join table 2)X   left join 
>      (select table3.column1 column4,table3.column2 column5,table4.column1 column1 6
>           from table 3
>            join 4 
>                 ) y on x.column1=y.column4  where y.COLUMN4 IS NULL

As per my requirement i need to show only those results from X that do not exist in Y.

But the current query above works as follows , displays all the results from X even though there are 0 results in Y.

I am not sure where i am going wrong.

Upvotes: 0

Views: 2270

Answers (1)

Avt
Avt

Reputation: 17043

General approach to exclude B from A is following

SELECT * FROM A LEFT JOIN B on A.ID = B.ID WHERE B.ID IS NULL

In your case it probably should be 

select x* 
    from 
    (select .. 
     from table1
      join table 2)X
  left join 
     (select ...
          from table 3
           join 4 
           join 5) y on x.COLUMN=y.COLUMN
 where x.Z='' and y.COLUMN IS NULL

UPDATE: General approach get all rows from A if some condition is true is:

SELECT * FROM A WHERE your_condition

In your case it probably should be 

select x* 
    from 
    (select .. 
     from table1
      join table 2)X
  left join 
     (select ...
          from table 3
           join 4 
           join 5) y on x.COLUMN=y.COLUMN
 where x.Z='' and y.COLUMN IS NULL
and (select count(*) from Y) > 0

Upvotes: 5

Related Questions