How to write a query to join 2 table

Question

How to write a query to join 2 table

Whoknow

Newbie

Points: 8
More actions
March 10, 2008 at 1:32 am

#182992

Hi all,
I have 2 tables as below
Company: id, CompanyName
Employee: id, CompanyID, EmployeeName
as you guys can see that Employee.CompanyID is a foreign key link to Company table.
For some reason I have to write a query that will get me the result like this
CompanyName1 EmployeeName1 EmployeeName2 EmployeeName3
CompanyName2 EmployeeName1 EmployeeName2
CompanyName3 EmployeeName1
instead of
CompanyName1 EmployeeName1
CompanyName1 EmployeeName2
CompanyName2 EmployeeName3
CompanyName2 EmployeeName1
CompanyName3 EmployeeName2
CompanyName4 EmployeeName1
How can achieve this , thanks alot

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Whoknow (3/10/2008)
Hi all,
I have 2 tables as below
Company: id, CompanyName
Employee: id, CompanyID, EmployeeName
as you guys can see that Employee.CompanyID is a foreign key link to Company table.
For some reason I have to write a query that will get me the result like this
CompanyName1 EmployeeName1 EmployeeName2 EmployeeName3
CompanyName2 EmployeeName1 EmployeeName2
CompanyName3 EmployeeName1
instead of
CompanyName1 EmployeeName1
CompanyName1 EmployeeName2
CompanyName2 EmployeeName3
CompanyName2 EmployeeName1
CompanyName3 EmployeeName2
CompanyName4 EmployeeName1
How can achieve this , thanks alot

This should not really be done in SQL, but you have two main choices. One is to return the result using a single column (all employee names in one column, separated by commas):

SELECT c.CompanyName

, STUFF(( SELECT DISTINCT TOP 100 PERCENT

',' + t2.EmployeeName

FROM Employee AS t2

WHERE t2.CompanyID = c.id

ORDER BY ',' + t2.EmployeeName

FOR

XML PATH('')), 1, 1, '')

FROM Company c

or if you know the max number of employees your query can return, you can return the data using this format (you need to use PIVOT and row_number). This second solution has the major limitation that if you have more employees later, you will need to change the schema of the returned information.

Anyway, the above is something that should really be done in your client application 🙂

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

It's gonna be a mess if you have, say, 1000 employees... you said you have to do this "for some reason"... what is the reason?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vijaya Kadiyala SSCrazy Eights Points: 9119 More actions · Answer 3

Hi,

The simplest would be by using PIVOT function, as i undestand in general scenario there will n.Of employees for a given company. So its pretty hard write a query like this and at some point you will hit into an issue where SELECT clause can't support the no.of columns that you are looking for.

So tell us how many employees you have it on company by company basis and why you want to do this? what exactly your requirement.

For using Pivot, you can check out the below link

http://www.devx.com/dbzone/Article/28165

Thanks -- Vj

http://dotnetvj.blogspot.com

Thanks -- Vijaya Kadiyala
www.dotnetvj.com
SQL Server Articles For Beginers

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Pivot also has some limits... I believe you can only Pivot based on one column...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Jeff Moden (3/10/2008)
Pivot also has some limits... I believe you can only Pivot based on one column...

Not only is the new pivot command limited to one column, it's limited to one operation on one column. Meaning if you want the average of some values, and the sum of some other values - that's two separate pivot operations (=2 table scans, twice the reads, etc.... and twice the fun of course).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

There's just nothing like someone providing instant confirmation for us folks just finally getting into 2k5... 🙂 Thanks, Matt.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ian Yates SSCoach Points: 19738 More actions · Answer 7

This is something that you should definitely do in the presentation layer. You're pretty much making extra columns in your resultset which the presentation layer than happens to present in a convenient format. Most reporting tools (Crystal and SQL Server's both fit the bill) can easily do this for you.

Whoknow Newbie Points: 8 More actions · Answer 8

Hi all,

thanks for helping me out about this problem, There are only about 5 employees each company so the number of employee is not an issue here , let me try the query out and have a look of PIVOT function too , I'll get back later when I have any problem . But again thanks a lot 🙂

frecal Ten Centuries Points: 1094 More actions · Answer 9

Hi

were you able to work this out?

I need to do something similar

thanks

Fred