Sybase * =符合Ansi Standard，对于同一内部表有2个不同的外部表 [英] Sybase *= to Ansi Standard with 2 different outer tables for same inner table

问题描述

我正在尝试迁移一些旧的程序代码.我在弄清楚ANSI标准语法以产生相同结果时遇到了麻烦.

I am trying to migrate some legacy procedural code. I am having trouble figuring out the ANSI standard syntax to produce the same results.

以下是我尝试过的多种组合之一.第二个联接的内部表是什么，它是第一个联接的输出还是源表.

Below is one of the many combinations I have tried. What is the inner table for the second join, is it the output from the first join or is it the source table.

请帮助我更改很多代码.

Please help I have a lot of code to change.

原始SQL语句

select * from  
JT1 a, JT2 b, JT3 c  
where a.ID *= b.ID   
  and c.JOB *= b.JOB  

我的转化

select *   
from JT1 a  
 left outer join JT2 b   
 on a.ID = b.ID  
 right outer join JT3 c  
 on c.JOB = b.JOB  

下面是SQL表定义和示例数据.

Below is the SQL table definitions and sample data.

Create table JT1 (  
 ID   int(4)   not null,  
 NAME char(20) not null)  


Create table JT2 ( 
  ID  int(4)   not null, 
  JOB char(20) not null)  


Create table JT3 ( 
  JOB  char(20) not null, 
  DUTY char(20) not null)  

INSERT INTO dbo.JT1 VALUES(10, "Saunders")  
INSERT INTO dbo.JT1 VALUES(20, "Pernal")  
INSERT INTO dbo.JT1 VALUES(30, "Marenghi")  
INSERT INTO dbo.JT2 VALUES(20, "Sales")  
INSERT INTO dbo.JT2 VALUES(30, "Clerk")   
INSERT INTO dbo.JT2 VALUES(30, "Mgr")  
INSERT INTO dbo.JT2 VALUES(40, "Sales")  
INSERT INTO dbo.JT2 VALUES(50, "Mgr")  
INSERT INTO dbo.JT3 VALUES("Mgr","Evaluate")  
INSERT INTO dbo.JT3 VALUES("Mgr","Reports")  
INSERT INTO dbo.JT3 VALUES("Mgr","Meeting")  
INSERT INTO dbo.JT3 VALUES("Clerk","Stocking")  
INSERT INTO dbo.JT3 VALUES("Clerk","Customer Request")  

推荐答案

好，我花了一段时间，但尝试一下:

OK it took me awhile but try this:

select   a.ID,  a.NAME, b.ID,   b.JOB,  a.JOB,  a.DUTY    
from (Select * from #jt1    
      cross join #jt3  ) a 
left outer join #jt2 b    
  on a.ID = b.ID    and a.job = b.job

多次使用左联接运算符的问题是您那里确实有一个隐藏的交叉联接.这应该得到正确的结果，关于结果是否一直是由于开发人员没有完全理解他们所做的事情而导致的结果是不正确的，只有您可以知道.

The problem with using that left join operator mulitple times is that you really had a hidden cross join in there. This should get the right results, As to whether the results have been incorrect all along due to developers not undersatnding waht they were doing, only you can tell.

这篇关于Sybase * =符合Ansi Standard，对于同一内部表有2个不同的外部表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！