Solved

SQL Join

Posted on 2014-11-18
21
140 Views
Last Modified: 2015-03-14
Hi,

I have 2 tables.


TblA  Col1, Col2, Col3

TblB Col1, Col2, Col3

I need to join them BUT....

I need to return a row if ALL 3 Cols equal.

Col1= Col1
and Col2 = Col2
and Col3 = Col3

but

If ALL 3 Cols do NOT equal but

Col1 = Col1
and Col2 = Col2
and Col3 <> Col3

then return that row instead

So its sort of a double pass.  First pass is "do all 3 cols equal" if not "do Cols 1 & 2 equal", if not return nothing.

I am stumped, please help

Thanks

nutnut
0
Comment
Question by:nutnut
  • 6
  • 5
  • 2
  • +3
21 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Select *
From tblA as A
Join tblB as B
On A.col1 = B.col1 and
A.col2 = B.col2 and
A.col3 = B.col3
0
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
Second one:

Select *
From tblA as A
Join tblB as B
On A.col1 = B.col1 and
A.col2 = B.col2 and
A.col3 <> B.col3
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Maybe a UNION can give you a better view of what you want:
SELECT TblA.Col1, TblA.Col2, TblA.Col3, 'COL3 EQUAL'
FROM TblA, TblB 
WHERE  TblA.Col1= TblB.Col1 
 and TblA.Col2 = TblB.Col2 
 and TblA.Col3 = TblB.Col3
UNION ALL
SELECT TblA.Col1, TblA.Col2, TblA.Col3, 'COL3 NOT EQUAL'
FROM TblA, TblB 
WHERE  TblA.Col1= TblB.Col1 
 and TblA.Col2 = TblB.Col2 
 and TblA.Col3 <> TblB.Col3

Open in new window

0
 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
Depending on the SQL dialect, you may be able to do something like this:  

Select tblA.*, tblB.* , 3 as NumMatchedItems
from tblA inner join tblB ON  tblA.col1=tblB.col1 AND tblA.col2=tblB.col2 AND tblA.col3=tblB.col3
UNION ALL
Select tblA.*, tblB.* , 2 as NumMatchedItems
from tblA inner join tblB ON tblA.col1=tblB.col1 AND tblA.col2=tblB.col2
WHERE NOT EXISTS  
  ( select *
   from tblA A2 inner join tblB B2 ON  A2.col1=B2.col1 AND A2.col2=B2.col2 AND A2.col3=B2.col3  
  WHERE A2.col1=tblA.col1 and a2.col2=tblA.col2
 )

The second query ( everything after the Union All) will only find results if the first query has no matching item with the same vales for col1 and col2.  The "nummatcheditems" column is included as a check to see what is coming back when you are testing.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
Vitor's suggestion will always return the exact and inexact matches, which seems not to be intended.
A partial outer join might be the best way. However, it is not clear what exactly should be returned - the fields of TblA only, or COl3 of TblB instead or in addition?
select tblA.Col1, tblA.Col2, tblA.Col3, tblB.Col3
from tblA
left join tblB on tblA.Col1 = tblB.Col1 and tblA.Col2 = tblB.Col2 and tblA.Col3 = tblB.Col3
where tblA.Col1 = tblB.Col1 and tblA.Col2 = tblB.Col2

Open in new window

0
 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
I tested my suggested code above the above using this data:  

TBLA
a               b               c               1        
a               c               d               2        
b               c               d               3  
a              b              e              5

TBLB
a               b               c               1        
a               b               d               2        
a               c               e               3        
f               g               h               4    
a              b               e              5


and it finds an exact match between the two ABC  and ABE rows ; and a partial match between the two ACx rows; and it does not return a partial match between ABC and ABD because there is an exact match with ABC.

a               b               c               1               a               b               c               1               3
a               b               e               5               a               b               e               5               3
a               c               d               2               a               c               e               3               2


Is this the kind of behaviour you want ?
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 166 total points
Comment Utility
SELECT a.col1, a.col2, a.col3,
    CASE WHEN EXISTS(SELECT 1 FROM TblB b2 WHERE b2.col1 = a.col1 AND b2.col2 = a.col2 AND b2.col3 = a.col3)
         THEN 'Yes' ELSE 'No' END AS [Matching_B_Col3_Exists?]
FROM TblA a
WHERE
    EXISTS(
        SELECT 1
        FROM TblB b
        WHERE
            b.col1 = a.col1 AND
            b.col2 = a.col2
    )
0
 

Author Comment

by:nutnut
Comment Utility
Thanks.  

These examples are close to what I need but not quite there yet.

I need if col3 <> col3 then return the match on col1 = col1 and col2=col2
0
 

Author Comment

by:nutnut
Comment Utility
So sorry...bad day.  This SQL is 100% what I need..again very sorry..getting confused clearly need help!

DROP TABLE [dbo].[tblA]
GO
DROP TABLE [dbo].[tblB]
GO
CREATE TABLE [dbo].[tblA]
         ([Col1] [VARCHAR](50) NULL
         ,[Col2] [VARCHAR](50) NULL
         ,[Col3] [VARCHAR](50) NULL
         )
ON         [PRIMARY]

GO

CREATE TABLE [dbo].[tblB]
         ([Col1] [VARCHAR](50) NULL
         ,[Col2] [VARCHAR](50) NULL
         ,[Col3] [VARCHAR](50) NULL
         ,[Output] [INT] NULL
         )
ON         [PRIMARY]

GO
INSERT      [dbo].[tblA]
            ([Col1], [Col2], [Col3])
VALUES      (N'A', N'B', N'C')
GO
INSERT      [dbo].[tblB]
            ([Col1], [Col2], [Col3], [Output])
VALUES      (N'A', N'B', N'C', 1)
GO
INSERT      [dbo].[tblB]
            ([Col1], [Col2], [Col3], [Output])
VALUES      (N'A', N'B', NULL, 2)
GO

SELECT      *
FROM      dbo.tblA TA
SELECT      *
FROM      dbo.tblB TB

GO

--I would like to join the tables but return only Output 1 here


SELECT      TA.Col1
         ,TA.Col2
         ,TA.Col3
         ,TB.Output
FROM      dbo.tblA TA
            INNER JOIN dbo.tblB TB
            ON TA.Col1 = TB.Col1
               AND TA.Col2 = TB.Col2
               AND TA.Col3 = TB.Col3

               GO

--And output 2 here -

DELETE FROM dbo.tblB
WHERE Output = 1

SELECT      *
FROM      dbo.tblA TA
SELECT      *
FROM      dbo.tblB TB


               GO

SELECT      TA.Col1
         ,TA.Col2
         ,TA.Col3
         ,TB.Output
         ,'This should return Output 2 now.  I need sql to handle both situations in one pass'
FROM      dbo.tblA TA
            INNER JOIN dbo.tblB TB
            ON TA.Col1 = TB.Col1
               AND TA.Col2 = TB.Col2
               AND ISNULL(TA.Col3,'') = ISNULL(TB.Col3,'')

               GO
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
I'm not sure what you're trying to do with the final ISNULL comparison, but it doesn't look like it will do what you described in the initial posting. As I understand it, as long as Col1 and Col2 match, you want to see some matching results.  But the ISNULL expression  " AND ISNULL(TA.Col3,'') = ISNULL(TB.Col3,'')  "  will only return TRUE if Col3 is equal in both tables, or if it is NULL in both tables.

Did you try the code I posted earlier ? Maybe you could set up to work with your own test data and see what it gives.  You might want to change the part of the code which specifies the columns included in the output.  

If that doesn't give you the output that you need,  come back and describe the way in which the data that it returns is wrong, and that might give us something else to go on.
0
 

Author Comment

by:nutnut
Comment Utility
I need sql that

If it can get a join on 3 columns then return that row

elseif it cant get a join on 3 columns but can on 2 column return that row instead

if it can't join on 2 or 3 return nothing

It may be the case that it can join on 2 and 3 colums both returning different rows.  In this case the 3 column match wins and that 3 column match should be returned.

Thanks
0
 
LVL 17

Expert Comment

by:aflockhart
Comment Utility
That sounds like what the code that I posted does.   In my sample data:

Where TBLA and TBLB both contain (A,B,C) , the matching rows are returned
We don't match TBLA(A,B,C) with TBLB (A,B,D) because there is an exact match for A,B,C

Where TBLA and TBLB both contain (A,B,E) , the matching rows are returned
We don't match TBLA(A,B,E) with TBLB (A,B,D) because there is an exact match for A,B,E

Where TBLA contains (A,C,D) and TBLB contains (A,C,E) the partially matching rows are returned because there are no exact matching rows.
0
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
To have a better example, let's asume we have the following:
DROP TABLE [dbo].[tblA]
GO
DROP TABLE [dbo].[tblB]
GO
CREATE TABLE [dbo].[tblA]
         ([Col1] [VARCHAR](50) NULL
         ,[Col2] [VARCHAR](50) NULL
         ,[Col3] [VARCHAR](50) NULL
         )
GO

CREATE TABLE [dbo].[tblB]
         ([Col1] [VARCHAR](50) NULL
         ,[Col2] [VARCHAR](50) NULL
         ,[Col3] [VARCHAR](50) NULL
         ,[Output] [INT] NULL
         )
GO

INSERT      [dbo].[tblA]  ([Col1], [Col2], [Col3])           VALUES ('A', 'B', 'C')
INSERT      [dbo].[tblB]  ([Col1], [Col2], [Col3], [Output]) VALUES ('A', 'B', 'C', 1)
INSERT      [dbo].[tblB]  ([Col1], [Col2], [Col3], [Output]) VALUES ('A', 'B', NULL, 2)

INSERT      [dbo].[tblA]  ([Col1], [Col2], [Col3])           VALUES ('X', 'Y', 'Z')
INSERT      [dbo].[tblB]  ([Col1], [Col2], [Col3], [Output]) VALUES ('X', 'Y', NULL, 2)
GO

Open in new window

The expectation would be
  A B C 1
  X Y Z 2
then.
0
 

Accepted Solution

by:
nutnut earned 0 total points
Comment Utility
Hi All,

I have managed to solve here is the solution.  Thanks all for your help.  I wish I could give all 500 but fairest is split equally

DROP TABLE [dbo].[tblA]
 GO
DROP TABLE [dbo].[tblB]
 GO
CREATE TABLE [dbo].[tblA]
         ([Col1] [VARCHAR](50) NULL
         ,[Col2] [VARCHAR](50) NULL
         ,[Col3] [VARCHAR](50) NULL
         )
ON         [PRIMARY]

 GO

CREATE TABLE [dbo].[tblB]
         ([Col1] [VARCHAR](50) NULL
         ,[Col2] [VARCHAR](50) NULL
         ,[Col3] [VARCHAR](50) NULL
         ,[Output] [INT] NULL
         )
ON         [PRIMARY]

 GO
INSERT      [dbo].[tblA]
            ([Col1], [Col2], [Col3])
VALUES      (N'A', N'B', N'C')
 GO
INSERT      [dbo].[tblB]
            ([Col1], [Col2], [Col3], [Output])
VALUES      (N'A', N'B', N'C', 1)
 GO
INSERT      [dbo].[tblB]
            ([Col1], [Col2], [Col3], [Output])
VALUES      (N'A', N'B', NULL, 2)
 GO

SELECT      *
FROM      dbo.tblA TA
SELECT      *
FROM      dbo.tblB TB

SELECT      A.Col1
         ,A.Col2
         ,A.Col3
         ,COALESCE(B2.Output, B1.Output) Result
         ,B2.Output OutputB2
         ,B1.Output OutputB1
FROM      dbo.tblA A
            INNER JOIN dbo.tblB B1
            ON A.Col1 = B1.Col1
               AND A.Col2 = B1.Col2
               AND A.Col3 <> ISNULL(B1.Col3, '')
            LEFT JOIN dbo.tblB B2
            ON A.Col1 = B2.Col1
               AND A.Col2 = B2.Col2
               AND A.Col3 = B2.Col3


DELETE      FROM dbo.tblB
WHERE      Output = 1

SELECT 'Just run DELETE      FROM dbo.tblB WHERE      Output = 1 SQL to see if this SQL works below'


SELECT      A.Col1
         ,A.Col2
         ,A.Col3
         ,COALESCE(B2.Output, B1.Output) Result
         ,B2.Output OutputB2
         ,B1.Output OutputB1
FROM      dbo.tblA A
            INNER JOIN dbo.tblB B1
            ON A.Col1 = B1.Col1
               AND A.Col2 = B1.Col2
               AND A.Col3 <> ISNULL(B1.Col3, '')
            LEFT JOIN dbo.tblB B2
            ON A.Col1 = B2.Col1
               AND A.Col2 = B2.Col2
               AND A.Col3 = B2.Col3
0
 
LVL 17

Assisted Solution

by:aflockhart
aflockhart earned 334 total points
Comment Utility
I checked my proposed solution again using your suggested data:  I amended the fields being returned to match the ones that you show in your sample, and it gives the result you are looking for.  It will also work in the circumstances that the col3 values are both non-null and are different


Select tblA.col1, tbla.col2, tbla.col3,tblb.output
 from tblA inner join tblB ON  tblA.col1=tblB.col1 AND tblA.col2=tblB.col2 AND tblA.col3=tblB.col3 
 UNION ALL
 Select tblA.col1, tbla.col2, tbla.col3,tblb.output
 from tblA inner join tblB ON tblA.col1=tblB.col1 AND tblA.col2=tblB.col2 
 WHERE NOT EXISTS  
   ( select * 
    from tblA A2 inner join tblB B2 ON  A2.col1=B2.col1 AND A2.col2=B2.col2 AND A2.col3=B2.col3  
   WHERE A2.col1=tblA.col1 and a2.col2=tblA.col2
  )

Open in new window


Result:

col1      col2      col3      output
A      B      C      1
X      Y      Z      2
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
>> Scott's would need severe rework to allow for having Output in the result. <<

Huh?  I don't follow that.  If other column(s) are needed from TblB, just use an OUTER APPLY instead of a subquery.  I don't consider that a "major rewrite", but that is subjective of course :).

There was no mention of SELECTing additional columns from TblB in the original q.  I try to write queries to be as efficient as possible, so if a capability is not needed I don't add it to the code unnecessarily.


SELECT a.col1, a.col2, a.col3,
    b.col3, --if NULL, no match was found on col3
    b.output, --if NULL, no match was found on col3
    b.output2 --if NULL, no match was found on col3
FROM TblA a
OUTER APPLY (
    SELECT b2.col3, b2.output, b2.output2 --,...other col(s)...
    FROM TblB b2
    WHERE
        b2.col1 = a.col1 AND
        b2.col2 = a.col2 AND
        b2.col3 = a.col3    
) AS b
WHERE
    EXISTS(
        SELECT 1
        FROM TblB b
        WHERE
            b.col1 = a.col1 AND
            b.col2 = a.col2
    )
0
 
LVL 17

Assisted Solution

by:aflockhart
aflockhart earned 334 total points
Comment Utility
Scott, even your Outer Apply version does not pick up the value of the "Output" column in the case when there is a 'partial match'.  Running it against the OP's test data returns this:  ( after removing the 'output2' column reference which doesn't match his/her column definitions):

col1      col2      col3      col3      output
A      B      C      C      1
X      Y      NULL      NULL      NULL

Note that in the second row, output is NULL, where he/she wants it to be the value 2 from the partially matched row in TblB

EDIT : I just noticed that this is not running against OPs test data, but against a variation of it that I had used to test another possible data combination - where tblA.col3 is NULL.  But the point remains - with this data, it doesn't return the [output] value from tblB
0
 

Author Closing Comment

by:nutnut
Comment Utility
I selected my own solution because it works
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now