SQL Query Problem

Question

SQL Query Problem

Viewing 5 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323741 More actions · Answer 1

a slight variation on your original code; all i've really done is present more data, so you can see the changed columns;

since you never showed the PHONE_NBR from the other table, you couldn't see if it was changed or not.

i also changed to a left outer join so you can see stuff that is not in the openquery.

Select

Alias1.id ,

Alias1.type ,

Alias1.number ,

Alias2.ID_NBR ,

Alias2.PHONE_TYPE ,

Alias2.PHONE_NBR

from dbo.telephone2 Alias1

LEFT OUTER JOIN

openquery (tst, 'select

ID_NBR ,

PHONE_TYPE ,

PHONE_NBR

from Member_Telephone')Alias2

on Alias1.id = Alias2.ID_NBR

AND Alias1.type = Alias2.PHONE_TYPE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

KamenickyBrian Newbie Points: 8 More actions · Answer 2

But I only want to show the 3 columns and have 2 distinct rows. I tried your way before and it showed both number but they were on the same line. Any other suggestions?

Lowell SSC Guru Points: 323741 More actions · Answer 3

you had joined them, thought that's what you wanted;

try a UNION ALL instead:

Select

Alias1.id ,

Alias1.type ,

Alias1.number ,

Alias2.ID_NBR ,

Alias2.PHONE_TYPE ,

Alias2.PHONE_NBR

from dbo.telephone2 Alias1

UNION ALL

SELECT

openquery (tst, 'select

ID_NBR ,

PHONE_TYPE ,

PHONE_NBR

from Member_Telephone')Alias2

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Atif-ullah Sheikh SSChampion Points: 12500 More actions · Answer 4

Or this might help you... I have used Temporary tables to give the idea....

The logic is same as of Lowell.

Declare @vTableA Table (ID int, [Type] varchar(10), Number int )

Declare @vTableB Table (ID int, [Type] varchar(10), Number int )

Insert into @vTableA

Select 123,'EL', 123

Insert into @vTableB

Select 123,'EL', 987

Select Distinct * from (

Select * from @vTableA

Union All

Select * from @vTableB

) MAin

----------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sometimes, winning is not an issue but trying.
You can check my BLOG [font="Arial Black"]here[/font][/url][/right]

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 5

[font="Verdana"]

Right now table B has a number of 987..but in the source table A it was changed to 123. Since table B is the final table I want to track history. So I need table B to look like this:
ID----Type---Number
123----EL-----123
123----EL-----987

If you want to maintain history, then why you dont keep separate table and how come TableB is main table iin your case?

--Mahesh

[/font]

MH-09-AM-8694