Solved

# SQL Join

Posted on 2014-11-18
146 Views
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

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.

Thanks

nutnut
0
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
• 6
• 5
• 2
• +3

LVL 24

Expert Comment

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

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

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
``````
0

LVL 17

Expert Comment

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

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
``````
0

LVL 17

Expert Comment

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

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

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

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

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

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

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

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
``````
The expectation would be
A B C 1
X Y Z 2
then.
0

Accepted Solution

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

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
)
``````

Result:

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

LVL 69

Expert Comment

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

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

ID: 40664868
I selected my own solution because it works
0

## Featured Post

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.
###### Suggested Courses
Course of the Month1 day, 13 hours left to enroll