Cant get query right

[alaric]

Hi
Im struggling a (for me complex) query

SELECT DISTINCT 
               TAdresBoek.CursistID, TAdresBoek.AchterNaam + ', ' + TAdresBoek.VoorNaam + ISNULL(+ ' ' + TAdresBoek.TussenVoegsel, '') AS Naam, 
               TAdresBoek.EmailAdres, TWeekdag.Weekdag AS Weekdag, TInschrijving.Plezier, TInschrijving.OpNivo, TCursussen.Cursus AS Cursus, 
               TBetalingen.Betaald
FROM  TInschrijving INNER JOIN
               TWeekdag ON TInschrijving.Weekdag = TWeekdag.WeekDagid INNER JOIN
               TLocaties ON TInschrijving.Locatie = TLocaties.LocatieID INNER JOIN
               TCursussen ON TInschrijving.Cursus = TCursussen.CursusID INNER JOIN
               TBetalingen ON TInschrijving.InschrijvingsID = TBetalingen.Inschrijvingsid RIGHT OUTER JOIN
               TAdresBoek ON TInschrijving.CursistID = TAdresBoek.CursistID CROSS JOIN
               TDansSoort
WHERE (TAdresBoek.EmailAdres <> ' ') AND (TWeekdag.Weekdag = N'maandag') OR
               (TWeekdag.Weekdag = N'dinsdag') OR
               (TWeekdag.Weekdag = N'woensdag') OR
               (TWeekdag.Weekdag = N'donderdag') OR
               (TWeekdag.Weekdag = N'vrijdag') OR
               (TWeekdag.Weekdag = N'zaterdag') OR
               (TWeekdag.Weekdag = N'zondag')

(offcourse) the result will have more than 1 cursistID, but this is not what i want. I only want to see the ID once.

can someone help me?

 

[jmcilhinney]

You've already filtered out duplicate records with DISTINCT so if you're getting the same ID more than once then it must have differing values for the remaining fields in each record, so how are you intending to choose which of those to keep? You haven't told us what exactly you are trying to do or what any of this means, so it's hard for us to advise. My advice with complex queries that are not working is to go back to basics. Start with just a simple query with no joins and make sure that that part is returning the records you expect. Next, add a single join and make sure that that returns what you expect. Keep building the query up one piece at a time and if it works at every step then it will work at the end. You should never look at a complex problem as a single problem. Break it up into its constituent pieces and and solve each simple problem. Solving each simple problem will implicitly solve the whole complex problem.

 

[alaric]

thx for your reply
As you already suggested, I started with a simple and builded it up to this complex one. (I use the visual query in sql manager)
You're right, what do I want? This made me think it over again.
I use on OR in the weekdays, to resolve students who dance on the selected days. Offcourse this can give more than one day (so the query returns more than 1 result). My thought was to bring this back to only one. But, in fact, this is not what you want (the student dances on 2 diff days, so show the 2 equal names).
I just gonna retink a better way of filtering;
let user select only 1 day or make it with AND (search for student who dance on specified day AND the other specified day)
Anyway thanks again, and I will do my retinking