Multiple tables

Anon

When retrieving information from a database with more than one table, how does one word the MySQL queries? I've broken my database up into 3 tables, but can't figure out how to link them together in the code.

Thanks

Anon

link them up with a query..
something like this

table1 (nr, name)
table2 (nr, adress)

query

select table1.name table2.adress from table1, table2 where table1.nr = table2.nr;

now the possible returned array will contain the name from table1 and adress from table2, both from id nr ..

alanl

Depends on what you want!

If you've got 2 tables and you want only the rows that exist in both, you can:

SELECT stuff FROM table1, table2 WHERE table1.id = table2.id

or if you want all the rows from table1 with just the matching table2 rows (and blanks where there is no match), you can:

SELECT stuff FROM table1 LEFT JOIN table2 ON table1.id = table2.id

Hope that helps!

Anon

But how can an id in table1 = and an id in table2? I think that is where I'm getting confused. Because I thought each table should have 1 unique id that identifies everything in that particular table.

Thanks

Anon

I'm not quite sure what 'nr' is supposed to represent. Could you explain it a little more for me? How can 'nr' be the same in both tables?

Thanx

pinoyvibes

i'm not sure if i understood you right but if i did the select would look something like this..........

mysql> select p.patient_lastname, p.patient_ssn, w.password, r.appointment_date, r.payment_date
-> from patients p, passwords w,
records r
-> where p.patient_ssn = w.patient_ssn and p.patient_ssn = r.patient_ssn;

comparing the patient ids in all three tables. if this is not what you mean then disregard this message.

Anon

Pinoyvibes,

This might sound stupid, but what does that select statement actually do? I'm not quite following the flow of the SQL you wrote.

Thanks,
Chris

sepodati

You've got some learning to do yet. Read this:

http://www.phpbuilder.com/columns/barry20000731.php3

---John Holmes...

Anon

You're right, each table should have a unique id to identify the row. But, when you have two related tables, you store the id of the related row (that lives in table 1), in the row in the other table (ie table2).

So table1 has it's unique id. Table2 has a unique 2 but also stores the id of the related row from table1.

Make sense?

Cheers,
Alan