SQL select where 1

[RosieGp]

I need to make some adjustment to a store Procedure, more like the select statement:

@EmployeeNumber varchar(15)
select ID, EmployeeNumber, EmployeeName,
from EmployeeTable
where ID = @EmployeeNumber

Ok i have 4 tables
EmployeeTable, EmployeeAddress, EmployeeRequest, EmployeeLog
Now I only have JobStatus in EmployeeRequest table...

EmployeeTable
ID EmployeeNumber EmployeeName
1 001 Joe
2 002 Sam
3 003 Dia

EmployeeAddress
ID EmployeeNumber EmployeeName EmployeeAdress
1 001 Joe 1200 S. BLVD
3 003 Dia 0987 E. park dr.

EmployeerRequest
ID EmployeeNumber EmployeeName JobStatus
1 001 Joe Y
2 002 Sam N
3 003 Dia Y

EmployeeLog
ID EmployeeNumber EmployeeName
1 001 Joe
2 002 Sam

So i need to add Address_Status in the query

@EmployeeNumber varchar(15)
select ID, EmployeeNumber, EmployeeName, [red]Address_Status[/red]
from EmployeeTable
where ID = @EmployeeNumber

Address_Status logic...

[blue]If EmployeeNumber exists in EmployeerLog table (compare Employee Number coming from EmployeeTable ) and if Job_Status IS 'N' in EmployeerRequest then Address_Status is 'Has No Address' else 'Has Address'

If EmployeeNumber exists in EmployeerAddress table (compare Employee Number coming from EmployeeTable ) then Address_Status is 'Has Address' else ''Has No Address'[/blue]

So all this has to be compared checked and added in the above query... Both the above conditions has to be added in the above query... Any help is appreciated.

 

[simian336]

ok.... Some things to consider. I am hoping you just posted what you thought you needed to help you with this query.

1st. all these tables should probably be combined into one.
You are duplicating data and making it harder on yourself. All 4 tables contain the same info except for 2 fields... EmployeeAdress and JobStatus

2nd you don't need and ID and an employeenumber if they are going to always be the same. Get rid of one of them.

3rd to get your answers as it stands your going to have to join all your data together, something like...

Declare @EmployeeNumber varchar(15)

set @EmployeeNumber ='001'

select ID, EmployeeNumber, EmployeeName, Address_Status,
case Job_Status
when 'N' then 'Has No Address'
else 'Has Address'
from EmployeeTable et
join EmployeeAddress ea
on et.EmployeeNumber=ea.EmployeeNumber
join EmployeeRequest er
on et.EmployeeNumber=er.EmployeeNumber
join EmployeeLog el
on et.EmployeeNumber=el.EmployeeNumber
where EmployeeNumber = @EmployeeNumber


4th @EmployeeNumber is a variable and will limit your selections to emp at a time.

5th you had ID = @EmployeeNumber with is a integer compared to a varchar... Should have been EmployeeNumber = @EmployeeNumber

Simi