MYSQL中有3个表。
[Resources]
Id
Name
....
[Characteristics]
Id
Name
...
"资源"可以有多个"特征",所以我在另一个表中建立了关系:
[RecurCaract]
Id
IdRes (Id of Resource)
IdCha (Id of Characteristic)
因此,例如,我有这些记录与以下特征相关:
Example 1 - Car1 / Car2 / Car3 / Car4
Example 2 - Car1 / Car3
Example 3 - Car2 / Car3
Example 4 - Car3 / Car4
我需要一个查询来显示选择了Car2和Car3的所有资源。
在本实施例(实施例1和实施例3(中,
要搜索启用了特征数字25的资源。。。我可以使用这个查询:
SELECT Resources.Name, Characteristics.Name FROM Resources LEFT JOIN RecurCaract ON (Resources.Id=RecurCaract.IdRes) LEFT JOIN Characteristics ON (Characteristics.Id=RecurCaract.IdCha) WHERE Characteristics.Id = '25'
但是。。。如果我需要特性为25和3的资源(两者都启用(。。。我有问题。
SELECT Resources.Name, Characteristics.Name FROM Resources LEFT JOIN RecurCaract ON (Resources.Id=RecurCaract.IdRes) LEFT JOIN Characteristics ON (Characteristics.Id=RecurCaract.IdCha) WHERE Characteristics.Id = '25' AND Characteristics.Id = '3'
此查询不起作用。
什么是最好的方法?
select r.name RESOURCE, c1.name Characteristic_1, c2.name Characteristic_2 from
RecurCaract rc1 inner join RecurCaract rc2 on rc1.resource = rc2.resource left join
resources r on r.id = rc1.resource left join characteristics c1 on c1.id =
rc1.characteristic left join characteristics c2 on c2.id = rc2.characteristic
where rc1.characteristic = 25 and rc2.characteristic = 3;
这应该对你有用。