php - MySQL搜索重叠的2张表

我有两个表，见下文-配置文件是我的主/主表

  profiles            invoices
 ____________       ___________________
|id  Name    |     |profileid   paid   |
|============|     |===================|
|1  Abraham  |     |  2         unpaid |
|2  Martin   |     |  3         unpaid |
|3  John     |     |  3         paid   |
|____________|     |___________________|

可见，亚伯拉罕有0张发票，马丁有1张未付发票，约翰有2张发票；1张已付，1张未付。
我想搜索：
所有带已付发票的配置文件（john）
所有未付发票的资料（约翰和马丁）
所有有已付和未付发票的资料（约翰）
我可以做1和2很好，但我对第3步有问题。
这是我对1的查询；

$query = "SELECT DISTINCT profiles.name
FROM profiles LEFT JOIN invoices ON (profiles.id=invoices.profileid)
AND (invoices.paid='paid' OR invoices.paid='unpaid')
WHERE
IFNULL(invoices.paid, '') LIKE 'paid';

这是我对2的询问；

$query = "SELECT DISTINCT profiles.name
FROM profiles LEFT JOIN invoices ON (profiles.id=invoices.profileid)
AND (invoices.paid='paid' OR invoices.paid='unpaid')
WHERE
IFNULL(invoices.paid, '') LIKE 'unpaid';

这是我的三个问题；

$query = "SELECT DISTINCT profiles.name
FROM profiles LEFT JOIN invoices ON (profiles.id=invoices.profileid)
AND (invoices.paid='paid' OR invoices.paid='unpaid')
WHERE
IFNULL(invoices.paid, '') LIKE 'paid'
AND IFNULL(invoices.paid, '') LIKE 'unpaid'
;

如前所述，1&2工作良好，但3给我0个结果。
任何帮助都非常感谢。谢谢

最佳答案

您需要选择invoices表2次，以便找到有人已付款和未付款的地方。试试像这样的-

SELECT DISTINCT profiles.name
FROM profiles
LEFT JOIN invoices i1 ON (profiles.id=i1.profileid)
AND (i1.paid='paid')
LEFT JOIN invoices i2 ON (profiles.id=i2.profileid)
AND (i2.paid='unpaid')
WHERE
IFNULL(i1.paid, '') LIKE 'paid'
AND IFNULL(i2.paid, '') LIKE 'unpaid';

请参阅此sqlfiddle示例-http://sqlfiddle.com/#!2/ee4e2/4