I have a query that I built via the query builder tool.
It joins 2 tables A and B. Table A is a contact list. B is phone number types.
I joined the 2 tables and select all recored from A.
The query has a condition on B where the phone number type is “CELL”.
Not every record in A is in B.
I expected to return results that had all the records in A and the CELL Phone populated for the contacts that had Cell Phones.
However I get only records where type CELL exists in table B.
I am a little rusty with SQL and would like to do this all throught the QUERY tool.
Suggestions are much appreciated.
Jim
Try using the different join types.
outer
inner
left
right
full
Some of these can be generated by right clicking on the join in the Query Builder. Se attached. Others you will have to type into the Query Tab.
Debbie
Thanks
The issue occurs when I add a filter to the child table like region = “North”.
Even if I have the all records from address it still only returns where the Region = north.
I need to have the region return as null where it is not populated.
My live example occurs with a refrence table that has different values for different languages and altough some values are populated others are not for a given record in the paretn table.
When I select a data type of say ENGLISH in the child table it forces a filter that selects all records from the parent and where the data type is found.
Hope this is a good explanation.
Jim
Jim, the WHERE clause filters the query after the join so you will only see rows that match the condition in the WHERE clause. If you want rows with null values you must include an OR WHERE clause that specifies IS NULL. Please see the attached image.
Jim,
You need to move the predicate for the phone type to the JOIN condition instead of the WHERE condition. So in my simple example, I’m doing a left join (return all contacts regardless of whether or not they have a phone record matching the criteria) that filters based on phone type as well as some matching key value.
IF I were to instead move the “AND phone.phone_type = ‘CELL’” to the WHERE clause, the database would perform the join, and then limit the resulting data set to only those records that had a phone type of ‘CELL.’ Basically it turns your outer join into an inner join.
Hope this helps, Jeff
SELECT
contact.last_name,
contact.first_name,
phone.phone_type,
phone.number
FROM
contact
LEFT JOIN phone
ON contact.contact_id = phone.contact_id
AND phone.phone_type = ‘CELL’
WHERE
contact.state = ‘CA’
