How to use case statement to compare multiple columns

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Hello,
I have 2 tables :
1)table course (id,name)
2)table reservation (id,name)
The manager in the university can reserve any course,
sure where a course is reserved then the id and the name of this course should be inserted by the system in the reservation table,
after reserve courses,
i want to show a column near to the name of the course :
if (id,name) to the course table are contained in the (id,name) to the reservation table then 'Course Reserved' else 'Not Reserved'
i tried not in statement but it don't work on multiple columns
thanks

Posted 8-Oct-14 5:35am

J.Ch4

Updated 8-Oct-14 5:47am

v2

Add a Solution

Comments

Maciej Los 8-Oct-14 11:41am

Not enough information! We can't read in your mind or direct from your screen! Please improve your question (use "Improve question" widget) and provide more details (i.e.: sample data).

Maciej Los 8-Oct-14 11:50am

How both tables are related? What have you tried (provide your SELECT statement)? Where are you stuck?

J.Ch4 8-Oct-14 11:51am

i want only to check if the id and name in the course table are contained in the reservation table , if yes i want to write near to it 'Reserved' else 'Not reserved'

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-10-08T06:19:00

Solution 1

Please, read my comments to the question.

I'm not sure your requirements, but try this:

SQL

SELECT c.ID AS CourseID, CASE WHEN r.ID IS NULL THEN 'Not reserved' ELSE 'Reserved' END AS ReservationID
FROM Course AS c LEFT JOIN Reservation AS r ON c.ID = r.ID

You can achive similar effect with COALESCE function:

SQL

SELECT c.ID AS CourseID, COALESCE(r.ID, 'Not reserved') AS ReservationID
FROM Course AS c LEFT JOIN Reservation AS r ON c.ID = r.ID

Please, read these articles:
Visual Representation of SQL Joins[^]
CASE (SQL)[^]
COALESCE (SQL)[^]

Posted 8-Oct-14 6:19am

Maciej Los

Comments

CHill60 8-Oct-14 12:21pm

5'd

Maciej Los 8-Oct-14 12:30pm

Thank you, Caroline ;)

J.Ch4 8-Oct-14 12:23pm

yes it's perfect thank you very much !

Maciej Los 8-Oct-14 12:46pm

You're very welcome ;)

Maciej Los 8-Oct-14 13:05pm

Can you accept this answer as a solution (green button), formally to remove this question from unanswered list?