Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kyledunn
Starting Member
7 Posts |
 Posted - 2003-04-30 : 15:45:23
|
| I have two tables, one withEmployeeKeyEmployeeNameand the other withEmployeeKeyCompanyNameManaged(Y/N)Table 1:1 Bob2 BillTable 2:1 Company1 Y1 Company2 Y2 Company1 Y2 Company2 NI need to query the two tables and return the employee name and company name for only the managed companies:Bob Company1Bob Company2Bill Company1Can someone help me with the query?Thanks,Kyle |
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-04-30 : 16:08:15
|
| SELECT EmployeeName,CompanyName FROM Employees E INNER JOIN EmployeeKey EK ON E.EmployeeKey = EK.EmployeeKey WHERE NOT EXISTS(SELECT * FROM EmployeeKey WHERE EmployeeKey = E.EmployeeKey AND Managed = 'N') GROUP BY EmployeeName,CompanyName (Group by is optional, depends if you're expecting duplicates. You can also use distinct)Sarah Berger MCSD |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-04-30 : 16:51:58
|
That's a little more than you need to do. Keep it simple:  SELECT Table1.EmployeeName,Table2.CompanyName FROM Table1INNER JOINTable2 ONTable1.EmployeeKey = Table2.EmployeeKeyWHERE Table2.Managed = 'Y'- Jeff |
|
|
|
simondeutsch
Aged Yak Warrior
547 Posts |
Posted - 2003-04-30 : 17:13:37
|
| OOOOPS. I thought he wanted to return only employees who worked for managed companies only. My mistake.Sarah Berger MCSD |
|
|
|
kyledunn
Starting Member
7 Posts |
Posted - 2003-05-01 : 09:03:13
|
| Thanks Jeff and Sarah 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 getBob Company1 Y Bob Company2 Y Bill Company1 Y Bill Company2 YIf I try it with Managed = 'N' then I getBob Company2 N Bill Company2 NI need the result to return only 3 rows Bob Company1 Y Bob Company2 Y Bill Company1 Y I also tried your suggestion Sarah but also could not get it to return only the 3 rows. 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 NIf I use the query not excluding any rows based on the Managed flag in the Company table like thisselect distinct E.EmployeeName, C.CompanyName, C.Managed from Employee E inner join Company C on E.EmployeeKey = C.EmployeeKeyI can see where the query produces the rows I want to exclude. Here is the results:Bob Company1 Y Bob Company2 NBob Company2 Y Bill Company1 Y Bill Company2 NBill Company2 YCan 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 |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2003-05-01 : 09:26:18
|
| Kyle -- the data you showed us is not what you really have! if it was, what we gave you would work.you told us you have:Table 1: 1 Bob 2 Bill Table 2: 1 Company1 Y 1 Company2 Y 2 Company1 Y 2 Company2 N Do the join on a piece of paper and write out the results, forget about sql. see what you get.Bob (1) links to company1 Y and company2 YBill (2) links to company1 Y and company Nthat results in:Bob, company1, ybob, company2, ybill, company1, ybill, company2, nNow, apply the filter: Where Managed = "Y".That removes 1 row, and results in:Bob, company1, ybob, company2, ybill, company1, yIf your data is different, let us know!- Jeff |
|
|
|
kyledunn
Starting Member
7 Posts |
Posted - 2003-05-01 : 11:17:36
|
| Jeff, Thank you for the clear cut response. It was excellent. 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. Thank you very much for the help.Kyle |
|
|
|
|
|
|
|
|
Query help