SQL join with the same table with different select

Question

I have a table tablename with columns col1-col10. Not every row has col4 populated, but every row has col1, col2, col3 populated. I want to get all {col1, col2, col3} tuples for when col4 satisfies a condition, then get all rows that match the tuple {col1, col2, col3} from tablename.

I am not sure if I should be using inner join or left join or something else? ( I think both inner and left join should give me same result) The query below gives me a syntax error "mismatched input AS". What is the correct way to write this query?

select col1, col2, col3
from tablename 
where col4 >= 1000 AS A
INNER JOIN
(select *
FROM tablename) AS B
ON A.col1 = B.col1 AND A.col2 = B.col2 A.col3 = B.col3

GMB · Accepted Answer · 2020-08-20 00:34:53Z

2

You can use exists:

select t.*
from mytable t
where exists (
    select 1
    from mytable t1
    where 
        t1.col1 = t.col1 
        and t1.col2 = t.col2 
        and t1.col3 = t.col3 
        and t1.col4 >= 1000
)

answered Aug 20, 2020 at 0:34

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1745995 Over a year ago

what is the meaning of select 1 in this query?

GMB Over a year ago

@user1745995: exists just checks if the subquery returns some row (no matter the content), so we don't need it to return something - hence select 1. It is just a convention, you would use select * or select 0, it would not change the results.

user1745995 Over a year ago

Is this the join equivalent :

Collectives™ on Stack Overflow

SQL join with the same table with different select

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related