Joining Data between 2 SQL Server machines

I’m trying to retrieve data from between two separate SQL server machines.
I
have Server A and B on separate machine. Server A contains the majority of
my data and I’m developing my select on that server. I’ve discovered th
at I
also need data from Server B. How do I accomplish this?
Code:
Select ColumnA, ColumnB, ColumnC
From TableA inner join
ColumnA ON
(Select ColumnD, ColumnE
From ServerB.dbo.TableG.ColumnD) = ColumnA
where ColumnB = 1
I only need to retrieve two columns from server B and join them in my
select on server A.You'll need to create a linked server. See
http://msdn2.microsoft.com/en-us/library/ms190479(SQL.90).aspx
for information on the topic. After the linked server is defined, you can
query the table on Server B using a four-part name:
SELECT * FROM ServerB.[database name].dbo.[table name]
"Larry Bird" wrote:

> I’m trying to retrieve data from between two separate SQL server machine
s. I
> have Server A and B on separate machine. Server A contains the majority o
f
> my data and I’m developing my select on that server. I’ve discovered
that I
> also need data from Server B. How do I accomplish this?
> Code:
> Select ColumnA, ColumnB, ColumnC
> From TableA inner join
> ColumnA ON
> (Select ColumnD, ColumnE
> From ServerB.dbo.TableG.ColumnD) = ColumnA
> where ColumnB = 1
> I only need to retrieve two columns from server B and join them in my
> select on server A.
>

Join stuck...

Hi guys,

I'm stuck with this one... I have two tables....a parent and a child table...(parent*-1child).

What I am trying to do is retrieve all the parent rows where the child must contain two different values in one of its columns that is not the primary key.

Ok so Parent table structure:

ParentID

and child table structure:

ChildID (PK)

ParentID

Col3

So return all records from Parent where the Parent can have two different values for Col3. Or even three different values.

Try this query:

SELECT * FROM Parent

WHERE ParentID IN

(SELECT ParentID FROM Child

WHERE Col3 = 'something' OR Col3 = 'something else')

Or

SELECT * FROM Parent INNER JOIN Child ON Parent.ParentID = Child.ParentID

WHERE Child.Col3 = 'something' OR Child.Col3 = 'something else'

I hope this answers your question.

Best regards,

Sami Samir

|||

If I understand you correctly, you want the parent records where there are 2 or more child records with different values. If that is correct, perhaps something like this will help (for SQL 2005):

Code Snippet

DECLARE @.Parent table
( ParentID int )

DECLARE @.Child table
( ChildID int,
ParentID int,
SomeValue int
)

SET NOCOUNT ON

INSERT INTO @.Parent Values ( 1 )
INSERT INTO @.Parent Values ( 2 )
INSERT INTO @.Parent Values ( 3 )
INSERT INTO @.Parent Values ( 4 )
INSERT INTO @.Parent Values ( 5 )
INSERT INTO @.Child Values ( 1, 1, 1 )
INSERT INTO @.Child Values ( 2, 1, 2 )
INSERT INTO @.Child Values ( 3, 2, 1 )
INSERT INTO @.Child Values ( 4, 2, 2 )
INSERT INTO @.Child Values ( 5, 2, 3 )
INSERT INTO @.Child Values ( 6, 3, 1 )
INSERT INTO @.Child Values ( 7, 4, 1 )
INSERT INTO @.Child Values ( 8, 4, 1 )
INSERT INTO @.Child Values ( 8, 4, 2 )
INSERT INTO @.Child Values ( 7, 5, 1 )
INSERT INTO @.Child Values ( 8, 5, 1 )

SELECT ParentID
FROM @.Parent

EXCEPT

-- Remove singletons
SELECT ParentID
FROM @.Child
GROUP BY ParentID
HAVING count(1) = 1

EXCEPT

-- Remove Two Records, same Parent and Value
SELECT ParentID
FROM @.Child
GROUP BY ParentID, SomeValue
HAVING ( count(1) = 2
AND ParentID NOT IN (SELECT ParentID
FROM @.Child
GROUP BY ParentID
HAVING count(1) > 2
)
)

This 'feels' a bit awkward. Perhaps someone will have a better idea.

|||

Building on Arnie's Test Data. I think the query you want is:

Code Snippet

DECLARE @.Parent table

( ParentID int )

DECLARE @.Child table

( ChildID int,

ParentID int,

SomeValue int

)

SET NOCOUNT ON

INSERT INTO @.Parent Values ( 1 )

INSERT INTO @.Parent Values ( 2 )

INSERT INTO @.Parent Values ( 3 )

INSERT INTO @.Parent Values ( 4 )

INSERT INTO @.Parent Values ( 5 )

INSERT INTO @.Child Values ( 1, 1, 1 )

INSERT INTO @.Child Values ( 2, 1, 2 )

INSERT INTO @.Child Values ( 3, 2, 1 )

INSERT INTO @.Child Values ( 4, 2, 2 )

INSERT INTO @.Child Values ( 5, 2, 3 )

INSERT INTO @.Child Values ( 6, 3, 1 )

INSERT INTO @.Child Values ( 7, 4, 1 )

INSERT INTO @.Child Values ( 8, 4, 1 )

INSERT INTO @.Child Values ( 8, 4, 2 )

INSERT INTO @.Child Values ( 7, 5, 1 )

INSERT INTO @.Child Values ( 8, 5, 1 )

SELECT *

FROM @.PARENT

WHERE ParentID IN (

SELECT ParentID

FROM @.CHILD

GROUP BY ParentID

HAVING (COUNT(DISTINCT SomeValue) >1)

)

The marked inner query returns a list those parents IDs in the child table with more than one distinct value in SomeValue.

|||

Dhericean,

Thanks, that is much better. It was late and my thinking was not working properly. (Stuck in a bad WHILE loop, I think...)

Actually, if there is a defined PK-FK relationship between the tables, the outer query is not required. The solution then becomes:

SELECT ParentID
FROM @.CHILD
GROUP BY ParentID
HAVING ( count( DISTINCT ValueCol ) > 1 )

|||

--Hmmm...ok let me explain again....

DECLARE @.Parent table
( ParentID int )

DECLARE @.Child table
( ChildID int, PrimaryKey
ParentID int,
SomeValue int
)

INSERT INTO @.Parent Values ( 1 )
INSERT INTO @.Parent Values ( 2 )
INSERT INTO @.Parent Values ( 3 )
INSERT INTO @.Parent Values ( 4 )

INSERT INTO @.Parent Values ( 5 )
INSERT INTO @.Child Values ( 1, 1, 1 )
INSERT INTO @.Child Values ( 2, 1, 2 )
INSERT INTO @.Child Values ( 3, 2, 1 )
INSERT INTO @.Child Values ( 4, 2, 1 )
INSERT INTO @.Child Values ( 5, 2, 2 )
INSERT INTO @.Child Values ( 6, 3, 1 )
INSERT INTO @.Child Values ( 7, 3, 1 )
INSERT INTO @.Child Values ( 8, 3, 1 )
INSERT INTO @.Child Values ( 9, 4, 1 )
INSERT INTO @.Child Values ( 10, 4, 1 )
INSERT INTO @.Child Values ( 11, 4, 2 )

INSERT INTO @.Child Values ( 12, 5, 2 )

INSERT INTO @.Child Values ( 13, 5, 2 )

INSERT INTO @.Child Values ( 14, 5, 2 )

--Now what I want to retrieve is all the parents that have BOTH 1 and 2 in its child values. .ie. The parents 1,2 and 4 will be returned but not 3 and 5.

|||

So, I don't see the problem.

This query returns 1,2,4

Actually, if there is a defined PK-FK relationship between the tables, the outer query is not required. The solution then becomes:

SELECT ParentID
FROM @.CHILD
GROUP BY ParentID
HAVING ( count( DISTINCT ValueCol ) > 1 )

|||

Thanks...but is there another way of doing this. Reason being is that sometimes you only have the value for one child. Eg. Return parents that have only 1 in its child value. That means 1, 2, 3 and 4 will be returned.

Or return parents that have 2 in its child value which will return 1,2,3,4 and 5

The combination can be anything for child and can be one or more differnt child value combinations.

The front end simply allows the user to select a parent value and then select child value/s that belongs to the selected parent. Later on the user can select a different parent value as long as it contains the existing child value/s selected.

Thanks in advanced.

|||

Basically a child can have may parents...so I need a list of all the parents that have the same children passed.

Join Query

Hi,

Please help me in writing a join query.

I have three tables with three columns each. Now I want to retrieve data from all the three

tables as one.

Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points

These are related to three different divisions. So, on analysis we have get the no. of points

accumulated on a day. On any day the points can be in all three divisions or in any one or two

divisions. My database SQL Server 2000.

So, how to get this sort of output.

Empid Date Pts(DIv1) Pts(DIv2) Pts(DIv3)
V001 07-24-2004 Null Null 25
V002 07-24-2004 20 Null 25
V003 07-24-2004 Null 30 NUll
V001 07-23-2004 15 Null NUll
V002 07-23-2004 10 25 25
V001 07-22-2004 Null 10 25

I'm badly in need of help. Any sort of help is appreciated.

M.L.Srinivas<code>
SELECT T1.EmpId,T1.Date,T1.points as Div1pts,T2.points as Div2pts,T3.points as Div3pts FROM Table1 as T1
INNER JOIN Table2 as T2 on T1.EmpId=T2.EmpId and T1.Date=T2.Date
INNER JOIN Table3 as T3 on T2.EmpId=T3.EmpId and T2.Date=T3.Date
WHERE <i>condition</i>
</code>|||Hi,

Thanks for your reply..as you used inner join it works only for dates which are available in table1. What if the person scored only in second or third div. on a day and not in first div.

Please see my data sample carefully..i beleive we have to use full outer join...i was succesful for two tables..but how about three tables...

here is my query

select 'Tdate'= case
when a.date is null then b.date
else a.date
end,
a.points,b.points from table1 as a full outer join table2 as b
on a.empid=b.empid and a.date=b.date

Please guide me

M.L.Srinivas|||<code>
SELECT T.EmpId,T.Date,T1.points as Div1pts,T2.points as Div2pts,T3.points as Div3pts FROM
(SELECT EmpId,Date FROM Table1
UNION
SELECT EmpId,Date FROM Table2
UNION
SELECT EmpId,Date FROM Table3
) as T
LEFT JOIN Table1 as T1 ON T.EmpId=T1.EmpId and T.Date=T1.Date
LEFT JOIN Table2 as T2 ON T.EmpId=T2.EmpId and T.Date=T2.Date
LEFT JOIN Table3 as T3 ON T.EmpId=T3.EmpId and T.Date=T3.Date
WHERE condition
</code>

JOIN issue

Hello All... would love some help with the following SQL:

I am joining 3 Tables on Accession Number, to retrieve data. The Tables are:

ClinicalPatient
ClinicalSpecimen
ClinicalTestsRequested

There is (1) record in the ClinicalPatient Table for Accession 6281.
There are (3) records in the ClinicalSpecimen Table for Accession 6281. Each Accession has a SpecimenID (6281-01, 6281-02, 6281-03).
When a Test is Requested, a record is written to the ClinicalTestsRequested table and a "1" is posted to the record in the "AddTest" Field.

At this point there are (2) records in the ClinicalTestsRequested table that have the Value of "1" in the "AddTest" field.

When I run the following query, all (3) records display, but the third one ALSO has a "1" displayed in the AddTest Field and it should be blank?? What am I missing here?? ClinicalTestsRequested table is the only one with the AddTest field.

THANKS !!

SELECT DISTINCT ClinicalPatient.PatientID, ClinicalPatient.MedRecord, ClinicalPatient.LastName, ClinicalPatient.FirstName, ClinicalPatient.Address1, ClinicalPatient.Accession, ClinicalSpecimen.SpecimenID AS CSSpecimenID, ClinicalSpecimen.Accession AS CSAccession, ClinicalTestsRequested.AddTest

FROM ClinicalPatient INNER JOIN
ClinicalSpecimen ON ClinicalPatient.Accession = ClinicalSpecimen.Accession INNER JOIN
ClinicalTestsRequested ON ClinicalTestsRequested.Accession = ClinicalSpecimen.Accession

WHERE (ClinicalPatient.Accession = 6281)

Can you post the data? From your description you have:

ClinicalPatient table
6281

ClinicalSpeciman
6281 01
6281 02
6281 03

ClinicalTestREquested
6281 1
6281 1

This doesn't really make sense to me -- if you're doing an inner join on all 3 tables you should only get back 2 rows -- unless I'm missing something

|||

After some research and offering Lunch to a Colleague, We ended up trying and using LEFT OUTER JOIN to accomplish this requirement.

Thanks for all responses.

SelectCommand="SELECT DISTINCT
ISNULL(ClinicalTestsRequested.AddTest, 0) AS Expr1, ClinicalPatient.PatientID, ClinicalPatient.MedRecord, ClinicalPatient.LastName,
ClinicalPatient.FirstName, ClinicalPatient.Address1, ClinicalPatient.Accession, ClinicalSpecimen.SpecimenID,ClinicalSpecimen.Accession AS
CSAccession

FROM ClinicalSpecimen
LEFT OUTER JOIN
ClinicalPatient ON ClinicalPatient.Accession = ClinicalSpecimen.Accession

LEFT OUTER JOIN
ClinicalTestsRequested AS ClinicalTestsRequested ON ClinicalTestsRequested.SpecimenID = ClinicalSpecimen.SpecimenID

WHERE (ClinicalSpecimen.Accession = @.Accession)">

RESULTS:

0147SommaJohn62816281-0362811147SommaJohn62816281-0162811147SommaJohn62816281-026281