how to fetch data using Case statement from multiple tables

Question

1.00/5 (1 vote)

See more:

I have 3 tables Prospects(KeyProspects, Keyterm,KeySubjectElective,Elective,Subject)
Elective(KeyElective, KeyElectiveGroup,KeySubject)
Subject(KeySUbject,SubjectName,KeyCourse)

When user select KeyProspects(in Prospects Table) and KeyTerm (in Prospects Table)
then i want to fetch KeySubject and Subject Name, i.e the query will return multiple subjects, in prospects table if 'Elective' attribute value is True then i want to fetch subjects against 'keyElectiveGroup' in Elective table,
if 'Subject' attribute value is True then directly to fetch subjects from subject table where p.keyElectiveSubject =S.keyElectiveSubject

SQL

select
    Case
       When p.Elective='True' Then (Select  S.Key_Subject,S.Subject_Name from Subjects S, Electives E,Prospects P
       where S.Key_Subject=E.Key_Subject
            and P.KeySubjectElective=E.KeyElective_Group
            and E.KeyElective_Group=2)
       When P.Subject='True' Then (Select S.Key_Subject,S.Subject_Name from Subjects  S,Prospects P
       where S.Key_Subject=P.KeySubjectElective)
        End
        from Prospects P
        where p.KeyTerm=2

Posted 9-Nov-14 20:31pm

Abdullah Kundi

Updated 10-Nov-14 22:51pm

Suraj Sahoo | Coding Passion

v4

Add a Solution

Comments

Kornfeld Eliyahu Peter 10-Nov-14 2:50am

Look for JOIN statement...

Maciej Los 10-Nov-14 3:55am

Agree

Maciej Los 10-Nov-14 3:56am

What relatioship is between tables?

Abdullah Kundi 10-Nov-14 4:05am

Prospects one to many with Elective.
Prospects one to one with Subject

Madhu Nair 11-Nov-14 1:49am

As you are saying "when user selects" that means you are allowing user to make the selection. If that is the case why are you not creating stored procedure with parameters? I think this will be enough

SRS(The Coder) 11-Nov-14 5:39am

Please try with dynamic SQL, it will definitely help in your scenario.

1 solution

Add a Solution

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

Maciej Los · Accepted Answer · 2014-11-11T20:28:00

Although i can only guess what you want to achieve, i decided to post my answer to clarify what you're doing wrong.

As Kornfeld Eliyahu Peter[^] mentioned, you should use JOINs instead of joining table via WHERE stetement. See this great article: Visual Representation of SQL Joins[^]. Remeber, there are few ways to join data. Choose proper one, depending on your needs.

Your CASE[^] statement is written incorrectly. To make it working, the SELECT statement must operate on some recordset. In your case, the query will return nothing. The proper

SQL

SELECT P.KeySubjectElective, CASE WHEN P.Elective='True' THEN 'Look in Electives table' ELSE '' END AS CaseFieldOne, 
    CASE WHEN P.Subject='True' THEN 'Look in Subjects table!' END AS CaseFieldTwo
FROM Prospects P

Finally, i'd suggest to use UNION[^] statement:

SQL

SELECT S.Key_Subject, S.Subject_Name
FROM Subjects S (CROSS|INNER|LEFT|RIGHT) JOIN Electives E ON S.Key_Subject=E.Key_Subject
    (CROSS|INNER|LEFT|RIGHT) JOIN Prospects P ON P.KeySubjectElective=E.KeyElective_Group and E.KeyElective_Group=2
WHERE P.Elective='True'
UNION ALL
SELECT S.Key_Subject, S.Subject_Name
FROM Subjects S (CROSS|INNER|LEFT|RIGHT) JOIN Prospects P ON S.Key_Subject=P.KeySubjectElective
WHERE P.Subject='True'

how to fetch data using Case statement from multiple tables

1 solution

Solution 2

Add your solution here

Preview 0

how to fetch data using Case statement from multiple tables

1 solution

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us