Need help with SQL to get an ID from one of two tables... 1

[DougP]

Is there a way to have one long SQL script find an ID if it exists in a Employees table or a Managers table?
I have a 3rd table called logins which has the persons unique ID.
The employees table has ID | Lastname | Firstname (names in separate columns).
Mangers has ID | Lastname, Firstname | and the managers has both names in one column.
the 3rd table has Unique ID | DateLoggedIN | Wherefrom .
The Wherefrom column contains one of 3 data items "Admin From Managers" , "Manager From Managers"
or "Resource from Resources" a resource is an employee and a manager could have different rights "Admin" or "Manager"

So is there a way to use "case" and have it get the managers name if the Wherefrom contains the word "Managers" or
case when and Select the Lastname , Firstname from the Resources Table if the Wherefrom contains the word "Resources" ?

Select name from ....
Case Wherefrom is substring(1, Wherefrom , "Resources")
Inner join (Select Lastname + ',' + from Firstname as name from Resources on Resources.ID = Logins.ID Where Logins.ID = Wherefrom.ID)
case else
Inner join
Select Manager from Managers on Managers.ID = Logins.ID Where Logins.ID = Wherefrom.ID
...

TIA



DougP

 

[simian336]

I assume id's are unique...

With cte as (
select ID, Lastname +' ' +Firstname as Name
from employees
union all
select ID, Name
from Mangers)
select name
from cte
Inner join
from logins
on cte.ID = Logins.ID

Simi

 

[simian336]

untested... there was a syntax error

With cte as (
select ID, Lastname +' ' +Firstname as Name
from employees
union all
select ID, Name
from Mangers)
select name
from cte
Inner join logins
on cte.ID = Logins.ID

Simi

 

[denimined]

I assume that you have a valid reason for doing this as a column lookup rather than a full join, but here you go...
Note that if the Logins.ID does not exist in the matching table, then it returns the Logins.ID as the name.
Also, it puts both name constructs into the same format as the Managers format.

select ...
  case 
    when Logins.WhereFrom like '%Resources%' 
      then ISNULL((select top 1 rtrim(emp.lastname) + ', ' + rtrim(emp.firstname) as name
                   from employees as emp
                   where emp.ID = Logins.ID
                  ), Logins.ID)
    when Logins.WhereFrom like 'Manager%'
      then ISNULL((select top 1 rtrim(mgr.lastname) as name
                   from Managers as mgr
                   where mgr.ID = Logins.ID
                  ), Logins.ID)
  end as Name,
...
From Logins
...

 

[DougP]

denimined,
When I try to interpolate my "made up" column names with your code I got 15 red lines.
So these are the actual columns names, and all I need is for it to work. if CASE in not it then thats fine. The Following code does not return all of the rows in the SOWwhoLoggedIN table. there are 4 people and I only get 2. I presume becasue those two are in both SOWManagers and SOWResources
SOWResources = employees
SOWManagers = Managers
SOWwhoLoggedIN = Logins
ePrizeID = ID

Select  rs.ePrizeID, rs.ResourceLastName as Lname, 
rs.ResourceFirstName as Fname, li.FromWhere, m.Manager
From dbo.SOWwhoLoggedIN li
inner join SOWResources rs on li.ePrizeID = rs.ePrizeID 
Inner join dbo.SOWManagers m on li.ePrizeID = m.ePrizeID 
Order by ePrizeID

DougP

 

[denimined]

Hi Doug.

Since you inner-joined both tables, the listing you gave will only show those entries that are in both tables - hence why you only got 2 entries returned.
Also, with the sub-queries built into the case statement you don't need to add the inner joins.

Try this...

select li.ePrizeID,
       case 
         when li.FromWhere like '%Resources%' 
           then ISNULL((select top 1 rtrim(emp.lastname) + ', ' + rtrim(emp.firstname) as name
                        from SOWResources as emp
                        where emp.ePrizeID = li.ePrizeID
                       ), li.ePrizeID)
         when li.FromWhere like '%Managers%'
           then ISNULL((select top 1 rtrim(mgr.Manager) as name
                        from SOWManagers as mgr
                        where mgr.ePrizeID = li.ePrizeID
                       ), li.ePrizeID)
       end as Name,
       li.FromWhere       
From SOWwhoLoggedIN as li
order by li.ePrizeID

 

[DougP]

SWEET denimined,
!!!
have a STAR

DougP