Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716

    Unanswered: 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:
    Code:
    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.
    Last edited by TallCowboy0614; 01-24-05 at 19:05.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd suggest:
    Code:
    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

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    How about a slightly different tack...

    Code:
    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 ;-)

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by MCrowley
    Sniped for using longer table synonyms ;-)
    It's a rough neighborhood, what can I say?

    -PatP

  5. #5
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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!!!
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    symbol not in both tables is covered by FULL OUTER JOIN

    MCrowley's WHERE clause handles it well
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Then I'd use what is basically MCrowley's solution, something like:
    Code:
    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

  9. #9
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Quote Originally Posted by Pat Phelan
    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:
    Code:
    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

  12. #12
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    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:
    Code:
    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:
    Code:
    XBFD	4	5
    XHERE	9	NULL
    XIMA	5	7
    XNHERE	NULL	9
    XSOB	7	8
    XSORU	8	4

    As always, thanks for your help!
    Last edited by TallCowboy0614; 01-25-05 at 14:16.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •