May 7, 2019 at 8:06 pm
Hello, I am trying to see all records, even those which do not have a match on table b. I tried full outer join but the results were the same. But there should be many who don't have an alt_id entered.
select a.*, b.full_name from alt_id_view a
inner join all_clients_view b
on a.people_id = b.people_id
---------------------------------------------------------------------
people_id Guid 16 255 255
id_number VarChar 50 255 255
effective_date DBTimeStamp 8 23 3
end_date DBTimeStamp 8 23 3
alternative_id_type_description VarChar 50 255 255
is_criminally_assigned Boolean 1 255 255
full_name VarChar 124 255 255
May 7, 2019 at 8:18 pm
Hello, I am trying to see all records, even those which do not have a match on table b. I tried full outer join but the results were the same. But there should be many who don't have an alt_id entered. select a.*, b.full_name from alt_id_view a inner join all_clients_view b on a.people_id = b.people_id --------------------------------------------------------------------- people_id Guid 16 255 255 id_number VarChar 50 255 255 effective_date DBTimeStamp 8 23 3 end_date DBTimeStamp 8 23 3 alternative_id_type_description VarChar 50 255 255 is_criminally_assigned Boolean 1 255 255 full_name VarChar 124 255 255
What's your question?
What's your query?
Is that supposed to be table DDL? There is no such data type as Boolean.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 7, 2019 at 8:28 pm
Try 'left outer join' instead of 'inner join'
May 7, 2019 at 8:47 pm
So this should be fine? select a.*, b.full_name from alt_id_view a
left outer join all_clients_view b
on a.people_id = b.people_id
It's only bringing in the matched so it could be the system we are on.
May 7, 2019 at 8:58 pm
So this should be fine? select a.*, b.full_name from alt_id_view a left outer join all_clients_view b on a.people_id = b.people_id It's only bringing in the matched so it could be the system we are on.
It should return all rows in alt_id_view and those from all_clients_view which match on people_id.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 8, 2019 at 11:32 am
So this should be fine? select a.*, b.full_name from alt_id_view a left outer join all_clients_view b on a.people_id = b.people_id It's only bringing in the matched so it could be the system we are on.
Do you also have a WHERE clause? If you put the filter criteria for the table in the WHERE clause, it acts as a INNER join again. Move the criteria to the ON clause and you should see different data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 8, 2019 at 1:36 pm
Reading your original post again and assuming something from the view names, I suspect that you want to swap the tables and put all_clients_view first. I am guessing that you want to see all clients, even those without an alt_id. Swapping the tables will give that result.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply