Joining Tables

Relevant Structure:

MasterOrg (OrgID, OrgName)

Child1(OrgID, Language)
Child2(OrgID, Service)
Child3(OrgID, Population)

Assuming a data sample as follows:

MasterOrg:
Org1, Test Organization
Org2, Another Agency

Child1
Org1, English
Org1, French
Org1, Spanish
Org1, Arabic
Org2, Spanish
Org2, Hatian/Creole
Org3, Hatian/Creole
Org3, Patios
Org3, Spanish

Child2
Org1, Advocacy
Org2, Advocacy
Org2, Counseling
Org2, Legal Aid
Org3, Advocacy
Org3, Counseling
Org3, Legal Aid

Child3
Org1, Seniors
Org1, Immigrants
Org2, Women
Org2, Immigrants
Org3, Youth

Is there an SQL statement that can accomplish the following:

Pull the data into a format that looks something like this:

Org1, English, Advocacy, Seniors
Org1, French, , Immigrants
Org1, Spanish
Org1, Arabic
Org2, Spanish, Advocacy, Women
Org2, Hatian/Creole, Counseling, Immigrants
Org2, Hatian/Creole, Legal Aid
Org3, Hatian/Creole, Advocaccy, Youth
Org3, Patios, Counseling
Org3, Spanish, legal Aidno

you have no means of associating the selected child tables in those particular combinations|||no

you have no means of associating the selected child tables in those particular combinations

I should have mentioned this to start with. I don't really care which service language and population wind up in one row, as long as they are all from the same Org. Does that change things?|||no

still can't do it

JOIN question and NOT JOIN

Hi,

This is a sample database table

TableA

============================

aID int identity(1,1) primary key

aName varchar(30)

TableB

===========================

bID int identity(1,1) primary key

bTitle varchar(30)

aID int references TableA(aID)

TableC

===========================

cID int indentity(1,1) primary key

cCategory varchar(30)

bID int references TableB(bID)

Here I got two query, are them the same?

Select A.aName, B.bTitle, C.cCategory

From TableA A, TableB B, TableC C

Where A.aID = B.aID And B.bID = C.cID

and

Select A.aName, B.bTitle, C.cCategory

From TableA A Join TableB B On A.aID=B.aID

Join TableC On B.bID=C.cID

Are those two the same?

And what is the different of JOIN and LEFT OUTER JOIN? Any other JOIN?

Millions Thanks!

Usually they are the same.

However, the first form is 'old' and will be soon deprecated.

Use the second form.

For more details about JOIN, refer to Books Online, Topics: Using Joins, JOIN

|||

hi ,

If you measure this, you will most likely discover that the two versions
use the exact same access plan. SQL Server tries very hard to optimize a
query, and in that process, a where clause which equates columns from two
tables will be converted to an inner join.

please check this link for the second question.

http://en.wikipedia.org/wiki/Join_(SQL)

hope, it clear

|||Thanks for leading me to the source.|||

The definitive 'source' is Books Online.

Wikipedia is often a good source of information also, but I would trust Books Online more than a wiki -especially if a job or exam was dependent upon the 'answer'.

|||

Books online can sometimes be complicated, but I agree with your answer.