How to modify an inner join search query to return all fields even if a table is null

[Robert Vida]

Hello, my question has to do with inner joins and nulls.

The question is, how would i need to modify an inner joins search query to return all fields even if a table is null. So basically, if I have two tables that have matching values and a third table that is completely null or doesn't have those values, I still want it to return those matching values.

I also don't have any experience with this so please describe it as thoroughly as possible.

Thanks in advance for the help!!!

[benwizzle]

I think I had a similar need before with what your asking. I needed the results of an inner join on two tables but wanted to get matches with a third table and still get results even if there was no matches from that third table.
If I am understanding you correctly then you would inner join the first two tables and then LEFT JOIN the third table. For example. Let say Table1 is General, Table 2 is Services, and Table3 is ServiceGoals.(T his is the example i had to use that i am referring to.)
Then your query would look like this.

SELECT * FROM General INNER JOIN Service ON General.ID = Service.ID LEFT JOIN ServiceGoals ON General.ID = ServiceGoals.ID

This will return the matches from the first two tables and then join the third even if there are no matches. So therefore you will have all the rows of the first two tables but if there was no match with the third table you will have columns with no values and ones with matches will have values.

W3Schools has a nice example and explanation of joins. Here is a link to that as well.

http://www.w3schools.com/sql/sql_join.asp

I hope this is what you were looking for.

[Jerry Winston]

After reading and rereading your post I think I have a solution for you.

You are performing two operations on four datasets, TableA, TableB, TableAB(INNER JOIN),TableC.

We start with TableA, TableB, and TableC available to us. We need to create dataset TableAB
by INNER JOINing tables TableA and TableB as our first operation.

SELECT A.[column1],B.[column2] FROM
TableA A
INNER JOIN
TableB B
ON
A.joinField = B.joinField

This produces dataset TableAB. Now we can proceed to our second operation, retrieving all rows
from TableC and appending them to the dataset TableAB.

SELECT column1,column2 as [ccolumn12] FROM TableAB
UNION ALL
SELECT [ccolumn7] as [column1],[ccolumn12] FROM TableC

To successfully use the UNION ALL, you will need to make sure the column names in TableC match with the column names in the dataset TableAB. In addition to matching column names you will have to match data types as well. You can accomplish this easily by assigning incongruous
fields an homogenizing alias. In the code above I give [ccolumn7] the alias [column1] which matches the field from the TableAB dataset. The second alias in the code sample assigns column2 from the TableAB dataset the alias [ccolumn12] which matches the field name from the TableC dataset.

Now that we have all the pieces we can put this together in one big script.

SELECT A.[Column1],B.[column2] as [ccolumn12] FROM TableA A
INNER JOIN TableB B ON A.joinField = B.joinField

UNION ALL
SELECT [ccolumn7] as [column1], [ccolumn12] FROM TableC

This script combines rows from INNER JOINed datasets TableA and TableB with any and all rows from dataset
TableC.

Let me know how this works out for you!

[NeoPa]

This is a perfect example of why we don't allow threads to be hijacked. Robert has asked a perfectly reasonable question, but Akhos seems to think this is his thread all of a sudden. I will do what I can to sort out the mess. In the mean-time, please ignore any further comments that are not on (original) topic. When the new thread is up and running Akhos will have his own thread in which he can continue his conversation.

It may take a while so please be patient.

The other thread can now be found at Full Outer Joins.

[NeoPa]

Robert,

If you mean the third table has a matching field, but will not always have matching data, then you are probably looking for a LEFT OUTER JOIN between the first set of tables (TableAB in b0010100's scenario). If not, and you're looking for something else then perhaps a clarification from you would be in order at this point.