开发者

PHP MySQL combine tables question, reading the fetch

I made a query that is joining the same table - users- twice, with JOINs.

SELECT  * from tbl开发者_高级运维1 
  JOIN  tbl2 ON tbl2.USER_ID = tbl1.ID 
  JOIN  tbl2 as alias ON tbl1.ID = tbl2.TEACHER_ID 
 WHERE  tbl1.ID = 45

The first call to that table is regular, and the second time I use an alias using AS. When I get the result in phpMyAdmin, I see the results are not referred to as the alias, but with the usual name, that's like this:

USERNAME => Ted, ID_NUMBER=>33322552, ... ,USERNAME=>Josh ....

How can I read that associative array


In this query, you are not using an alias, you are refering to a real table called tbl2, twice. This will not work.

SELECT * from tbl1 
JOIN tbl2 ON tbl2.USER_ID = tbl1.ID 
JOIN tbl2 as alias ON tbl1.ID = tbl2.TEACHER_ID   
//                              ^^^^ there's no alias here, it's still tbl2 
WHERE tbl1.ID = 45

Rewrite it to:

SELECT * from tbl1 
JOIN tbl2 AS t2A ON t2A.USER_ID = tbl1.ID 
JOIN tbl2 AS t2B ON tbl1.ID = T2B.TEACHER_ID 
WHERE tbl1.ID = 45

The as keyword is optional.
This way there can be no misunderstanding which version of tbl2 you are refering to.

The difference between the two queries

Note that you can also rewrite query 2 as:

SELECT * from tbl1 
JOIN tbl2 ON (tbl2.USER_ID = tbl1.ID) OR (tbl2.teacher_ID = tbl1.id)  
WHERE tbl1.ID = 45

In that case you would rewrite query 1 as:

SELECT * from tbl1 
JOIN tbl2 ON (tbl2.USER_ID = tbl1.ID) AND (tbl2.teacher_ID = tbl1.id)  
WHERE tbl1.ID = 45

See the difference?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜