April 4, 2007 at 8:29 am
I have two tables, employee and dependants. I want to create one table or a view for that matter that will list the active employees and all thier associated dependants. I rarely use multiple selects, so I'm having trouble on how to code this. I've attached my code that list the dependants of the employee, the only data missing is the employee itself.
SELECT *
FROM dbo.employee
INNER JOIN
dbo.dependant ON dbo.employee.emp_id = dbo.dependant.emp_id
WHERE (dbo.employee.status = 'A') and (dbo.employee.company <> 'ABC')
I invisioned something like this
select * from employee
WHERE (employee.status = 'A') AND (dbo.employee.company <> 'ABC')
and (dbo.employee.emp_id) = (SELECT *
FROM dbo.dependant WHERE (employee.emp_id = dependant.emp_id))
Any help would be greatly appreciated.
April 4, 2007 at 9:19 am
I would prefer to use left join in the first query. The reason is what if an employee does not have any dependent, or the data on the dependant has not been input.
Also, I would specify the column names in the SELECT clause because there are some same column names in the both tables.
April 4, 2007 at 11:32 am
The first SQL only pulls the dependants, the second was an example I thought would work. Any ideas how I can do this? I created a union statement that kinda worked, but I would like to do it using a select statement.
SELECT emp_id,last_name,first_name FROM empper
WHERE COMPANY <> 'HLH'
UNION ALL
SELECT emp_id,last_name,first_name FROM empdepnd
ORDER BY emp_id
April 5, 2007 at 6:51 am
Modifying your first select with SQL ORACLEs reccomendations should do the trick for you.
April 5, 2007 at 7:05 am
Thanks for everyones help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply