Multiple Table Query

jaZon

Ok, I'm going to TRY to explain this properly. I have 4 tables. 3 of the tables each contain different descriptors. Ex: Table_2 contains color_id and a name for that color. (1 = red 2 = green, etc.). Table_3 contains another descriptor like....rating_id and a name for that(1 = good, 2 = bad, 3 = worse). The third table is just the same.

NOW, Table_1 would have an id field and then three other fields containing the id's of descriptors from the other tables. So id 1 may have descriptors 1, 2, 4 - id 2 would have 1, 4, 3 and so on.

So, select * from table_1 where id = 1 would list everything in table_1 with id 1. That's easy enough. Now, how do I go about getting the descriptors that correspond to the numbers in the other three fields? Is there a way to do this in one query? Would I have to have 3-4 different queries? I am pretty lost at this point.

cosminb

Let me see if i got this right
Table 2: color_id, color_name
Table 3: rating_id,rating_name
Table 4: ....
Table 1: id,color_id,rating_id ...

So in order to get color_name and rating_name for id=1 in table_1 you could do something like this:

select a.color_name, b.rating_name from table_2 as a, table_3 as b, table_1 as c where c.color_id=a.color_id and b.rating_id=c.rating id

select table_2.color_name, table_3.rating_name where table_2.color_id=table_1.color_id and table_3.rating_id=table_1.rating_id

hope it's what you're looking for

stolzyboy

regardless if he is right or not, you will be joining tables when you select, you may have to figure what you want to select and stuff, but his general idea is correct

jaZon

Now, I understand how that works and why it works, but what I do not understand is how to get it to pull out all of those values for a certain id in that first table. I had played around w/ that idea, but I could not, for the life of me, figure out how to get it to pull the values for a certain id, in a certain table. ie. All the rows in table one that have id 2 for field 2 and so on...and have all the corresponding descriptors pull with them. Sure, I can pull all the rows in table one w/ id 2 for field 2, but how do you pull the descriptors from the other tables with it. As it is you can only pull every record from database.

sfullman

You just need to add one more item in your where clause, that's all, nothing complicated. If you want ID = 2:

select
a.color_name,
b.rating_name,
etc. (more fields here)
from table_2 as a, table_3 as b, table_1 as c
where
c.color_id=a.color_id and
b.rating_id=c.rating id AND
b.rating_id=2

the first two conditions are joins, the next condition is what you're looking for.

I am shooting from the hip and the field names might not be right, but that's the main point; a join of 3 tables will have two conditions; use more if you want to select a specific condition beyound that like some id =ing 2 for example.

Sam

jaZon

bah, i'm an idiot. thanks a bunch!