带case / COALESCE语句的SQL查询

I have query that working perfectly.but now there is situation where i have to join 2 query in case statement.but problem is one of the query is in already use.So my question how do i add this two sql in one sql

我有查询,工作完美。但现在有情况我必须加入2个查询在case语句。但问题是其中一个查询已经在使用。所以我的问题如何在一个sql中添加这两个sql

My original sql is

我原来的sql是

SELECT
tc.dentist_id,md.vendor_no,pl.pack_trans_id,tc.agent_dentist,md.company_name,md.contact,md.phone_no,sql1.image_path,sql1.metal_id,sql1.expect_more,sql1.how_long_acquire,tc.check_amt,tc.check_date_sent,tc.check_no
          FROM tbl_check tc
          LEFT JOIN tbl_mst_dentist md ON tc.dentist_id=md.dentist_id
          LEFT JOIN tbl_pack_list pl   ON tc.pack_id=pl.pack_id 
          LEFT JOIN (SELECT image_path,pack_id,metal_id,expect_more,how_long_acquire FROM
tbl_metals_list  GROUP BY  pack_id
          )sql1 ON tc.pack_id=sql1.pack_id
          WHERE tc.sale_agent_id = '3' AND tc.paying_percent !=0

Now i have to add 2 sql statement in the above statement

现在我必须在上面的语句中添加2个sql语句

if(tc.agent_dentist=a) select sa.* ,sm.state_code from tbl_sales_agent
as sa,tbl_mst_state as sm where sa.sales_agent_id = '3' AND
sa.state=sm.state_name else select * from tbl_mst_dentist where
dentist_id =tc.dentist_id

second table already is in use.Is it possible? Thanks in advance

第二张表已经在使用中。是否可能?提前致谢

2 个解决方案

#1

yes, you can use an ALIAS with a different name. E.g. TABLE AS ANY_ALIAS.

是的,您可以使用具有不同名称的ALIAS。例如。表ASYYALALAS。

SELECT *
FROM t1
JOIN t2 AS alias_a ON (alias_a.t1_id = t1.id)
JOIN t2 AS alias_b ON (alias_b.t1_id = t1.id)

#2

Note that in the absence of any aggregating functions, the use of GROUP BY in this query...

请注意,在没有任何聚合函数的情况下,在此查询中使用GROUP BY ...

   SELECT image_path
        , pack_id
        , metal_id
        , expect_more
        , how_long_acquire 
     FROM tbl_metals_list  
    GROUP 
       BY  pack_id

...makes little sense. Perhaps you meant SELECT DISTINCT...?

......没什么意义。也许你的意思是SELECT DISTINCT ......?

#1