CODEWITHSUNDEEP
sqlleft-joincase
ssbb
ssbb

Reputation: 245

using join in case sql

i am trying to make a query but it is not right.

what i want to do is to apply join in case

my query is

SELECT LEFT(Student_First_Name,LEN(Student_First_Name)-LEN(Name_Lookup_Table.Dirty_Name)),Name_Lookup_Table.Dirty_Name, Name_Lookup_Table.Standard_Name

case
when Transformed_All_Student.Student_First_Name like '% '+Name_Lookup_Table.Dirty_Name
then
from Transformed_All_Student left join Name_Lookup_Table 
on Transformed_All_Student.Student_First_Name like '% '+Name_Lookup_Table.Dirty_Name

when Transformed_All_Student.Student_First_Name like '% '+Name_Lookup_Table.Dirty_Name+'%'
then from Transformed_All_Student left join Name_Lookup_Table on Transformed_All_Student.Student_First_Name like '% '+Name_Lookup_Table.Dirty_Name+'%'

when Transformed_All_Student.Student_First_Name like Name_Lookup_Table.Dirty_Name+'% '
then from Transformed_All_Student left join Name_Lookup_Table on Transformed_All_Student.Student_First_Name like Name_Lookup_Table.Dirty_Name+'% '

can anyone help??

Upvotes: 1

Views: 165

Answers (2)

urlreader
urlreader

Reputation: 6605

I do not think you can put FROM in case when. one possible solution is using UNION. You probably can optimize this:

    SELECT LEFT(Student_First_Name
        , LEN(Student_First_Name)-LEN(nlt.Dirty_Name))
        , nlt.Dirty_Name
        , nlt.Standard_Name

    FROM Transformed_All_Student tas
            LEFT JOIN Name_Lookup_Table nlt
                        ON tas.Student_First_Name like '% '+nlt.Dirty_Name
    WHERE tas.Student_First_Name like '% '+nlt.Dirty_Name

    UNION

    SELECT LEFT(Student_First_Name
        , LEN(Student_First_Name)-LEN(nlt.Dirty_Name))
        , nlt.Dirty_Name
        , nlt.Standard_Name

    FROM Transformed_All_Student tas 
            LEFT JOIN Name_Lookup_Table nlt 
                ON tas.Student_First_Name like '% '+nlt.Dirty_Name+'%'

    WHERE tas.Student_First_Name like '% '+nlt.Dirty_Name+'%'

    UNION

    SELECT LEFT(Student_First_Name
        , LEN(Student_First_Name)-LEN(nlt.Dirty_Name))
        , nlt.Dirty_Name
        , nlt.Standard_Name
    FROM Transformed_All_Student tas 
            LEFT JOIN Name_Lookup_Table nlt 
                    ON tas.Student_First_Name like nlt.Dirty_Name+'% '

    WHERE tas.Student_First_Name like nlt.Dirty_Name+'% '

Upvotes: 1

saarp
saarp

Reputation: 1951

Since you're trying to do things this way, I assume performance is not a primary concern. Also, am assuming this is for MS SQL since not otherwise specified. The general form is below:

SELECT 1
FROM foo f
JOIN bar b
   ON CASE WHEN f.col1 = 'X' then 'Y' ELSE  END = b.col1

Another possible way of getting the same result is using a subquery:

SELECT 1
FROM (
    SELECT *, CASE WHEN f.col1 = 'X' then 'Y' ELSE  END JoinCol
    FROM foo
) f
JOIN bar b
    ON f.JoinCol = b.col1

There are probably a couple of other methods. Would be best if you can give us a quick definition of the tables involved and the columns you want in the output.

Hope this helps.

Upvotes: 1

Related Questions