Clever Geek Handbook

Replacement / Alternative for UNION with a single JOIN Select Statement

I have a table called posts

and I want retrieve

everything records

that meets the conditions below in select statement

and returns a single table

-

select ID from posts where UserID= 23487 and postlevel <> 1
select ID from posts where ParentID in (select ID from posts where UserID= 23487 and postlevel <> 1)

Now, using the operator UNION

as shown below, I can return one table -

select ID from posts where UserID= 23487 and postlevel <> 1
UNION 
select ID from posts where ParentID in (select ID from posts where UserID= 23487 and postlevel <> 1)

Output

enter image description here

Tried below JOIN

query but didn't get expected result and returns NULL

-

select ID from posts cs
LEFT JOIN posts cs1 ON cs.ID = cs1.ID
where cs.UserID = 23487 and cs.PostLevel <>1 and cs.ParentID = cs1.ID

Expected

I want to get records using JOIN

OR using one SELECT

, not UNION

to get the desired result as shown above.

+3


source to share


3 answers


Have you tried simply ORing both conditions together in one sentence WHERE

:



select ID
from posts
where
    (UserID = 23487 and postlevel <> 1) or
    (ParentID in (select ID from cs_posts where UserID= 23487 and postlevel <> 1))
+4


source


You can try this



select ID
from posts
where
    (UserID = 23487 and postlevel <> 1) or
    (EXISTS (select 1 from cs_posts where UserID= 23487 and postlevel <> 1 and ID = ParentID) )
+1


source


No union required, or exists or exists .... Try this:

select distinct F1.id from post F1 inner join
 posts f2 on f2.UserID= 23487 and f2.postlevel <> 1 AND 
(F1.id=f2.id or F1.parentid=f2.id)

If you want to keep duplicate values, remove the individual

0


source






More articles:


All Articles