4 Replies - 1493 Views - Last Post: 01 December 2013 - 03:34 AM

#1 Jiro_  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 60
  • Joined: 27-August 12

trouble with CASE

Posted 30 November 2013 - 10:09 AM

I am developing a web application for schools. the goal is to get all teachers, and the course they give. however, since some teachers teach multiple classes, I added this:
there's an option to select a class, and display the course a teacher gives in that class.
I tried using a CASE statement for this, but so far I have no success. any help would be appreciated!
the Query:
SELECT tblPersoon.Achternaam,tblVakken.Vak,tblPersoon.ID,tblLokaal.Lokaal FROM tblPersoon INNER JOIN tblLeerkrachtKlas ON tblPersoon.ID = tblLeerkrachtKlas.LeerkrachtID INNER JOIN tblVakken ON tblLeerkrachtKlas.VakID = tblVakken.ID INNER JOIN tblLokaal ON tblLeerkrachtKlas.LokaalID = tblLokaal.ID CASE WHEN (tblPersoon.ID = 6) THEN (WHERE tblLeerkrachtKlas.KlasID = 1) END;

PS: this is my first time using CASE so there'll most likely be an error in there

Is This A Good Question/Topic? 0
  • +

Replies To: trouble with CASE

#2 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5641
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: trouble with CASE

Posted 30 November 2013 - 10:48 AM

Let's just format this a little:
SELECT tblPersoon.Achternaam, tblVakken.Vak, tblPersoon.ID, tblLokaal.Lokaal
	FROM tblPersoon
		INNER JOIN tblLeerkrachtKlas 
			ON tblPersoon.ID = tblLeerkrachtKlas.LeerkrachtID 
		INNER JOIN tblVakken 
			ON tblLeerkrachtKlas.VakID = tblVakken.ID
		INNER JOIN tblLokaal
			ON tblLeerkrachtKlas.LokaalID = tblLokaal.ID
		CASE WHEN (tblPersoon.ID = 6) THEN (WHERE tblLeerkrachtKlas.KlasID = 1) END;



Hmm... you just kind of tacked it on the end, huh?

There are two primary filters in SQL, WHERE and HAVING. If you are trying to limit data, then that's what you use. You can use a case in either of these statements, or in the select. But, where you have it? Not really.

Just guessing, but this would be a start:
SELECT tblPersoon.Achternaam, tblVakken.Vak, tblPersoon.ID, tblLokaal.Lokaal
	FROM tblPersoon
		INNER JOIN tblLeerkrachtKlas 
			ON tblPersoon.ID = tblLeerkrachtKlas.LeerkrachtID
				AND tblLeerkrachtKlas.KlasID = 1
		INNER JOIN tblVakken 
			ON tblLeerkrachtKlas.VakID = tblVakken.ID
		INNER JOIN tblLokaal
			ON tblLeerkrachtKlas.LokaalID = tblLokaal.ID
	WHERE tblPersoon.ID = 6;


Was This Post Helpful? 0
  • +
  • -

#3 Jiro_  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 60
  • Joined: 27-August 12

Re: trouble with CASE

Posted 30 November 2013 - 11:49 AM

I think I wasn't clear enough, sorry for that ^^'
the code I am using at the moment is this:
SELECT tblPersoon.Achternaam,tblVakken.Vak,tblPersoon.ID,tblLokaal.Lokaal
FROM tblPersoon
INNER JOIN tblLeerkrachtKlas ON tblPersoon.ID = tblLeerkrachtKlas.LeerkrachtID 
INNER JOIN tblVakken ON tblLeerkrachtKlas.VakID = tblVakken.ID 
INNER JOIN tblLokaal ON tblLeerkrachtKlas.LokaalID = tblLokaal.ID

what I'm trying to achieve basicly boils down to this:
if tblPersoon.ID = 6
then it should add another where clause:
WHERE tblLeerkrachtKlas.KlasID = 1

I was wondering if this is possible, or wether I'll have to program a way around it using other languages
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is online

  • Dreaming Coder
  • member icon

Reputation: 5641
  • View blog
  • Posts: 12,359
  • Joined: 16-October 07

Re: trouble with CASE

Posted 30 November 2013 - 12:23 PM

Ah, I see.

Try:
WHERE tblPersoon.ID <> 6 OR ( tblPersoon.ID = 6 AND tblLeerkrachtKlas.KlasID = 1)


Was This Post Helpful? 1
  • +
  • -

#5 Jiro_  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 60
  • Joined: 27-August 12

Re: trouble with CASE

Posted 01 December 2013 - 03:34 AM

View Postbaavgai, on 30 November 2013 - 12:23 PM, said:

Ah, I see.

Try:
WHERE tblPersoon.ID <> 6 OR ( tblPersoon.ID = 6 AND tblLeerkrachtKlas.KlasID = 1)



I can't believe the answer is so simple! thanks a lot!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1