"Leetcode Brush Problem" Sql-combine the Tables

Source: Internet
Author: User

Table:Person

+-------------+---------+| Column Name | Type    |+-------------+---------+| PersonId    | int     | | FirstName   | varchar | | LastName    | varchar |+-------------+---------+personid is the primary key, column for this table.

Table:Address

+-------------+---------+| Column Name | Type    |+-------------+---------+| Addressid   | int     | | PersonId    | int     | | City        | varchar | | State       | varchar |+-------------+---------+addressid are the primary key column for this table.

Write a SQL query for a report this provides the following information for each person in the person table, regardless if There is a address for each of those people:

Main topic:

There are two data tables: Person table and Address table. The person table primary key is the personid,address (address) Table primary key is Addressid, which is associated with the People table by PersonID.

Write an SQL query, for everyone in the person table, remove the FirstName, LastName, City, and state properties, regardless of whether their address information exists.

Problem Solving Ideas:

The subject is a simple even table query, with the person table as the main table, the Address table as a secondary table. Therefore, you can use the left associative query to query the tables.

About left (outer) association, Right (outer) association, Inner Association, and Outer Association query. See this blog: http://www.cnblogs.com/afirefly/archive/2010/10/08/1845906.html

In a word, the left side of the association is the left table, as long as the left table has data on the output, regardless of the right table has no data. The right association is reversed, and the table on the right is whichever. The internal correlation is that both tables have data to be queried. The external association is to be queried and displayed as long as any one of the tables has data.

So the subject uses left association.

SQL statements:

SELECT P.firstname, P.lastname, a.city, a.state
From the person p left joins Address a on A.personid=p.personid;

"Leetcode Brush Problem" Sql-combine the Tables

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.