Query help

[kyledunn]

I have two tables, one with

EmployeeKey
EmployeeName

and the other with

EmployeeKey
CompanyName
Managed(Y/N)

Table 1:
1 Bob
2 Bill

Table 2:
1 Company1 Y
1 Company2 Y
2 Company1 Y
2 Company2 N

I need to query the two tables and return the employee name and company name for only the managed companies:

Bob Company1
Bob Company2
Bill Company1

Can someone help me with the query?

Thanks,

Kyle

 

[cheyney]

try this

select distinct tbl1.EmployeeName, tbl2.CompanyName
from tbl1 INNER JOIN tbl2 ON tbl1.EmployeeKey = tbl2.EmployeeKey
where tbl2.Managed = 'Y'


cheyney

 

[checkai]

select e.employeename, c.companyname
from table1 e inner join table2 c on e.employeeid=c.employeeid
where c.managed= true

this should work.

 

[kyledunn]

Thanks for your help but I am still unable to get the results I need.

I tried the query

select distinct E.EmployeeName, C.CompanyName, C.Managed from Employee E inner join Company C on E.EmployeeKey = C.EmployeeKey and Managed = 'Y'

I get

Bob Company1 Y
Bob Company2 Y
Bill Company1 Y
Bill Company2 Y

If I try it with Managed = 'N' then I get

Bob Company2 N
Bill Company2 N

I need the result to return only 3 rows

Bob Company1 Y
Bob Company2 Y
Bill Company1 Y

I've also been trying to get it to return just the 4 rows including the Managed flag.

Bob Company1 Y
Bob Company2 Y
Bill Company1 Y
Bill Company2 N

If I use the query above not excluding any rows based on the Managed flag in the Company table like this

select distinct E.EmployeeName, C.CompanyName, C.Managed from Employee E inner join Company C on E.EmployeeKey = C.EmployeeKey

I can see where the query produces the rows I want to exclude. Here is the results:

Bob Company1 Y
Bob Company2 N
Bob Company2 Y
Bill Company1 Y
Bill Company2 N
Bill Company2 Y

Can you help me to return only the three rows that produce this table?

Bob Company1 Y
Bob Company2 Y
Bill Company1 Y

Thanks for your help.

Kyle

 

[SQLSister]

Why do you want to return two rows for bob but only 1 for bill when clearly your results show that he is managing two sites as well?

 

[dodge20]

Try changing your and Managed = 'Y'

to
where
Managed = 'Y'

like checkai has in the example.


Dodge20

 

[cmgaviao]

This should do it unless I've missed something:

select t1.employeename, t2.companyname
from employee t1, company t2
where t1.employeekey = t2.employeekey
and t2.managed = 'Y'

 

[kyledunn]

SQLSister,

If you look at the original data in the tables from the first post on this thread you will see that the Managed(Y/N) flag for EmployeeKey 2 Company2 is set to N. The results return Y as a result of the join.

dodge20,

I tried both and and where and got the same results.

cmgaviao,

I think the reason that it doesn't work is that the join creates a new row

Bill Company2 Y

combined from both tables that I consider a false result. In the original data Bill's EmployeeKey for Company2 has a managaed flag set to N and yet the results from the query you described create a row with Bill Company2 and Y for the Managed flag. A condition based on a field in only one of the two tables in the join creates a new row that is a mixture from both tables but is a mixture I need to exclude because Bill's managed flag for Company2 is N in the database and yet the results return Y. I'm still looking for the solution. Thanks for your help.

Kyle

 

[checkai]

I just created a test DB with your values...THIS SHOULD WORK! I GOT your 3 results...

SELECT Table1.employeeKey, Table2.companyName, Table2.Managed
FROM Table1 INNER JOIN Table2 ON Table1.employeeKey = Table2.employeekey
WHERE Table2.Managed=True

 

[kyledunn]

I just did the same thing and you are correct, it does work! I had posted a simple example to try to understand my problem and when I applied the simple example to your query it worked correctly. That led me to re-evaluated the much lengthier query in my code and led me to my mistake. The false results were driving me crazy. Thank you very much for helping me to solve my problem.

Kyle