Too many results.

[Sogeking]

Hi,

I got a problem retrieving data from multiple tables.
My sql looks like this:

SELECT r.[Password], s.[Name]
FROM Publications p, Surveys s, Recipients r
WHERE
EXISTS(
SELECT * FROM Surveys WHERE s.[Name] LIKE 'Enkät 2008 Skolor%'
)
AND p.[Name] LIKE 'Publikation 2008%'

Here is how my tables look:

[dbo].[Surveys]
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL

[dbo].[Publications]
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[SurveyId] [int] NOT NULL

[dbo].[Recipients]
[Id] [int] IDENTITY(1,1) NOT NULL,
[Password] [varchar](20) NOT NULL,
[PublicationId] [int] NOT NULL

As my SQL is now I get 512 rows back and I want to get only 64.
I want to only get all password and the names of the surveys associated with that password.
Now I get one password associated with every survey.
I have no clue what I'm doing wrong?

I guess my explanation is pretty confusing right now so ask if there is something more you need to know!



Cheers,
Sogeking

[n0vembr]

SELECT r.[Password], s.[Name]
FROM Publications p, Surveys s, Recipients r
WHERE
r.[PublicationId] = p.[PublicationId]
AND p.[SurveyId] = s.[Id]
AND p.[Name] LIKE 'Publikation 2008%'
AND s.[Name] LIKE 'Enkät 2008 Skolor%'

This line

EXISTS(
SELECT * FROM Surveys WHERE s.[Name] LIKE 'Enkät 2008 Skolor%'
)

isn't relevant as its condition is still referring to the outer query.

[Sogeking]

Thanks a lot! Guess I need to practice a lot more with my SQL skills after using NHibernate for some years.

[r937]

write your joins this way --

SELECT r.[Password]
     , s.[Name]
  FROM Surveys AS s
INNER
  JOIN Publications AS p
    ON p.[SurveyId] = s.[Id]
   AND p.[Name] LIKE 'Publikation 2008%'
INNER
  JOIN Recipients AS r
    ON r.[PublicationId] = p.[PublicationId]
 WHERE s.[Name] LIKE 'Enkät 2008 Skolor%'

this way, you are always reminded to write the ON clause, and cannot easily forget your join conditions like you can with the old-style comma-delimited "WHERE" joins