It's not easy because you tables aren't properly designed.

To join a table the way you want, the primary key of one table (the parent table) needs to be placed into the child table as a
foreign key. However Table T1 has (WFDEFINITIONID + SEQNO) as the primary key, but this hasn't been placed into the child table
(T2).

When you do the JOIN, the database can see that WFDEFINTIONID is in the child table, but which ServiceName is it supposed to use
from TableT1

T2.WfInstanceID  T2.WFDefinitionID   T1.ServiceName 
---------------  -----------------   --------------
200              109                 ????
201              109                 ????

There are three matching ServiceName's for each value of 109, the database doesn't know which one to use.

 
Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "dbdbdb" <beaker@h...>
Subject: [sql_language] It should be easy, but....


: It should be easy, but....
: 
: I have two tables, T1 and T2: 
: How could I get the result like T3? 
: 
: TABLE T1: 
: WFDEFINITIONID SEQNO SERVICENAME 
: -------------- ----------- -------------------------- 
: 109 1 BioFetch 
: 109 2 ExtractMedRefs 
: 109 3 citationService 
: 110 1 BioFetch 
: 110 2 ExtractMedRefs 
: 
: Table T2: 
: WFINSTANCEID WFDEFINITIONID 
: ------------ -------------- 
: 200 109 
: 201 109 
: 202 109 
: 203 109 
: 204 109 
: 205 109 
: 222 110 
: 224 110 
: 226 110 
: 
: How to make the result like this: 
: 
: TABLE T3: 
: WFINSTANCEID WFDEFINITIONID SERVICENAME 
: ------------ -------------- ---------------------------------------- 
: 200 109 BioFetch 
: 201 109 ExtractMedRefs 
: 202 109 ExtractMedRefs 
: 203 109 citationService 
: 204 109 citationService 
: 205 109 citationService 
: 222 110 BioFetch 
: 224 110 ExtractMedRefs 
: 226 110 ExtractMedRefs