Bringing data from multiple tables

[dinkeshpatel]

Hi

I was wondering if anyone could help me. I need to retreive data from multiple tables and diplay it onto a webpage. The data retrieved should relate to only one user, i.e. all records from the tables where user_id is 1.

I would be very gratefull if someone could help me out with this.

Thanks Dinkesh

[van__]

select * from this_table, that_table WHERE this_table.user_id = 1 AND that_table.userId = this_table.userId

[Anon]

Say you have two tables (to keep things simple), called table1 and table2. Both have a user id field called "user_id" (they don't have to have the same name, but it's often a good move).

We want fields "this", "that" and "theother" from table1, and "foo", "bar", "baz" from table2, for a given user_id, 42, say in this example).

"SELECT A.this, A.that, A.theother, B.foo, B.bar, B.baz FROM table1 A, table2 B WHERE A.user_id=42 AND A.user_id=B.user_id"

I'm using A and B as abbreviations so I don't have to write "table1" and "table2" respectively all the time.

The full stop can be used to indicate which table a field is to be taken from - the field "fieldname" in table "tablename" is referred to as "tablename.fieldname"

Note the bit after the AND - this ensures that only user 42's data is extracted from table B as well.

From here on things can only get hairier 🙂