CODEWITHSUNDEEP
sqloracle-database
Arthur V
Arthur V

Reputation: 11

SQL questions - Medium difficulty

I am struggling on Q9 from this website: http://sqlzoo.net/a1m.htm

This is what I have put down at the moment even though I know it's not right

SELECT ID, 
       modle, 
       DOW, 
       TOD
  FROM event x
 WHERE modle = (SELECT modle
                  FROM event y
                 WHERE y.TOD = x.TOD)

Here is a link for the ER diagram: sqlzoo.net/a1.htm

What is the proper way to do this?

Upvotes: 1

Views: 161

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332691

The problem with this:

SELECT ID, modle, DOW, TOD 
  FROM event x 
 WHERE modle = (SELECT modle FROM event y WHERE y.TOD = x.TOD)

...is the subquery can return more than one row. For such situations, you want to use IN rather than equals:

SELECT ID, modle, DOW, TOD 
  FROM event x 
 WHERE modle IN (SELECT modle FROM event y WHERE y.TOD = x.TOD)

Otherwise, you'll want to use either an aggregate or filter criteria to ensure a single record is always returned from the subquery.

I prefer using ANSI-92 JOIN syntax:

SELECT x.*
  FROM EVENT x
  JOIN EVENT y ON y.tod = x.tod
              AND y.modle = 'co72010'

Using IN

SELECT x.*
  FROM EVENT x
 WHERE x.tod IN(SELECT y.tod 
                  FROM EVENT y
                 WHERE y.modle = 'co72010')

Using EXISTS

SELECT x.*
  FROM EVENT x
 WHERE EXISTS(SELECT NULL
                FROM EVENT y
               WHERE y.modle = 'co72010'
                 AND y.tod = x.tod)

Upvotes: 3

kmkaplan
kmkaplan

Reputation: 18960

You should try to use a join using twice the same table. Start with:


SELECT e2.id
FROM event AS e1, event AS e2
WHERE e1.modle = 'co72010'
AND …

I let you fill in the dots.

Upvotes: 0

Related Questions