-1

I need some advice.

I have an issue with joining two tables.

Example: Owner has a five-piece composite key
Position has a Six piece composite key

Owner Table has
As_Of_Date(Key), PORT_CD (Key), Deal_Or_Schedule (Key), Deal_Sched_No(Key),owner_port_cd (Key),Percent

Position table has As_Of_Date (Key),PORT_CD (Key),Deal_Or_Schedule (Key), Deal_Sched_No (Key), Book_CD_Nme (Key),Positn_Num (Key)

As you can see 4 of the conditions match in Owner to join to table 2 but Onwer_port_cd cant be joined in as it's not in Position.

I need to get one table from the two tables so I can map income to its owner.

Right now my join results in a cartesian product as it's not taking in all qualifiers.

Mike Donoghue
  • 11
  • 3
  • 3
    This is not very clear. What is killing you? Why can't you join OWNERSHIP and POSITION_ALLOCATION tables? – django-unchained Feb 08 '20 at 21:48
  • Thanks for editing. But this is still not clear & hard to read. Please read the edit help re inline & block formats for code & quotations. Also re line breaks. Please before you post look at the formatted version of your post below the edit box. No, I can't see. What does "4 of the conditions match" mean? Or "Onwer_port_cd cant be joined in as it's not in Position"? And there's still no question in your post body & no [mre]. Is there some code you meant to put into this post? PS One doesn't need to know constraints to query. The table meanings (what a row says) are necessary & sufficient. – philipxy Feb 09 '20 at 00:36
  • Please in code questions give a [mre]--cut & paste & runnable code; example input (as initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For errors that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL, which includes constraints & indexes & tabular initialization. [ask] – philipxy Feb 09 '20 at 00:45
  • When you get a result that you don't expect, stop trying to find your overall goal & find out what your misunderstanding is.--Isolate the first unexpected subexpression & its input & output. (Debugging fundamental.) Ask about that. Nobody could take what you wrote & know what your query is supposed to do or why you think it would do that. Use enough words, sentences & references to parts of examples. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. Google re @ in comments. – philipxy Feb 09 '20 at 00:52

1 Answers1

0

Try this:

SELECT *
FROM JUNE_INCOME JI
JOIN CHART_OF_ACCOUNTS CA
ON JUNE_INCOME.GL_ACCT_NO = CHART_OF_ACCOUNTS.GL_ACCT_NO
JOIN SECURITY_MASTER SM
ON JUNE_INCOME.INS_NO = SECURITY_MASTER.INS_NO
JOIN POSITION_ALLOCATION PA
ON JUNE_INCOME.TRADE_DT = POSITION_ALLOCATION.AS_OF_DATE
AND JUNE_INCOME.PORT_CD = POSITION_ALLOCATION.PORT_CD
AND JUNE_INCOME.BOOK_CD_NME = POSITION_ALLOCATION.BOOK_CD_NME
AND JUNE_INCOME.POSITN_NUM = POSITION_ALLOCATION.POSITN_NUM
JOIN POSITIONS PO
ON JUNE_INCOME.PORT_CD = POSITIONS.PORT_CD
AND JUNE_INCOME.BOOK_CD_NME = POSITIONS.BOOK_CD_NME
AND JUNE_INCOME.POSITN_NUM = POSITIONS.POSITN_NUM
JOIN OWNERSHIP OW
ON PA.AS_OF_DATE=OW.AS_OF_DATE
AND PA.PORT_CD=OW.PORT_CD
AND PA.DEAL_OR_SCHEDULE=OW.DEAL_OR_SCHEDULE
AND PA.DEAL_SCHED_NO=OW.DEAL_SCHED_NO
JOIN OWNERS 
ON OWNERSHIP.OWNER_PORT_CD = OWNERS.OWNER_PORT_CD

Please make sure you add input and expected output in your question. Additionally, make use of aliases to avoid writing full names everytime.

django-unchained
  • 844
  • 9
  • 21
  • unfortunately, this is the same results I was getting should be 309 rows this returns 1476 as it doesn't have the full composite key from either table. I am horrible at laying things out thanks for the idea. The problem is Ownership has a five-part primary key, of which the column OWNER_Port_CD isn't in position_allocation. Also OWNERSHIP is missing the Book_CD_NME which is part of the 5 part primary key . – Mike Donoghue Feb 08 '20 at 22:31