[RESOLVED] query select from 3 tables at once

[Justa Lol]

i got 3 tables that i would like to link to each other... what i need is to select where 'tid' from table3 is equal to 'id' on table2, and from table2 i need to select where 'cid' on table2 is equal to 'id' on table1, now this is pretty confusing so i can't really figure it out because i don't really know how to select from multiple tables at once...

[techgnome]

What you are looking for is the JOIN clause.... INNER JOIN means to pull rows, only where data exists in both tables. (there's others, like LEFT and RIGHT, and OUTER, which shouldn't be too hard to look up if you need them).

SELECT [put your fields here]
FROM table3 t3
INNER JOIN table2 t2
ON t3.tid = t2.id
INNER JOIN table1 t1
ON t2.cid = t1.id
WHERE
[put your where criteria here]


-tg

[Justa Lol]

thanks for the reply, i will try this later because i don't have time right now, i will update on you.

edit: could someone help me with the "WHERE" because i can't really figure that one out...

i think i need to clear this a bit more up, i need to select everything within the 3 tables and do something with the ids... i won't give more information unless its required to help me on the code, because i want to write as much as possible of this project for my self.. so i can actually learn something..

[SambaNeko]

What to put in the WHERE clause depends on what conditions you want to match. If you really want everything, then you want "no conditions" I suppose, in which case, omit the WHERE:

SELECT *
FROM table3 t3
INNER JOIN table2 t2
ON t3.tid = t2.id
INNER JOIN table1 t1
ON t2.cid = t1.id

It's best to avoid SELECT * if you don't actually need it.

[Justa Lol]

well the problem i have is to get the details...

edit: i give up in this "join" i'll just make everything simplier to add the cid to table3 instead of going through all the maths...

[SambaNeko]

There isn't any "math" involved... you're just matching up 2 (or more) tables on columns that they have in common. You can make the SQL less verbose with

SELECT *
FROM table1, table2, table3
WHERE table1.id=table2.cid
AND table2.id = table3.tid

Though this isn't exactly the same as techgnome's query, it may work for you and may be easier to understand?

[Justa Lol]

well in what i'm trying to do, there is alot of maths, i need to calculate a few stuff and make them show, not everything...

and i got what i needed, thanks for the replies.

plus the machine prompts in binary... xD

[techgnome]

Yes and no.... some DBMSs may not recognize that format, and to be honest, I really dislike that format as it makes the query convoluted and harder to read where the joins are. Plus if you need to turn an INNER Join into a LEFT join for testing... it's much easier using the verbose format. (which I might add is ANSI SQL-92 Standard, which, theoretically supersedes the alternate version - if I remember correctly).

-tg

[SambaNeko]

I don't mind that format, it's a bit more intuitive to me, and I've not yet used a DBMS where it didn't work. But I realize those are all personal reasons, and I know the JOIN syntax has its advantages. Just brought it up in case it was conceptually clearer.

[visualAd]

I don't mind that format, it's a bit more intuitive to me, and I've not yet used a DBMS where it didn't work. But I realize those are all personal reasons, and I know the JOIN syntax has its advantages. Just brought it up in case it was conceptually clearer.

Some DBMS' will make optimization decisions based on whether or not you use a join or a where clause, so watch out.

Also I cannot think of any good reason why you would want to have a join with no condition. Joining 3 tables each with 20 rows and no join condition would result in a record set containing 8000 rows.

[Justa Lol]

Some DBMS' will make optimization decisions based on whether or not you use a join or a where clause, so watch out.

Also I cannot think of any good reason why you would want to have a join with no condition. Joining 3 tables each with 20 rows and no join condition would result in a record set containing 8000 rows.

that would make it load extremely slow, therefore i made the easiest way, just have the uid and cid in 1 table, clever right?

[SambaNeko]

just have the uid and cid in 1 table, clever right?

Redundant and a cop-out. :/ It will not always be a simple matter to just copy a column over to another table, and relationally speaking there may be no good reason to duplicate that column either. Sooner or later, you're going to need joins.

[Justa Lol]

well, so far no errors doing it, i simply replaced the tables so the cid would not go into table2 but into table3...

and sorry it was tid and cid(uid was there already).

well, basically, tid on table3 is the id on table2 and cid on table2 is the id on table1, so it still works putting cid on table3.

the reason i did it is because i would need it on there anyways, so its ready to be there already.