Solved

SQL Join

Posted on 2014-11-18
21
146 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +3
21 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40450223
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
ID: 40450227
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 50

Expert Comment

by:Vitor Montalvão
ID: 40450230
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
How To Reduce Deployment Times With Pre-Baked AMIs

Even if we can't include all the files in the base image, we can sometimes include some of the larger files that we would otherwise have to download, and we can also sometimes remove the most time-consuming steps. This can help a lot with reducing deployment times.

 
LVL 17

Expert Comment

by:aflockhart
ID: 40450274
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 70

Expert Comment

by:Qlemo
ID: 40450294
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
ID: 40450311
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:Scott Pletcher
Scott Pletcher earned 166 total points
ID: 40450779
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
ID: 40451959
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
ID: 40452021
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
 
LVL 17

Expert Comment

by:aflockhart
ID: 40452049
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
ID: 40452061
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
ID: 40452093
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 70

Expert Comment

by:Qlemo
ID: 40452178
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
ID: 40452185
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
ID: 40452294
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:Scott Pletcher
ID: 40452466
>> 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
ID: 40452504
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
ID: 40664868
I selected my own solution because it works
0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

717 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