SQL Query

[lalitha1]

I have two tables X and Y. X has empid and jobid, they both can be either same or different. Table Y has TEST_EMPID and CompanyName. I am trying to join table X to table Y. I need to select CompanyName as Emp_CompanyName when x.Empid = Y.TEST_EMPID and companyName as Job_CompanyName when X.Jobid = Y.TEST_EMPID.

Any help.

Result set should look like this.
EMPID JobID Emp_CompanyName Job_CompanyName
abc abc Test Test
uv hi Test inc. Test inc.

 

[SQLSister]

so what do you have so far? why isn;t it working?

"NOTHING is more important in a database than integrity." ESquared

 

[jbenson001]

Try:

Select EMPID, JOBID, CompanyName as Emp_CompanyName, companyName as Job_CompanyName 
From X
   Inner Join Y ON  x.Empid = Y.TEST_EMPID 

Union

Select EMPID, JOBID, CompanyName as Emp_CompanyName, companyName as Job_CompanyName 
From X
   Inner Join Y ON  X.Jobid = Y.TEST_EMPID

 

[lalitha1]

Thanks. I am able to get the required columns Emp_companyName and Job_CompanyName with data. But again
I expect to see the same number of records in the result set as table X has which I dont. Do you still think it
's the issue with DB design.

 

[jbenson001]

Yes I do believe it's bad DB design. You shouldn't have 2 columns in a table that act as a foreign key to another.

 

[lalitha1]

Thanks.

 

[jbenson001]

No problem, good luck.