SQL and CASE statement issue... 3

[hneal98]

Hi Everyone,

I need to know if this is possible?

SELECT

CASE​

WHEN IDI.ID = E.ENROLLID THEN 'Entry'​

WHEN IDE.ID = E.EXITID THEN 'Exit'​

END AS Test​

FROM

Enrolled E​

INNER JOIN TestTable IDI ON E.EnrollID = IDI.ID​

INNER JOIN TestTable IDE ON E.ExitID = IDE.ID​

If not, is there a way to do something like this?

Thanks in advance.

 

[SkipVought]

Well what happens when you execute your query?

 

[Olaf Doschke]

This will work, but it's about the same way as IF a=1 AND a=2 will technically work but make no sense.

INNER JOIN, think about what it does.

The two conditions you have do not necessarily exclude each other, but you only get final results with both an Entry and Exit match. If you want to see all, make it LEFT joins.
General rule: If you want ALL data of one table and side info where applicable, you cannot INNER JOIN any other data, you always need outer joins, inner joins act as intersection of data, filtering.

See the famous venn diagram of sql joins:

http://bhaumik89.blogspot.de/2012/10/venn-diagram-visual-representation-of_7.html

Bye, Olaf.

 

[gmmastros]

hneal,

You're likely to get a more complete answer to your question if you explain what it is you are trying to do and also post some sample data that represents the situation.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hneal98]

Thanks for the responses.

Here is what I am trying to achieve...

I have two tables like this:

Enroll table
Enrollid EntryID Exit ID
1 111 121
2 112
3 123 128
4 124 156
5 125


Assessment table
Assessment AssessmentDate111 01/01/2014
112 01/02/2014
121 01/05/2014
123 01/08/2014
124 01/08/2014
125 01/09/2014
128 01/10/2014
156 01/11/2014

Sorry, I don't know how to setup tables on this board. There should be 3 columns in the first set and 2 columns in the second.
So, I am trying to capture the Exit and Entry records. However, I want them listed out in one column and do a count like this:

Entry 5
Exit 3

Hopefully that helps.

Thanks.

 

[SQLBill]

If all you are counting is the entry id's and exit id's, then something as easy as this maybe???

SELECT 'Entry: ' + COUNT(EntryID) + ', Exit: ' + COUNT(exited)
FROM Enroll

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Olaf Doschke]

Indeed I'd go with SQLBills simple query, if all you want to do is count, simply replace his misspelled "exited" with ExitID.

What I assumed is the case, your INNER instead of outer JOINs cause your query to limit data to Enroll records having both IDs set, so you don't catch/count cases of someone having no ExitID, still being enrolled. So do LEFT joins, if you want to repair your query.

Bye, Olaf.

 

[hneal98]

Yes, but I want it to show a listing like the following:

Entry 5
Exit 3

I am working with a SQL tool that our clients are using and it is very limited. So I was trying to figure out a simple way of producing this. I may just end up being that I have to create two queries, one for Entry and one for Exit. I was just trying to avoid the extra work and just do it in one query.

SQLBIll, I used to be on this board a lot a long time ago and remember you from back then. You helped me a lot. Good to see you are still here.

Thanks for you help though.

 

[gmmastros]

I want them listed out in one column

SELECT 'Entry: ' + Convert(VarChar(10), COUNT(EntryID)) As Result
FROM   Enroll

Union All

Select 'Exit: ' + Convert(VarChar(10), COUNT(exitid))
FROM   Enroll

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hneal98]

Well This is why I posted here. It isn't that easy. I know how to write SQL, however this is a special case due to this tool we use for users to write their own queries. It is limited to keep them from building queries that would bring down the system.

Anyway, thanks for trying.

 

[gmmastros]

I have a goofy little tool that my users use to create custom reports, so I kinda know what you are talking about. It is very limited in what you can do, at at least you can create some basic reports without needing to get a programmer involved.

In my application, I allow users to pick tables and views to base their queries on. This brings me to my point. Is it possible that you can create a view in your database that your users can use? The query I posted earlier can easily be made in to a view.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[hneal98]

Yes, but the powers that be are very particular about what views can be created and stored as well as whether they feel like bothering, so in the long run, it is easier just to create two queries. That's life when you don't have control over things. :-(, but I am cool with it as it means less for me to be responsible for.

Thanks again.

 

[Olaf Doschke]

Since EnrollID/EntryID is always set in your sample data, your CASE will always result in the first case, you never get to the second one. I don't see how you can get what you want without a union.
At least you could have a third case:

WHEN IDI.ID = E.ENROLLID AND IDE.ID = E.EXITID THEN 'Both'

The case order also will matter, if you ever want to get 'Exit' only, either you need data with only ExitID set, or this case has to be before the other.

CASE
WHEN IDI.ID = E.ENROLLID AND IDE.ID = E.EXITID THEN 'Both' 
WHEN IDE.ID = E.EXITID THEN 'Exit'
WHEN IDI.ID = E.ENROLLID THEN 'Entry'
END AS Test

This case with your sample data presumably will give several 'Both' and some 'Entry' records. You might simply take this 'Both' case as Exit case and simply remove the Exit case never occurring. Then you may apply an outer SELECT to GROUP and COUNT this data.

I hope that clears things out in a way possible with your reporting tool.

Bye, Olaf.

 

[hneal98]

I think you just solved my puzzle. I can work with this. Thank you very much.

 

[SQLBill]

I'm glad I was able to help you in the past. Another option for getting the values on separate lines is:

SELECT 'Entry: ' + COUNT(EntryID) + CHAR(10) + CHAR(13) +'Exit: ' + COUNT(ExitID)
FROM Enroll

Even if you can't use this and the other suggestions provided, maybe they will help you in the future.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[hneal98]

Thank you SQLBill. I appreciate it.