Q

Query (from Oracle) won't work in SQL Server

I am new to SQL Server (coming from Oracle) and am having trouble getting a query to return the proper results.

I have three tables:

 FAMILY ======= family A123 A211 B343 LOCATION ======== epa_family location A123 Europe FAMILY_INFO =========== family mfg A123 us A211 them
The LOCATION table epa_family is a subset of FAMILY family. The FAMILY_INFO table family is also a subset of FAMILY family.

I am trying to get a list of all familes from FAMILY, all locations from LOCATION that have a corrosponding record in FAMILY; and the mfg field from FAMILY_INFO where there is a record returned for LOCATION. So for the above data the result set would be:

 ------------------------------ family location mfg ------------------------------ A123 Europe us A211 ? ? B343 ? ?
I can get this to work in Oracle, but SQL Server either gives me syntax errors or incorrect results. I even tried the lazy man's method of building it using the Access query designer, but it didn't return the proper result set. Thanks for any help.

The first thing to watch for in going from Oracle to SQL Server is that you are using ANSI 92 syntax, especially when dealing with outer joins.

The query should look something like this:

 Select a.family, b.location, c.mfg From family a inner join location b on a.family = b.epa_location Inner join family_info c on a.family = b.family

 

For More Information

This was first published in October 2002

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close