SQL JOIN and Compare - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL JOIN and Compare

Author

Topic

bagzli
Starting Member

17 Posts

Posted - 2012-01-16 : 14:46:15

Hello,

I have a little problem that I'm trying to solve and hoping you guys can help me out.

So I have a table that goes like this:


FieldA, FieldB, FieldC, Field D, Field E, FieldDate
AAAA    AA      A       AA       KLM      11/11/11
AAAA    AA      A       AA       DRB      11/11/11
AAAA    AA      A       AA       CTE      11/11/11
AAAA    AA      A       AA       A89      11/11/11
BBBB    AA      A       AA       CTE      11/11/11
BBBB    AA      A       AA       L33      11/11/11
BBBB    AA      A       AA       DRB      11/11/11
BBBB    AA      A       AA       KLM      11/11/11

Now there is a very long list of these, but whats important is that I have to compare all the Field1 that have AAAA versus Field1 that has BBBB. I need to see which FieldE code is missing. as you see some are the same but some are one off, I need it to show in one big table and have on the left side all AAAA info and on the right side all BBBB info. Reason for this is so i can look by eye and tell where the mistake is. So in the end it would look something like this:


FieldA, FieldB, FieldC, Field D, Field E, FieldDate, FieldA, FieldB, FieldC, Field D, Field E, FieldDate
AAAA    AA      A       AA       KLM      11/11/11   BBBB    AA      A       AA       KLM      11/11/11
AAAA    AA      A       AA       CTE      11/11/11   BBBB    AA      A       AA       CTE      11/11/11
AAAA    AA      A       AA       DRB      11/11/11   BBBB    AA      A       AA       DRB      11/11/11
AAAA    AA      A       AA       A89      11/11/11   BBBB    AA      A       AA       L33      11/11/11

Now if i look at this table, I can see that 4th line of code is where the mismatch happens

I've tried to the best of my knowledge to solve this but haven't had much luck.

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-16 : 16:09:55

what are you joining on?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-01-16 : 16:24:04

Does this help? I'm not sure how you'd want to match un-matched rows:

DECLARE @Table TABLE(FieldA CHAR(4), FieldB CHAR(2), FieldC CHAR(1), FieldD CHAR(2), FieldE CHAR(3), FieldDate DATETIME)

INSERT @Table VALUES
('AAAA', 'AA', 'A', 'AA', 'KLM', '11/11/11'),
('AAAA', 'AA', 'A', 'AA', 'DRB', '11/11/11'),
('AAAA', 'AA', 'A', 'AA', 'CTE', '11/11/11'),
('AAAA', 'AA', 'A', 'AA', 'A89', '11/11/11'),
('BBBB', 'AA', 'A', 'AA', 'CTE', '11/11/11'),
('BBBB', 'AA', 'A', 'AA', 'L33', '11/11/11'),
('BBBB', 'AA', 'A', 'AA', 'DRB', '11/11/11'),
('BBBB', 'AA', 'A', 'AA', 'KLM', '11/11/11')

SELECT  
    *
FROM
    (
        SELECT * FROM @Table WHERE FieldA = 'AAAA'
    ) AS A
FULL OUTER JOIN
    (
        SELECT * FROM @Table WHERE FieldA = 'BBBB'
    ) AS B
    ON A.FieldE = B.FieldE

bagzli
Starting Member

17 Posts

Posted - 2012-01-16 : 16:25:13

Well its AAAA versus BBBB and I need it to show Field E likes and differences when Fields B C and D match as shown in the second table of my first post.

Edit: There is suppose to be for every AAAA Field F a same field for BBBB and for some reason I have bunch of AAAA's FieldF missing. I need to determine which are missing.

So if there is 300 records of Field AAAA there should be 300 records of BBBB yet A only has like 100 out of the 300. I am trying to determine which ones are the ones missing.

Maybe if I can somehow print out all the 300 records and have them show up as NULL values when they are missing?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-16 : 22:44:19

are you sure that all the other fields b,c,d etc will have same values always? can they also vary?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bagzli
Starting Member

17 Posts

Posted - 2012-01-17 : 08:24:22

yes they can vary, I suppose i did not explain myself well. Fields b c and d are what identifies them. Its like those 3 fields are the variable name and i'm matching the values of same variables. Then when a variable does not has a pair, i need it displayed. Or simply pair up the ones that do have matches and then leave everything else bellow it.

@Lamprey

I don't want the rest of them matched, i want them displayed on the side they belong as records without a match for AAAA

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-17 : 08:37:04

What is wrong with Lamprey's solution?

No, you're never too old to Yak'n'Roll if you're too young to die.

bagzli
Starting Member

17 Posts

Posted - 2012-01-17 : 09:11:23

Only displays matched records and not the rest

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-01-17 : 09:20:46

quote:
Originally posted by bagzli

Only displays matched records and not the rest

Sure? It gave me this result

FieldA FieldB FieldC FieldD FieldE FieldDate               FieldA FieldB FieldC FieldD FieldE FieldDate
------ ------ ------ ------ ------ ----------------------- ------ ------ ------ ------ ------ -----------------------
AAAA   AA     A      AA     CTE    2011-11-11 00:00:00.000 BBBB   AA     A      AA     CTE    2011-11-11 00:00:00.000
NULL   NULL   NULL   NULL   NULL   NULL                    BBBB   AA     A      AA     L33    2011-11-11 00:00:00.000
AAAA   AA     A      AA     DRB    2011-11-11 00:00:00.000 BBBB   AA     A      AA     DRB    2011-11-11 00:00:00.000
AAAA   AA     A      AA     KLM    2011-11-11 00:00:00.000 BBBB   AA     A      AA     KLM    2011-11-11 00:00:00.000
AAAA   AA     A      AA     A89    2011-11-11 00:00:00.000 NULL   NULL   NULL   NULL   NULL   NULL

(5 row(s) affected)

No, you're never too old to Yak'n'Roll if you're too young to die.

bagzli
Starting Member

17 Posts

Posted - 2012-01-17 : 09:41:13

I keep getting an error that says there is an error in the FROM clause. Parser expected table_ref. I'm using SEQUEL ViewPoint. Any idea why?

From reading the code and his comment I assumed it wouldn't give the answers that don't gave match. If I can get this to work that should do the trick I think.

bagzli
Starting Member

17 Posts

Posted - 2012-01-18 : 08:25:28

so I've resolved my problem. The Sequel ViewPoint does not accepts Full outer joins, so i managed to do it somehow with a partial outer join and a union. Thanks for the help and ideas.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources