I have data due by tomorrow and I have to use 12 tables in order to get results

Question

I have used JOIN ON before my WHERE filters and I am having a hard time getting it to run. It is not recognizing the aliases. Should I provide the query in a generic fashion or is this a general enough question without that necessity?

EDIT (ADDED QUERY)

SELECT DISTINCT T8.CONTRACTAGENTOFFICE,
   T1.CONTRACTID,
   T1.CONTRACTOTHERID,
   T1.CONTRACTDATEID1,
   T1.CONTRACTDATEID2,
   T1.CONTRACTDATEID3,
   T11.CONTRACTMARKET,
   T10.CONTRACTAGENTFIRSTNAME,
   T10.CONTRACTAGENTMIDDLENAME,
   T10.CONTRACTAGENTLASTNAME,
   T3.CONTRACTOWNERLASTNAME,
   T3.CONTRACTOWNERFIRSTNAME,
   T3.CONTRACTOWNERMIDDLENAME,
   T3.CONTRACTOWNERBIRTHDATE,
   T3.CONTRACTOWNERUNIQUEID,
   T3.CONTRACTROLETYPE,
   T9.WIDGETNAME,
   T15.CONTRACTTRANSACTIONGROUPING,
   T6.CONTRACTOFFERINGNAME,
   SUM (T12.CONTRACTTRANSACTIONDOLLARS),
   MIN (T12.CONTRACTTRANSACTIONDATE)
FROM TABLE1 T1,
   TABLE2 T2,
   TABLE3 T3,
   TABLE4 T4,
   TABLE5 T5,
   TABLE6 T6,
   TABLE7 T7,
   TABLE8 T8,
   TABLE9 T9,
   TABLE10 T10,
   TABLE11 T11,
   TABLE12 T12,
   TABLE13 T13,
   TABLE14 T14,
   TABLE15 T15,
   TABLE16 T16
   JOIN TABLE1
   ON T1.CONTRACTID = T3.CONTRACTID
   JOIN TABLE3
   ON T3.CONTRACTPERSONTYPE = T5.CONTRACTPERSONTYPE
   JOIN TABLE4
   ON T1.CONTRACTID = T4.CONTRACTID
   JOIN TABLE 12
   ON T1.CONTRACTID = T12.CONTRACTID
   JOIN TABLE2
   ON T1.CONTRACTID = T2.CONTRACTID
   JOIN TABLE10
   ON T2.CONTRACTAGENTID = T10.CONTRACTAGENTID
   JOIN TABLE9 PRODUCTS
   ON T4WIDGETID = T9.WIDGETID
   JOIN TABLE 12
   ON T9.WIDGETID = T12.WIDGETID
   JOIN TABLE7
   ON T7.CONTRACTAGENTOFFICE = T8.CONTRACTAGENTOFFICE
   JOIN TABLE 16 TI_GROUP_RLSHPS
   ON T16.CONTRACTTRANSACTIONTYPE = T12.CONTRACTTRANSACTIONTYPE
   JOIN TABLE 12
   ON T12.CONTRACTTRANSACTIONTYPE = T13.CONTRACTTRANSACTIONTYPE
   JOIN TABLE14
   ON T14.TRANSACTIONID = T13.TRANSACTIONID
   JOIN TABLE15
   ON T15.CONTRACTTRANSACTIONGROUPING = T16.CONTRACTTRANSACTIONGROUPING
   LEFT JOIN TABLE6
   ON T1.CONTRACTID = T6.CONTRACTID
   AND T1.CONTRACTOTHERID IN ('1',
                              '21',
                              '47')
   AND T1.CONTRACTDATEID1 BETWEEN TO_DATE ('2012/07/01', 'yyyy/mm/dd') AND TO_DATE ('2014/09/30', 'yyyy/mm/dd')
   AND T1.CONTRACTDATEID2 BETWEEN TO_DATE ('2012/07/01', 'yyyy/mm/dd') AND TO_DATE ('2014/09/30', 'yyyy/mm/dd')
   AND T1.CONTRACTDATEID3 BETWEEN TO_DATE ('2012/07/01', 'yyyy/mm/dd') AND TO_DATE ('2014/09/30', 'yyyy/mm/dd')
   AND T3.CONTRACTTYPE                 IN (1,2,5)
   AND T11.CONTRACTMARKET              IN (1,2,3,4,5,6,7)
   AND T9.WIDGEGROUP                   IN (1,11)
   AND T15.CONTRACTTRANSACTIONGROUPING IN (1,4)
   AND T6.CONTRACTOFFERINGTYPE         IN (0,1,3)
   AND T3.CONTRACTROLETYPE             IN (1,7)
   AND T8.CONTRACTAGENTOFFICENAME LIKE 'XXXX%'
   AND T8.CONTRACTAGENTORIGINATINGOFFICE IN (1,13,21,47)
   AND T14.TRANSACTIONORIGINATINGID      IN (A,
                                             B,
                                             C,
                                             D,
                                             E)
GROUP BY T8.CONTRACTAGENTOFFICE,
   T1.CONTRACTID,
   T1.CONTRACTOTHERID,
   T1.CONTRACTDATEID1,
   T1.CONTRACTDATEID2,
   T1.CONTRACTDATEID3,
   T11.CONTRACTMARKET,
   T10.CONTRACTAGENTFIRSTNAME,
   T10.CONTRACTAGENTMIDDLENAME,
   T10.CONTRACTAGENTLASTNAME,
   T3.CONTRACTOWNERLASTNAME,
   T3.CONTRACTOWNERFIRSTNAME,
   T3.CONTRACTOWNERMIDDLENAME,
   T3.CONTRACTOWNERBIRTHDATE,
   T3.CONTRACTOWNERUNIQUEID,
   T3.CONTRACTROLETYPE,
   T9.WIDGETNAME,
   T15.CONTRACTTRANSACTIONGROUPING,
   T6.CONTRACTOFFERINGNAME

Thanks. There's actually 16 tables. My apologies as I have been at this for awhile. — Agileskunk, May 21 '15 at 00:51

score 0 · Answer 1 · answered May 21 '15 at 01:08

0

You problem is that you are listing each table twice in your from clause. You can either use the FROM TABLEA, TABLEB ... WHERE form or FROM TABLEA JOIN TABLEB ON ... WHERE form, but you don't mix them.

You FROM clause should look something like this:

FROM TABLE1 T1 
   JOIN TABLE2
   ON T1.CONTRACTID = T2.CONTRACTID
   JOIN TABLE3
   ON T1.CONTRACTID = T3.CONTRACTID
   JOIN TABLE4
   ON T1.CONTRACTID = T4.CONTRACTID
...
WHERE 
....

answered May 21 '15 at 01:08

ScottMcG

3,867
2
12
21

I need to join TABLE1 to 6 other tables with different attributes. – Agileskunk May 21 '15 at 02:22
This snippet I provided above shows joining TABLE1 to TABLE2, and to TABLE3 , and to TABLE4. The ON statement gives the criteria for the join of each table as it is added. Understanding JOINs is fundamental to writing SQL. You would probably be best advised to check out some tutorials such as http://www.w3schools.com/sql/sql_join.asp – ScottMcG May 21 '15 at 02:53

I have data due by tomorrow and I have to use 12 tables in order to get results

1 Answers1