SQL subquery on same table

Question

I think what I am after is fairly elementary, but I haven't been able to get the expected results through my experiments so far.

I am operating on one table, where the values from one known row determine which other rows from the same table are selected.

To illustrate what I want in two queries:

SELECT id, gender, sport, age
    FROM 
        person 
    WHERE 
       id = '123';

-- using id, gender, sport and age as inputs into the next query
SELECT id, age, sport, gender 
    FROM 
        person 
    WHERE 
       id != $1 AND looking_for_gender = $2 AND looking_for_sport = $3 AND min_age <= $4;

I've been trying different varieties of joins and subqueries in the from clause but not being able to get a satisfactory result to get the same from a single query.

Thanks in advance for any direction.

Gordon Linoff · Accepted Answer · 2014-02-13 04:28:51Z

2

You can do this with a join:

SELECT p.id, p.age, p.sport, p.gender 
FROM person p cross join
     (select * from person where id  = 123
     ) p1
WHERE p.id <> p1.id AND p.looking_for_gender = p1.looking_for_gender AND
      p.looking_for_sport = p1.looking_for_sport AND p.min_age <= p1.min_age;

answered Feb 13, 2014 at 4:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

markdsievers Over a year ago

Thanks, the subquery is what got me on the right track.

Collectives™ on Stack Overflow

SQL subquery on same table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related