How do I write this type of query? 2

[Qmoto]

The select statement I'm using to create my view has about 4 joins to get to various files that I need, however, there are two main files that I am pulling data from. One is the orders_current [ oC ] and the other is orders_historical [ oH ].

Here's what I need to do:
[ol]
[li]If I find a record in [ oC ] then add the record.[/li]
[li]If I find a record in [ oH ] but not in [ oC ] then add the record.[/li]
[li]If there is a record in both [ oC and oH ] ONLY ADD record from [ oC ][/li]
[/ol]

How to I write this type of query? It seems like it should be simple but for some reason I'm not understanding how to write the code for this.

 

[bborissov]

I am not sure what you want, but try this:

--- PREPARING TEST DATA
declare @orders_current TABLE (ID int, Fld2 varchar(20))
declare @orders_historical TABLE (ID int, Fld2 varchar(20))
INSERT INTO @orders_current VALUES (1, 'aaaaaaaa')
INSERT INTO @orders_current VALUES (2, 'bbbbbbbbb')

INSERT INTO @orders_historical VALUES (1, 'gggggggg')
INSERT INTO @orders_historical VALUES (2, 'hhhhhhhhh')
INSERT INTO @orders_historical VALUES (3, 'ccccccccc')
--- END PRAPARING


[code]
SELECT ISNULL(oC.Id, oH.Id) AS Id,
       ISNULL(oC.Fld2, oH.Fld2) AS Fld2
FROM @orders_current oC
FULL JOIN @orders_historical oH ON oC.Id = oH.Id

Result:

1	aaaaaaaa   -- Record from oC
2	bbbbbbbbb  -- Record from oC
3	ccccccccc  -- Record from oH

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Qmoto]

Thanks for the reply borrisov, I appologize for the slow reply!

The code you gave me fullfills points 1, and 2 in my question, but I'm still not sure how to take care of point three.

By adding a fourth record to the @orders_historical table the query returns a duplicate Fld2 value. How do I stop this from happening?

--- PREPARING TEST DATA
declare @orders_current TABLE (ID int, Fld2 varchar(20))
declare @orders_historical TABLE (ID int, Fld2 varchar(20))
INSERT INTO @orders_current VALUES (1, 'aaaaaaaa')
INSERT INTO @orders_current VALUES (2, 'bbbbbbbbb')

INSERT INTO @orders_historical VALUES (1, 'gggggggg')
INSERT INTO @orders_historical VALUES (2, 'hhhhhhhhh')
INSERT INTO @orders_historical VALUES (3, 'ccccccccc')[COLOR=blue]
INSERT INTO @orders_historical VALUES (4, 'aaaaaaaa') 
[/color]
--- END PRAPARING


[code]
SELECT ISNULL(oC.Id, oH.Id) AS Id,
       ISNULL(oC.Fld2, oH.Fld2) AS Fld2
FROM @orders_current oC
FULL JOIN @orders_historical oH ON oC.Id = oH.Id

RESULT:
1    aaaaaaaa   -- Record from oC
2    bbbbbbbbb  -- Record from oC
3    ccccccccc  -- Record from oH[COLOR=red]
4    aaaaaaaa   -- Record from oH *
[/color]

It is this last record I need to get rid of because it is in cO as well.

 

[gmmastros]

Try...

SELECT Min(ISNULL(oC.Id, oH.Id)) AS Id,
       ISNULL(oC.Fld2, oH.Fld2) AS Fld2
FROM @orders_current oC
FULL JOIN @orders_historical oH ON oC.Id = oH.Id
Group By ISNULL(oC.Fld2, oH.Fld2)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

No, The forth record is not in oC, because its ID s not the same. You compare the second field Fld2, but this field is result, not the main. There is no record with Id = 4 in the oC table.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[Qmoto]

bborrisov:
Grr! My code example is right but I miss typed the statment after the code - sorry for the confusion there!

gmmastros:
adding the min() solves the problem - thanks for the tip!

Now, if I understand what's going on in this code... the select statement is checking oC.Id and oC.Fld2 to see if they are null. If they are null, the value returned is from oH.Id and oH.Fld2 respectivly.

The min() function then tells the system to select record with the lowest Id number, which I could then substitute wit Max() to get the most recently entered recored and so on...


Thanks for the help guys! I really have to say the help provided on tek-tips is far above and beyone any other site I've used. You guys (and gals) ;0) take a lot of time out of your day to help us (mostly?) clueless learners along the way!

Thanks again,
Steve