MySQL属性数据库 [英] MySQL attribute database
问题描述
我有一个属性数据库.每个属性值都存储在一个表中,该表具有其父属性的ID.
I have an attribute database. Each attribute value is stored in a table with an ID to its parent attribute.
在一个独立的表中,我将编写一条SQL语句来提取数据
In a stand alone table I would write an SQL statement that would pull data
WHERE username = '' AND domain = ''
在属性表中,因为每个值都存储为单独的行,所以我不能这样做..我有一个联接,可以带回一个值+结果(请参见下文),但是我需要基于如上所示的2条标准.
In the attribute table as each value is stored as a separate row, I can not do this.. I have a join that allows me to bring back one value + result (see below), however i need a result based on 2 criteria as shown above..
SELECT upa.value, up.status, u.status
FROM user_product up, product_attribute pa, product p, user u, user_product_attribute upa
WHERE pa.attribute_name = 'username'
AND pa.product_attribute_id = upa.product_attribute_id
AND pa.product_id = p.product_id
AND u.user_id = up.user_id
AND up.user_product_id = upa.user_product_id
AND p.product_name = 'email'
AND upa.value = 'exampleuser'
我想我需要以某种结合的方式再次加入上述能力?任何输入都会有所帮助.
I presume i would need to join the above able again in some sort of union? Any input would be helpful..
谢谢
推荐答案
请不要使用隐式联接语法.需要第二组联接才能获得第二个属性.
Please don't use implicit join syntax. A second set of joins is needed to get a second attribute.
SELECT upa1.value, upa2.value, up1.status, u1.status
FROM product p
/* These joins get attribute/value for 'username' attribute */
INNER JOIN product_attribute pa1
ON p.product_id = pa1.product_id
AND pa1.attribute_name = 'username'
INNER JOIN user_product_attribute upa1
ON pa1.product_attribute_id = upa1.product_attribute_id
INNER JOIN user_product up1
ON upa1.user_product_id = up1.user_product_id
INNER JOIN user u1
ON up1.user_id = u1.user_id
/* These joins get attribute/value for 'domain' attribute */
INNER JOIN user_product_attribute upa2
ON upa1.user_product_id = upa2.user_product_id
INNER JOIN product_attribute pa2
ON upa2.product_attribute_id = pa2.product_attribute_id
AND pa2.attribute_name = 'domain'
WHERE p.product_name = 'email'
这篇关于MySQL属性数据库的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!