Query from external source - linking tables?

L

Lori G

Hi,

I would like to run a query from an external SQL database that pulls
information from more than one table. When I tried to do this, the query did
not return the correct data. I guess it is probably a linking problem? How
can that be remedied, or can it in Excel?
 
J

Jamie Collins

Lori G said:
I would like to run a query from an external SQL database that pulls
information from more than one table. When I tried to do this, the query did
not return the correct data. I guess it is probably a linking problem?
Click to expand...

By 'linking', do you mean the sql JOIN syntax? Post some more details
e.g. some sample data, the expected result set, your existing sql
code, etc.

Jamie.

--
 
L

Lori G

Sorry - I'm new to this. I've only ever run queries using the wizard and one
table. I have 3 tables in this query. Here is my select statement:

SELECT fsmachin.fcmachinid, fsmachin.fac, fsmachin.fcpartno,
fsmachin.fcpartrev, fsmachin.fcserno, fsmachin.fcstatus, fsmachin.fcatcompan,
fsmachin.fccurcust, fsmachin.fmatstreet, fsmachin.fcattype,
fsmachin.fcataddr, fsmachin.fcatcity, fsmachin.fcatstate, fsmachin.fcatzip,
fsmachin.fcatcountr, fsmachin.fccusporef, fsmachin.fcjoborder,
fsmachin.fcorgcust, fsmachin.fcsalesno, fsmachin.fcshipper,
fsmachin.fdinstalle, fsmachin.fdshipped, fsmachin.fmdescript,
fsmachin.fmnotes, fsmachin.udrev, fsmachin.timestamp_column,
fsmachin.identity_column, fswarnty.fcwarntyid, fswarnty.fcwarntype,
fswarnty.fcstatus, fswarnty.flinclexp, fswarnty.flincllabo,
fswarnty.flinclpart, fswarnty.flinclsw, fswarnty.fdwarndate,
fswarnty.fdexpires, fswarnty.fdparend, slcdpm.fphone, slcdpm.fcfname,
slcdpm.fcontact, slcdpm.ffax, slcdpm.fcemail
FROM M2MDATA01.dbo.fsmachin fsmachin, M2MDATA01.dbo.fswarnty fswarnty,
M2MDATA01.dbo.slcdpm slcdpm
WHERE fsmachin.identity_column = fswarnty.identity_column AND
fsmachin.identity_column = slcdpm.identity_column AND
fswarnty.identity_column = slcdpm.identity_column

I tried to edit my select statement by joining the tables, for example:

AND fsmachin.fccurcust = slcdpm.fcustno AND fsmachin.fcmachinid =
fswarnty.fcmachid

The select statement was not rejected, but no data was returned.

Do you need more info? Thanks for your help!
 
J

Jamie Collins

Lori G said:
I've only ever run queries using the wizard and one
table. I have 3 tables in this query. Here is my select statement:

SELECT fsmachin.fcmachinid, fsmachin.fac, fsmachin.fcpartno,
fsmachin.fcpartrev, fsmachin.fcserno, fsmachin.fcstatus, fsmachin.fcatcompan,
fsmachin.fccurcust, fsmachin.fmatstreet, fsmachin.fcattype,
fsmachin.fcataddr, fsmachin.fcatcity, fsmachin.fcatstate, fsmachin.fcatzip,
fsmachin.fcatcountr, fsmachin.fccusporef, fsmachin.fcjoborder,
fsmachin.fcorgcust, fsmachin.fcsalesno, fsmachin.fcshipper,
fsmachin.fdinstalle, fsmachin.fdshipped, fsmachin.fmdescript,
fsmachin.fmnotes, fsmachin.udrev, fsmachin.timestamp_column,
fsmachin.identity_column, fswarnty.fcwarntyid, fswarnty.fcwarntype,
fswarnty.fcstatus, fswarnty.flinclexp, fswarnty.flincllabo,
fswarnty.flinclpart, fswarnty.flinclsw, fswarnty.fdwarndate,
fswarnty.fdexpires, fswarnty.fdparend, slcdpm.fphone, slcdpm.fcfname,
slcdpm.fcontact, slcdpm.ffax, slcdpm.fcemail
FROM M2MDATA01.dbo.fsmachin fsmachin, M2MDATA01.dbo.fswarnty fswarnty,
M2MDATA01.dbo.slcdpm slcdpm
WHERE fsmachin.identity_column = fswarnty.identity_column AND
fsmachin.identity_column = slcdpm.identity_column AND
fswarnty.identity_column = slcdpm.identity_column

I tried to edit my select statement by joining the tables, for example:

AND fsmachin.fccurcust = slcdpm.fcustno AND fsmachin.fcmachinid =
fswarnty.fcmachid

The select statement was not rejected, but no data was returned.

Do you need more info?
Click to expand...

If you had supplied some sample data and expect results (using just
the key columns and a few data columns) then I would've been able to
come up with a solution. In lieu, I'm just guessing.

You (or the wizard you employed) has used the old-style syntax for
equi-join. As of ANSI SQL-92, the syntax has been the INNER JOIN.
Simply re-writing using this syntax and the criteria you posted may
help identify the problem:

SELECT <<column list>>
FROM
(
M2MDATA01.dbo.fsmachin fsmachin
INNER JOIN M2MDATA01.dbo.fswarnty fswarnty
ON fsmachin.fcmachinid = fswarnty.fcmachid
)
INNER JOIN M2MDATA01.dbo.slcdpm slcdpm
ON fsmachin.fccurcust = slcdpm.fcustno
;

If this hasn't worked, post back with some data.

Jamie.

--
 
Top