与非直接相关的表的SQL关联

I need to perform a lookup using SQL to see if these 3 columns match my id. I tried JOIN, but the columns don't have any join point. No clear correlation. I also tried UNION, but they have different number of columns and different column data types. Basically I need to dip into 1 table to get a value. Use that value to dip into a 2nd table to get an additional value. Then use both of those values along with a hardcoded value to dip into a 3rd table to retrieve a unique value. Then use that unique value to dip into a 4th table to get the results that match the unique value. Here is the query that I have currently:

我需要使用SQL执行查找以查看这3列是否与我的id匹配。我尝试了JOIN，但列没有任何连接点。没有明确的相关性我也尝试过UNION，但它们具有不同数量的列和不同的列数据类型。基本上我需要深入到1个表中来获取值。使用该值进入第二个表以获取其他值。然后使用这两个值以及硬编码值浸入第3个表以检索唯一值。然后使用该唯一值进入第4个表以获得与唯一值匹配的结果。这是我目前的查询：

SELECT * FROM DEF_TABLE
WHERE DEF_ID IN (
SELECT DEF_ID FROM DESCRIPTION_TABLE 
    WHERE MY_TYPE = 'EQP'
    AND COL_TYPE = a
    AND COL_NAME = b
        IN (SELECT COL_TYPE as a, COL_NAME as b FROM TYPE_TABLE WHERE COL_TYPE IN
           (SELECT COL_TYPE FROM EQP_TABLE WHERE ID = 9000)));

---

EDIT: I found that running COL_TYPE and COL_NAME checks as seperate calls it works, but very slowly. Is there another way?

编辑：我发现运行COL_TYPE和COL_NAME检查它是否有效，但速度非常慢。还有另一种方式吗？

SELECT * FROM DEF_TABLE
WHERE DEF_ID IN (
SELECT DEF_ID FROM DESCRIPTION_TABLE 
    WHERE MY_TYPE = 'EQP'
    AND COL_TYPE IN (SELECT COL_TYPE FROM TYPE_TABLE WHERE COL_TYPE IN
                    (SELECT COL_TYPE FROM EQP_TABLE WHERE ID = 9000))
    AND COL_NAME IN (SELECT COL_NAME FROM TYPE_TABLE WHERE COL_TYPE IN
                    (SELECT COL_TYPE FROM EQP_TABLE WHERE ID = 9000)));

1 个解决方案

#1

It looks to me like what you're trying to do is

它看起来像你想要做的是

SELECT * FROM DEF_TABLE
WHERE DEF_ID IN (
    SELECT DT.DEF_ID 
    FROM DESCRIPTION_TABLE DT 
        JOIN TYPE_TABLE TT 
        ON   DT.COL_TYPE = TT.COL_TYPE
        AND  DT.COL_NAME = TT.COL_NAME
        JOIN EQP_TABLE ET
        ON   DT.COL_TYPE = ET.COL_TYPE
    WHERE DT.MY_TYPE = 'EQP'
      AND ET.ID = 9000 )