CODEWITHSUNDEEP
sqlsql-serversql-server-2008joininner-join
user3633538
user3633538

Reputation:

SQL Inner join on one field when second is null and on second when first is null

OP is completely new to databases and SQL, so this question may be answered somewhere else but I don't quite have the vocabulary to find what I'm looking for; a push in the right direction would be great.

I'm trying to make a view of two tables inner joined. This is currently how they look: enter image description here

and this is how I would like them to look: enter image description here

The issue with this is that the view is empty because c4 and c5 can be null values.

I essentially want these two later inner joins on c4 and c5 to happen if one of them has a value.

Just to be thorough:

  1. If c4 exists, inner join on it.
  2. If c5 exists, inner join on it.
  3. If neither exist, don't inner join on it.

Each of these are preceded by the inner joins between UTC and colNum. By this I mean that the UTC and colNum joins always happen.

I know that sql is a query language, so it does not do computation, but there must be a filter that will allow for this logic to be applied to these two tables.

It is useful to note that if c4 exists, c5 is null and if c5 exists, c4 is null, and that I still want a row (joined based on the previous two inner joins) if both are null.

Again, I don't really know the language surrounding SQL, so my effort to find an answer before asking one was hampered. If something like this has already been answered, just point me to it.

Upvotes: 5

Views: 14650

Answers (4)

Derek
Derek

Reputation: 41

I re-read your request. A union query is the appropriate solution. Something along these lines should work well.

select  *

from
    (
        select
            t1.primaryKey,
            t2.foreignKey
        from
            table1 t1 inner join table2 t2 on t1.c1 = t2.c1

        union

        select
            t1.primaryKey,
            t2.foreignKey
        from
            table1 t1 inner join table2 t2 on t1.c2 = t2.c2
    ) x inner join table1 t1 on x.primaryKey = t1.primaryKey
    inner join table2 t2 on x.foreignKey

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

It is a little big statement to do it in comment so I will post it as an answer. If my understanding of the problem is correct then it will be like:

select * 
from sizeconditionstable t1
join specalloytable t2
on (t1.c4 is not null and t2.c4 is not null and t1.c4 = t2.c4) or 
   (t1.c5 is not null and t2.c5 is not null and t1.c5 = t2.c5)

Edit:

select * 
    from sizeconditionstable t1
    join specalloytable t2
    on (t1.utc = t2.utc and t1.colnum = t2.colnum) and
       ((t1.c4 = t2.c4) or (t1.c4 is null and t2.c4 is null)) and
       ((t1.c5 = t2.c5) or (t1.c5 is null and t2.c5 is null))

This is the version which will join always on utc and colnum and also on c4 and c5 if they are filled in both tables.

Upvotes: 5

Derek
Derek

Reputation: 41

coalesce(column1, column2, '') will work, however, it isn't cheap so if you are joining two large tables you would be better off with a different approach.

Upvotes: 1

HLGEM
HLGEM

Reputation: 96552

I think a UNION ALL query is your best bet here.

SELECT field1, field2, field3 
FROM table1 t1
JOIN table2 t2 ON t2.field1 = t1.field1 AND t2.field2 = t2.field2
UNION ALL
SELECT field1, field2, field3 
FROM table1 t1
JOIN table2 t2 ON t2.field1 = t1.field1 AND t2.field3 = t2.field3

In this case, if field 2 is null and field 3 is not, then there are no results from the first query but the second one brings in the results

Upvotes: 1

Related Questions