我有一个具有以下结构的表:
MyObject(id,name,category_id,t1,t2,t3,t4);
t1。。。tn是表的n个外键
MyType(id,name);
给定category_id,我想要一个列表,其中包含给定category.id的MyObjects"使用"的所有MyType的id。
我可以通过php在某些周期中解析流程来实现这一点,但我更愿意采用直接的SQL方法。
例如:
MyObject1(1,'q',1,1,2,3,NULL);
MyObject2(2,'w',1,1,2,NULL,NULL);
MyObject3(3,'e',2,5,3,NULL,NULL);
我的预期结果是category_id=1:
类型={1,2,3,5}
我的预期结果是category_id=2=
类型={3,5}
我的预期结果是category_id==432:
类型={}
感谢
我想这比我以前想象的要简单,这段代码解决了我的需求
SELECT t1
FROM
(SELECT DISTINCT s.t1
FROM MyObject s
WHERE s.category_id = 1 AND s.t1 IS NOT NULL) AS q
UNION
SELECT t2
FROM
(SELECT DISTINCT s.t2
FROM MyObject s
WHERE s.category_id = 1 AND s.t2 IS NOT NULL) AS w
UNION
SELECT t3
FROM
(SELECT DISTINCT s.t3
FROM MyObject s
WHERE s.category_id = 1 AND s.t3 IS NOT NULL) AS e
UNION
SELECT t4
FROM
(SELECT DISTINCT s.t4
FROM MyObject s
WHERE s.category_id = 1 AND s.t4 IS NOT NULL) AS r
UNION
SELECT t5
FROM
(SELECT DISTINCT s.t5
FROM MyObject s
WHERE s.category_id = 1 AND s.t5 IS NOT NULL) AS t
ORDER BY t1 ASC