Write an SQL query to report the city of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.
Question
Write an SQL query to report the city of each person in the Person table. If the address of a personId is not present in the Address table, report null instead.
Solution
Sure, here is the SQL query you need:
SELECT Person.PersonId, Person.Name, Address.City
FROM Person
LEFT JOIN Address
ON Person.PersonId = Address.PersonId;
This query will return a list of all persons along with their cities. If a person doesn't have an address in the Address table, it will return null for the city.
Here's how it works:
-
SELECT Person.PersonId, Person.Name, Address.City: This line is selecting the columns we want to display in our final result. We want the PersonId and Name from the Person table, and the City from the Address table. -
FROM Person: This line is specifying the main table we are working with, which is the Person table. -
LEFT JOIN Address: This line is joining the Address table to the Person table. A LEFT JOIN is used to return all the records from the left table (Person), and the matched records from the right table (Address). If there is no match, the result is NULL on the right side. -
ON Person.PersonId = Address.PersonId: This line is specifying the condition for the join. We are joining on PersonId, which means we are matching records where the PersonId in the Person table is the same as the PersonId in the Address table.
Similar Questions
Combine Two TablesTable: PersonColumn NameTypePersonIdintFirstNamevarcharLastNamevarcharPersonId is the primary key column for this table.Table: AddressColumn NameTypeAddressIdintPersonIdintCityvarcharStatevarcharAddressId is the primary key column for this table.Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:FirstNameLastNameCityStateOptionsselect FirstName, LastName, City, State from Person as p left join Address as a on PersonId = PersonId;select FirstName, LastName, City, Statefrom Person as p left join Address as a on p.PersonId = a.PersonId;select FirstName, City, State from Person as p left join Address as a on p.PersonId = a.PersonId;select FirstName, LastName, City, State as p left join Address as a on p.PersonId = a.PersonId;
Write a statement that will select the City column from the Customers table.
To display number of Recipients from each City. Also write the output
Write a query to display the owner_name, address and contact_number of the buildings which does not have an email_address. Display the records in ascending order based on their owner_name.
9. There are two tables, a users table with demographic information and the neighborhood they live in, and a neighborhood table. Please write a query that returns all of the neighborhoods that have 0 users.
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.