Oracle sql query with multiple tables

Question

As I mentioned in another question, I am working on small relational database queries for school. This query is to find male users with female friends. Here is the relevant structure of the database

Table: Users
Attributes: Id_pk, Name, Gender

Table: Friends
Attributes: Id1_pk (fk to users.id), Id2_pk (fk to users.id),Startdate

I am attempting to solve this query using this query:

select distinct id from friends, users, where users.id = id1 and users.gender = 'M'
intersect
select id from friends, users where users.id = id2 and users.gender = 'F'

So this is a valid search but no rows are selected though I certainly have friends between males and females in the database. I think I am running into a problem where i am checking the same users.gender twice, but I am not sure how to solve it. My thinking in constructing this query was to try to check the gender of the person in slots id1 and id2, making sure the id1 is a male and id2 is a female.

Thank you

If I started my answer with "As I answered your question in my dream yesterday ... ", just saying.... — Chandu
– Chandu, Commented Oct 16, 2013 at 0:28
I saw that question yesterday... I am reading this thinking, haven't I read this already? — Scott Solmer
– Scott Solmer, Commented Oct 16, 2013 at 0:30

Chandu · Accepted Answer · 2013-10-16 00:43:43Z

2

Try this(assuming >=Oracle 9x):

 WITH FemalesQuery AS
(
    SELECT Id_pk
      FROM Users
     WHERE Gender = 'F'
), MalesQuery AS
(
    SELECT Id_pk
      FROM Users
     WHERE Gender = 'M'
)

SELECT Id2_pk
  FROM Friends
 WHERE Id1_pk IN (SELECT Id_pk FROM FemalesQuery)
   AND Id2_pk IN (SELECT Id_pk FROM MalesQuery)
 UNION
 SELECT Id1_pk
  FROM Friends
 WHERE Id2_pk IN (SELECT Id_pk FROM FemalesQuery)
   AND Id1_pk IN (SELECT Id_pk FROM MalesQuery)

edited Oct 16, 2013 at 0:43

answered Oct 16, 2013 at 0:37

Chandu

83.2k19 gold badges136 silver badges136 bronze badges

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

1 Comment

user1814946 Over a year ago

I solved it slightly differently but tested your method and it looks good too. I used: Select a.id from users a, users b, friends where id1 = a.id and a.gender = 'M' and id2 = b.id and b.gender = 'F';

Collectives™ on Stack Overflow

Oracle sql query with multiple tables

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