Given the following two tables:
Person table
id (pk)
first
middle
last
age
Address table
id(pk)
person_id (fk person.id)
street
city
state
zip
How do I create an SQL statement that returns all information for people with zip code 97229?
This question is related to
sql
Like that:
SELECT p.*, a.street, a.city FROM persons AS p
JOIN address AS a ON p.id = a.person_id
WHERE a.zip = '97299'
select P.*,
A.Street,
A.City,
A.State
from Preson P
inner join Address A on P.id=A.Person_id
where A.Zip=97229
Order by A.Street,A.City,A.State
First select all record from person table, then join all these record with another table 'Address'...now u have record of all the persons who have their address in address table...so finally filter your record by zipcode.
select * from Person as P inner join Address as A on
P.id = A.person_id Where A.zip='97229'
You need to join the two tables:
select p.id, p.first, p.middle, p.last, p.age,
a.id as address_id, a.street, a.city, a.state, a.zip
from Person p inner join Address a on p.id = a.person_id
where a.zip = '97229';
This will select all of the columns from both tables. You could of course limit that by choosing different columns in the select
clause.
Source: Stackoverflow.com