0

If I have a database(sakila) with multiple tables, and I want to query multiple columns that relate to each other do I need to use keywords like

SELECT city.city, actor.first_name, actor.last_name
FROM city, actor, staff, address, inventory, film_actor, store
WHERE city.city_id = address.city_id AND
address.address_id = staff.address_id AND
staff.staff_id = store.store_id AND
store.store_id = inventory.store_id AND
inventory.film_id = film_actor.film_id AND
film_actor.actor_id = actor.actor_id

or can I just select them without linking the keys together like this:

SELECT city.city, actor.first_name, actor.last_name
FROM city, actor

EDIT:
So, since I want to see which cities the actors are from, I should use an inner join because a cross join will just match every city to every actor regardless if they actually relate?

Jeff Greene
  • 19
  • 5
  • 2
    Your first sample creates an implicit `INNER JOIN` between the tables, the second - `CROSS JOIN` (full Cartesian Product). – PM 77-1 Mar 15 '16 at 19:15
  • if you don't provide a linking condition, you'll end producing a cartesian product of the records, which can get massive. e.g. 1000 records in each table will produce 1,000,000 records in your result set. – Marc B Mar 15 '16 at 19:18
  • So, since I want to see which cities the actors are from, I should use an inner join because a cross join will just match every city to every actor regardless if they actually relate? – Jeff Greene Mar 15 '16 at 19:27
  • @JeffGreene Yes, the inner join will return the results you need. Also, here is a great answer when it comes to differences between types of joins. http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins – Eric S Mar 15 '16 at 20:11

2 Answers2

0

Yes. You will have to use a JOIN command.

Ex.

SELECT c.city, a.first_name, a.last_name
FROM city c 
INNER JOIN address ad ON c.city_id = ad.city_id 
INNER JOIN staff s ON ad.address_id = s.address_id
INNER JOIN store st ON s.store_id = st.store_id
INNER JOIN inventory i ON st.store_id = i.store_id
INNER JOIN film_actor fa ON i.flim_id = fa.film_id
INNER JOIN actor a ON fa.actor_id = a.actor_id
Eric S
  • 1,336
  • 15
  • 20
0

What do you mean by multiple columns that relate to each other? can you explain further, the normal way of making a select query is like this 

$sql= "Select column name FROM tablename ";

or be specific like

$sql="Select column name FROM tablename Where column name LIKE '%%' ";

you can make query with related fields by making another query for example $sql, $sql2 and so on.

Erza Scarlet
  • 21
  • 8