Sql Select Statements With Multiple Tables

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 s

Solution 1:

Select * from people p, address a where  p.id = a.person_id and a.zip='97229';

Or you must TRY using JOIN which is a more efficient and better way to do this as Gordon Linoff in the comments below also says that you need to learn this.

SELECT p.*, a.street, a.city FROM persons AS p
JOIN address AS a ON p.id = a.person_id
WHERE a.zip = '97299';

Here p.* means it will show all the columns of PERSONS table.

Solution 2:

You need to join the two tables:

select p.id, p.first, p.middle, p.last, p.age,
       a.idas address_id, a.street, a.city, a.state, a.zipfrom 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.

Solution 3:

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'

Solution 4:

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'

Solution 5:

select P.*,
A.Street,
A.City,
A.State
from Preson P
inner join Address A on P.id=A.Person_id
where A.Zip=97229Orderby A.Street,A.City,A.State