Convert Subquery to Self Join Convert Subquery to Self Join sqlite sqlite

Convert Subquery to Self Join


sql sqlite subquery self-join

The result you are looking for using a self join is:

SELECT DISTINCT t1.*FROM ma t1JOIN ma t2  ON  t1.SCHEDULED_ID <> t2.SCHEDULED_ID --Satisfies 2nd queryWHERE t2.ACTION_ID = 3 --Satisfies 2nd query    OR  t1.ACTION_ID = 3 --Satisfies 1st queryORDER BY t1.ID

sql sqlite subquery self-join

I don’t think a JOIN is really what you need here. I would use the following query, which avoids UNION :

SELECT m.* FROM ma mWHERE     m.action_id = 3    OR NOT EXISTS (        SELECT 1        FROM ma m1        WHERE             m1.scheduled_id = m.scheduled_id            AND m1.action_id = 3    )

When it comes to checking for the existence (or absence) of something, NOT EXISTS with a correlated subquery is usually the most relevant and efficient approach.

sql sqlite subquery self-join 

SELECT m1.* FROM ma m1INNER JOIN(    SELECT *     FROM ma m2     WHERE m2.action_id = 3) AS matbl WHERE m1.action_id = 3 OR matbl.scheduled_id<>m1.scheduled_id

Hope it will help.

Recent Posts
How can I color dots in a xy scatterplot according to column value?
How to update a claim in ASP.NET Identity?
What does {0} mean when initializing an object?
Accessing members of items in a JSONArray with Java
How to log SQL statements in Spring Boot?
Powershell Get-WebSite name parameter is ignored
How to detect scroll to bottom of html element
Java synchronized method
How to test controllers with CodeIgniter?
Detect Visual Composer
Matplotlib: Specify format of floats for tick labels
Rails join a list of strings with commas and "and" before the last