Issue with joins

1

Hello

I am trying to do the following join but it returns null, what could be the issue?

SELECT * FROM Table1
LEFT JOIN Table2
ON Col1 = Col2
LEFT JOIN Table3
ON Col4 = Col5
RIGHT JOIN Table4
ON Col6 = Col7;

Thanks!

2

ON Col1 = Col2

ON Col4 = Col5

ON Col6 = Col7

the data is "not" there which matches
example
col1 = 123 col2 = 666 col1 <> col 2
all data is like this

3

You need to prefix the columns with the appropriate table name / alias. We have NO idea which table each specific column is in. For example:

SELECT * FROM Table1
LEFT JOIN Table2
ON Table1.Col1 = Table2.Col2
...and so on for all the JOINs...

4

Thanks, here is the full code:

SELECT * FROM Table1
LEFT JOIN Table2
ON Table1.Col1 = Table2.Col2
LEFT JOIN Table3
ON Table2.Col4 = Table3.Col5
RIGHT JOIN Table4
ON Table2.Col6 = Table4.Col7

Any idea why only Table4 values are shown and all the rest table has null values?

5

Concept of left or right join

If data matches get it
If data does not match then nulls

Left join means
data in left table data in right table match then
123 123
Data in left table not there in right table
123 null

6

You're doing a LEFT JOIN on Table2. That means a row matching Table1 might not be found in Table2. In that case SQL automatically sets all Table2 column values to NULL (Table1 columns still have all their normal values).

Then when you do a RIGHT JOIN on Table4, you use a column from Table2. If that column is NULL, the "=" condition can never be true (NULL is never "equal to" anything). Thus, SQL would drop the row from the result, since it can't satisfy the join.

7

thanks, could you tell me please how it should be?

I want to start with Table4.Col7.
Then, left join Table1.Col1. However some Table1 values will be zero because not all Table4.Col7 may exist in Table1.Col1.

Then, in the above joined table, I want to also join Table2.Col2. However, I am afraid that the null values of the firstly joined table will be matched to the null values of Table2.Col2 which will be wrong!

How can I overcome this?

Thanks!

8

NULL values will never match. You don't need to sorry about that. For example:
SELECT CASE WHEN NULL = NULL THEN 1 ELSE 0 END

9

Thanks, I think I found the problem. Some fields that I am joining have common values but they are formatted differently, e.g. as numbers or as texts.
What is the neatest way to fix this?

I was thinking to insert a CAST statement between the joins but it does not work.
It would also help if somehow SQL would match the values and choose automatically the right format.

Any idea?

10

SQL will automatically chose whichever format has higher precedence. Between a string and any number, the number has a higher precedence, so the string will be converted to a number and then the columns will be compared as numbers.

11

Thanks but it still does not work.

I tried the following but it doesn't fix it either:

SELECT * FROM Table1
LEFT JOIN Table2
ON Table1.Col1 = Table2.Col2
LEFT JOIN Table3
ON CAST(Table2.Col4 AS int) = CAST(Table3.Col5 AS int)
RIGHT JOIN Table4
ON Table2.Col6 = Table4.Col7

Any idea please?

12

What are you trying to do. I mean expecting results

From that solution

Depends on data and logic

13

I found the error! There were commas in the numerical values which I thought were treated as number but instead they were literal text commas.

I will need to delete the commas in the values of Col1. From what I found, I need to use REPLACE but not sure how to construct the argument to fit only for a specific column and also to fit along the three consecutive JOINs that I have.

Any hint? Thanks!

14

You need to provide sample data for each table. Comma delimited values in one column sounds like a major design flaw you will need to fix. Until you provide sample data its just shooting in the dark, we cant help you

15

LEFT join on
replace(Table1.Col1,',','') = = Table2.Col2

16

That may not be the correct approach - this string (text) column may contain a list of items separated by a comma. If that column is supposed to contain a numeric value then it should be defined as a numeric value and you would not have this problem - which leads me to believe that whoever designed that table set it up to contain multiple values in a list.

17

this might not work as there will be multiple values that will now become

1,2,3,4 to 1234
and we do not know what is in the joining column Col2. Unless you can the following but we have no idea what your data looks like

use sqlteam
go

create table #commavalues(kamboocha nvarchar(50))

insert into #commavalues
select '1,2,3,4'


create table #boomshaka(id int , name nvarchar(50))

insert into #boomshaka
select 1, 'Darth Vader' union
select 2, 'Yoda' union
select 3, 'Look Skywaler' union
select 4, 'Princess Layha' union
select 5, 'The tiny cute bears'

select *
 from #boomshaka a
 join  #commavalues cv
	  cross apply string_split(cv.kamboocha,',') ss
 on a.id = ss.value

 drop table #commavalues
 drop table #boomshaka