SQL syntax - exclude values

[johngordon]

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.

LodgeID, Year
1, 2015
1, 2014
1, 2013
2, 2015
3, 2015
3, 2013

What would the SQL syntax be to return a list of lodges where the Year included 2015 only?

ie

2, 2015

Thanks.

[mawburn]

@johngordon

What would the SQL syntax be to return a list of lodges where the Year included 2015 only?

If it’s a varchar then:

 SELECT * FROM table WHERE Year = '2015' 

Iff it’s an int then:

SELECT * FROM table WHERE Year = 2015

[r937]

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

[johngordon]

Thanks - that’s correct. Only want results where 2015 is the only year a lodge has had a nomination.

I don’t need it to do anything - just give me the list.

I tried:

SELECT LodgeID, Year 
  FROM nominations 
GROUP 
  BY LodgeID 
HAVING COUNT(CASE WHEN Year = '2015') = 1

But getting a 1064 error.

[mawburn]

@johngordon

I don’t need it to do anything - just give me the list.

Use my version. It will return everything within 2015.

The query you posted in this latest reply doesn’t make sense.

[WolfShade]

[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.

HTH,

[r937]

[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

john, please do try my solution

[johngordon]

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.

Thanks again for the replies.

[r937]

@johngordon

So even if those bits are superfluous as a means to an end it did the job.

they aren’t superfluous, they’re an integral and necessary part of the solution

you could substitute something else for humpty and dumpty, but you have to have ~something~ there that the COUNT function can work on

[johngordon]

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.

[r937]

[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