Menu
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?
809
In SQL we can retrieve data from multiple tables also by using SELECT with multiple tables which actually results in CROSS JOIN of all the tables.
In SQL, to fetch data from multiple tables, the join operator is used. The join operator adds or removes rows in the virtual table that is used by SQL server to process data before the other steps of the query consume the data.
Double-click the two tables that contain the data you want to include in your query and also the junction table that links them, and then click Close. All three tables appear in the query design workspace, joined on the appropriate fields. Double-click each of the fields that you want to use in your query results.
From multiple tables To retrieve information from more than one table, you need to join those tables together. This can be done using JOIN methods, or you can use a second SELECT statement inside your main SELECT query—a subquery.
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.
168
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
