I have table1Name with data populated and table2 with no data populated.
select * from [database1Name].dbo.table1Name
on [database1Name].dbo.table1Name.fieldName like value;
After running the above sql statement it joins the tables but does not show any populated data from the table 'table1Name'.
Why does this happen?
JOIN which is an
INNER JOIN means that it will get you only data where the condition matches. So if the second table has not data, then the condition is never met, so you get no data in return.
In your case you need a
LEFT JOIN. This will get all the rows from the left table (table1Name in your case) and the corresponding values from the right table when the condition is met.
SELECT * FROM [database1Name].dbo.table1Name LEFT JOIN [database1Name].dbo.table2Name ON [database1Name].dbo.table1Name.fieldName like [database1Name].dbo.table2Name.fieldName;
Just to mention that using joins mean that you might get multiple times a single row from a specific table. For instance since you have a
LIKE condition, if
fieldName of Table 1 matches fieldName in 2 rows from Table 2 then you will get two rows containing the same row from Table 1 and the two rows from Table 2:
FieldName 1 2
FieldName OtherField 1 1 1 2
Result of LEFT JOIN
T1FieldName T2FieldName T2OtherField 1 1 1 1 1 2 2 NULL NULL