多表查询

来源：互联网发布：网络教育统考不通过率编辑：程序博客网时间：2024/05/29 09:46

一、多表查询 (Get ModelOptions Not Linked To Color)

需求：有以下三张表，查询出vehiclemodel_vehiclemodeloptions中的op3的那一行记录。

（该记录不在第二个表中有关联的color,同时该记录存在同一个VehicleModel_id）

SELECT * FROM test.vehiclemodel_vehiclemodeloptions;

SELECT * FROM test.dictionary_color;

SELECT * FROM test.vehiclemodel_dictionarycolorreferences;

参考答案：

SELECT     t3.DICTIONARY_OPTION_REFERENCEFROM    test.vehiclemodel_vehiclemodeloptions t3where    t3.VehicleModel_id = 1        and t3.DICTIONARY_OPTION_REFERENCE not in (SELECT distinct            t2.DICTIONARY_OPTION_REFERENCE        FROM            test.dictionary_color t2        where            t2.id in (SELECT                     t1.dictionaryColorReferences                FROM                    test.vehiclemodel_dictionarycolorreferences t1                where                    t1.VehicleModel_id = 1))

0 0