如何在一个表中使用条件在两个表中获取记录 - PHP和Mysql

I have two table like

我有两张桌子

table: t1
id|name|type
1 | a  |0
2 | b  |1
3 | c  |0
4 | d  |0
...

table: t2
uid|type
4  |1
3  |0
...

I want to count or get records in table t1 have type = 1 and records have id = uid in table t2 has type = 1
What is the best way to count or get record in one query like

我想计数或获取表t1中的记录有type = 1并且记录在表t2中有id = uid有type = 1在一个查询中计算或获取记录的最佳方法是什么

$type = 1;
$count = mysql_num_rows(mysql_query("Select * .... Where type = $type ")); //$count = 2
$result = mysql_query("Select * ... order by id desc "); // $result include record in table t1 has id (4, 2) b/c order by id desc

Edit: i made a sql to try in http://www.sqlfiddle.com/#!2/8847d1/12 But i can't do that
I want the records result look like (all column have my above conditional)

编辑:我做了一个sql尝试在http://www.sqlfiddle.com/#!2/8847d1/12但我不能这样做我希望记录结果看起来像(所有列都有我上面的条件)

id|name|
4 |d   | 
2 |b   |

How to do that thank

怎么办谢谢

4 个解决方案

#1

select t1.id,t1.name from t1 left join t2 on t1.id = t2.uid where t1.type = 1 or t2.type = 1

#2

Since there is no link between the tables you can use a UNION query:

由于表之间没有链接,因此可以使用UNION查询:

SELECT id,type FROM t1 WHERE type=1
UNION
SELECT uid AS id,type FROM t2 WHERE type=1

http://dev.mysql.com/doc/refman/5.0/en/union.html for more info on union joins.

有关联合联接的更多信息,请访问http://dev.mysql.com/doc/refman/5.0/en/union.html。

#3

try this

select * from t1 join t2 on t1.id = t2.uid where t1.type = 1 and t2.type = 1

read how to use join(left join,right join,join)

阅读如何使用join(左连接,右连接,连接)

1. read here

在这里阅读

2. 2nd docs

2.第二个文档

#4

$type = 1;
if ( isset($type) and $type > 0 )
{
    $sql =  "SELECT id, name FROM t1
            LEFT JOIN t2 ON t1.id = t2.uid
            WHERE t1.type = $type AND t2.type = $type 
            ORDER BY t1.id DESC";
    $count  = mysql_num_rows( $sql ) or die('Error');
    $result = mysql_query( $sql ) or die('Error');
}

#1

select t1.id,t1.name from t1 left join t2 on t1.id = t2.uid where t1.type = 1 or t2.type = 1

#2