Grabbing data from two tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tsm4781
Forum Commoner
Posts: 38
Joined: Wed Jul 09, 2003 7:17 pm

Grabbing data from two tables

Post by tsm4781 »

If I want to grab data from two tables is it...

SELECT * FROM table1, table 2 WHERE id = $id ??
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post by McGruff »

Do you mean get rows in each table with the same id value?

A JOIN query such as:

"SELECT t1.col1, t1.col2, t1.col3, .. etc .. , t2.col1, t2.col2 .. etc .. FROM table1 AS t1, table2 AS t2 WHERE t1.id = t2.id";
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Or similiar to McGruff's example:

Code: Select all

select 
  tablea.*, tableb.* 
from 
  tablea 
  inner join tableb on tablea.id = tableb.id 
where 
  tablea.id = '$id'
mcp
Forum Newbie
Posts: 5
Joined: Fri Aug 29, 2003 3:29 am

Post by mcp »

How would this work for grabbing all information when there is a 1:n relationship between the data?

The case I am thinking about is where there is an item table and a user table. Each item can have multiple users associated with it. The item table references the user table by ids. The user table has things like first name, last name, email, etc.

How do I grab all user's user data that is associated with a particular item?

I can do the join (like described earlier in this thread) to get one, but not all users...

Thanks!

mcp
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

This might be worth looking into:
http://jinxidoru.com/tutorials/union.html
Post Reply