I have a table of nominations for lodges that includes a Year field. Any given lodge can be nominated in different years, so will multiple records in this table, e.g.
the way i read your question, you donât want lodges 1 and 3 because they have nominations in other years as well
so do this â
SELECT LodgeID
FROM daTable
GROUP
BY LodgeID
HAVING COUNT(CASE WHEN Year = 2015 THEN 'Humpty' ELSE NULL END) = 1
AND COUNT(CASE WHEN Year = 2015 THEN NULL ELSE 'Dumpty' END) = 0
[quote=âmawburn, post:5, topic:107695â]
The query you posted in this latest reply doesnât make sense.
[/quote]Not to mention I think âyearâ is a reserved word⌠so, if the column name canât be changed, then (depending upon what flavour of SQL) it should be [Year], I think.
[quote=âmawburn, post:5, topic:107695â]
Use my version. It will return everything within 2015. [/quote]unfortunately, that will return lodges 1 and 3 as well
he wants lodge 2 only
[quote=âmawburn, post:5, topic:107695â]The query you posted in this latest reply doesnât make sense.
[/quote]it sort of does, heâs trying to count all rows for 2015, but unfortunately, that will return lodges 1 and 3 also
I have been - but wasnât sure about the THEN⌠ELSE parts. So thought I could snip those out.
Which was when I got the error.
To âsuck it and seeâ I tried your code as was, with Humpty and Dumpty and it worked though. So even if those bits are superfluous as a means to an end it did the job.
This isnât doing anything on a site - its literally just a query to export out a list of contacts for a mailing list.
Sorry - âsuperfluousâ was the wrong word. I just first read it as something that would go further than return the list of values. i.e. with matching values, go and do one thing, and with the non matching values do something else.
Although I guess effectively the thing its doing with the matching values is returning them, and with the non matching values not returning them.
[quote=âjohngordon, post:11, topic:107695, full:trueâ]Although I guess effectively the thing its doing with the matching values is returning them, and with the non matching values not returning them.
[/quote]not exactly â itâs only returning the LodgeID