A UNION made in heaven, or hades?

Hi all, Hope everyone is well, my poor Steelers got chomped and spit, and I've been spending lots of time today eating my words *sigh*.

However, in between all the crow-chomping, I have run into a problem I think I am too closely involved with to see around.

I have two identical tables in two different databases. They contain (for simplicity's sake) a symbol, and a ranking for that symbol. The two tables should be the same, but are sometimes not, so I am trying to figure out a way to select from the tables in such a way that I can say:

"Symbol xxx is ranked nn in table t1, but is ranked mm in table t2"

Perhaps I am getting too fancy, but thought I could do it in a single select.

here's what I have so far:
CREATE TABLE tMyPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)

CREATE TABLE tUrPicks (
sym VARCHAR(5) NOT NULL
, rank INT NOT NULL
)

INSERT INTO tMyPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 4
UNION SELECT 'IMA', 5
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 7
UNION SELECT 'SORU', 8
UNION SELECT 'HERE', 9

INSERT INTO tUrPicks (sym, rank)
SELECT 'BFA', 1
UNION SELECT 'BFB', 2
UNION SELECT 'BFC', 3
UNION SELECT 'BFD', 5
UNION SELECT 'IMA', 7
UNION SELECT 'ICU', 6
UNION SELECT 'SOB', 8
UNION SELECT 'SORU', 4
UNION SELECT 'NHERE', 9

select sym, rank
from ( select sym, rank from tMyPicks
union all
select sym, rank from tUrPicks) AS MyUnionTable
group by sym, rank
having count(*) <> 2
order by sym

DROP TABLE tMyPicks
DROP TABLE tUrPicks

This results in output that is a step away from what I want...that is, it at least identifes the individual symbols (and the associated ranking) that are NOT the same in the two tables.

IF there was a way to show which table the output of my union came from, I would be good to go, and thought I could add a literal to the select lists from each table in the UNION, but that destroys my group by clause.

Any thoughts? I suspect I will have to go away from my use of the UNION, but when I try using a join, I still have a problem with the grouping logic.

I suspect I am trying to be TOO dang "fancy" but at the moment I think I am too close to the forest to see the trees. :(I'd suggest:SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
ORDER BY 1
-PatP|||How about a slightly different tack...

select you.sym, you.rank , me.sym, me.rank
from tMyPicks me full outer join tUrPicks you on me.sym = you.sym
where (you.sym is null or me.sym is null)
or you.rank <> me.rank
order by you.sym

Sniped for using longer table synonyms ;-)|||Sniped for using longer table synonyms ;-)It's a rough neighborhood, what can I say?

-PatP|||YESSSSS!!!

You guys RULE!!! I KNEW I was making it too hard...and there you are, Pat...allowing me an opportunity to use my favorite function, COALESCE!!! *LOL*

Thanks much guys...I sincerely (as always) appreciate your time!!!|||Dang...so close...Actually, I left out an important aspect of my problem...one that throws a monkey wrench into the whole deal.

Alas, There can also be situations (typically at the lower end of the ranked list) in which the symbol is NOT in BOTH of the two tables. That's where my UNION was helpful...

That complicates the join, I know...if anyone has a quick adjustment, that would be much appreciated, otherwise I'll keep playing around with things. I adjusted the original post to show the data as it could be out there...

I did notice that my test data in my code above did not address this situation either...thank goodness for the ability to test against the live data ;)|||symbol not in both tables is covered by FULL OUTER JOIN

MCrowley's WHERE clause handles it well|||Then I'd use what is basically MCrowley's solution, something like:SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
OR m.sym IS NULL
OR u.sym IS NULL
ORDER BY 1-PatP|||Wow, and I still get to use COALESCE *LOL*

Seriously, thanks again for your help. It seems so plain and obvious once someone else writes it down (oh, and that "figures it out" part too. ;) )

Thanks anyway...perhaps someday I'll actually KNOW what I THINK I know.|||"Thanks anyway...perhaps someday I'll actually KNOW what I THINK I know."

Be aware that such a situation would result in your immediate and permanent banishment from the forum. :)|||It's a rough neighborhood, what can I say?

And I would not have it any other way.

The thought occurs to me, though, that the data should probably all be in a single table of picks. Then it would be easier to get a third, fourth, or fifth set of picks in the database. The query would change a little bit to something like:

SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM (select * from picks where picker = 'me') AS m
FULL OUTER JOIN (select * from picks where picker = 'you') AS u
ON (u.sym = m.sym)
WHERE m.rank <> u.rank
OR m.sym IS NULL
OR u.sym IS NULL
ORDER BY 1|||Well, I would also take that tack if'n it was up to me, and it fit with the plan ;) However, what y'all did not know (because, as you know, knowledge is power, and I only let on what I dared to let on...) is that I am just writing a stored proc that I can use to verify daily processing results in two different, but identical databases on different servers.

We have a production system that uses the Poor Man's Redundancy scheme...that is, two servers that, under the best of situations, contain databases that are *coff, coff* MIRRORS of each other, but are completely independent. Both start off with the same data in "identical" databases, and theoretically, after importing the "same" data from the "same" source each day, process independently (using the "same" stored procedures) and should, theoretically, arrive at the end of the processing day with exactly the same data in the respective databases.

That said, all the quotes should tell ya that it doesn't always happen that way. Sometimes an FTP or fails, and imported data isn't "identically" imported to each half of the mirrored system. What I am doing is trying to write a quick and dirty proc that checks one part of the end-result on each server, then compares the stock rankings to make sure both systems arrived at the same results at the end of the day.

If nothing else, this allows me to jump on the problem BEFORE the data gets out to the end users, and resolve any system/data issues that cause a disagreement between the two servers.

*phew* That said, I also neglected to show in my original code posting that there is also a DATE aspect of the select, so I had to play around with the code you kind gentlement provided yesterday in order to take that into account.

Here, for the sake of posterity (or is that posteriority?), is what I ended up with:CREATE TABLE tMyPicks (
myDate smalldatetime NOT NULL,
sym VARCHAR(6) NOT NULL,
rank INT NOT NULL
)

CREATE TABLE tUrPicks (
myDate smalldatetime NOT NULL,
sym VARCHAR(6) NOT NULL,
rank INT NOT NULL
)

INSERT INTO tMyPicks (mydate, sym, rank)
SELECT CONVERT(varchar(10), getdate(), 101), 'BFA', 1
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFB', 2
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFC', 3
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFD', 4
UNION SELECT CONVERT(varchar(10), getdate(), 101),'IMA', 5
UNION SELECT CONVERT(varchar(10), getdate(), 101),'ICU', 6
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SOB', 7
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SORU', 8
UNION SELECT CONVERT(varchar(10), getdate(), 101),'HERE', 9
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101), 'XBFA', 1
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFB', 2
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFC', 3
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFD', 4
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XIMA', 5
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XICU', 6
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSOB', 7
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSORU', 8
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XHERE', 9

INSERT INTO tUrPicks (mydate, sym, rank)
SELECT CONVERT(varchar(10), getdate(), 101),'BFA', 1
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFB', 2
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFC', 3
UNION SELECT CONVERT(varchar(10), getdate(), 101),'BFD', 5
UNION SELECT CONVERT(varchar(10), getdate(), 101),'IMA', 7
UNION SELECT CONVERT(varchar(10), getdate(), 101),'ICU', 6
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SOB', 8
UNION SELECT CONVERT(varchar(10), getdate(), 101),'SORU', 4
UNION SELECT CONVERT(varchar(10), getdate(), 101),'NHERE', 9
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101), 'XBFA', 1
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFB', 2
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFC', 3
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XBFD', 5
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XIMA', 7
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XICU', 6
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSOB', 8
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XSORU', 4
UNION SELECT CONVERT(varchar(10), getdate() - 1, 101),'XNHERE', 9

SELECT Coalesce(m.sym, u.sym) AS sym, m.rank AS myRank, u.rank AS urRank
FROM tMyPicks AS m
FULL OUTER JOIN tUrPicks AS u
ON ((u.myDate = m.myDate) AND (u.sym = m.sym))
WHERE (COALESCE(U.mydate, M.myDate) = '2005-01-24') AND
((m.rank <> u.rank) OR (m.sym IS NULL) OR (u.sym IS NULL))
ORDER BY 1

DROP TABLE tMyPicks
DROP TABLE tUrPicks

this results in output that I need, which is:

XBFD 4 5
XHERE 9 NULL
XIMA 5 7
XNHERE NULL 9
XSOB 7 8
XSORU 8 4

As always, thanks for your help!