Update Table with Mutiple Joins

[Schaeffrcc]

I have been working on this problem my company has to move data from one database to another. I have a Query that I set up in Access to query out all of the data element and update another table. I grabbed the SQL script data and tried to fix it so I could run it in Query Analyzer.

Generally:

I have 5 Tables:
Agent
Client_Agent_link
Client
Case_Table
Case_History

Case_table is where most of the data comes from and it Right joins to Case_history and it Left Joins to Client

Client Left Joins to Client_agent_Link
Client_Agent_Link Left Joins to Agent

Agent <- Client_Agent_Link <- Client <- Case_Table -> Case_History.

I hope that makes Sense?

Here is what I have for my Script:

/* insert new rows */
INSERT INTO CaseFee
SELECT case_table.case_seq,
case_table.client_seq,
case_history.case_type_seq,
case_table.offense_type_seq,
agent.agent_seq,
case_table.case_offense_date,
case_history.case_open_date,
case_history.case_close_date,
case_table.case_number,
case_table.case_sentence_date,
case_table.case_exp_date,
case_table.case_desc,
case_table.case_adult_juv

FROM case_table
RIGHT JOIN Case_history ON case_history.case_seq = case_table.case_seq
LEFT JOIN Client ON client.client_seq = Case_Table.client_seq
Left Join Client_Agent_link ON Client_agent_link.client_seq = Client.client_seq
LEFT Join client_agent_link.agent_seq = agent.agent_seq

WHERE case_history.case_type_seq = 2 Or
case_history.case_type_seq = 6 Or
case_history.case_type_seq = 23 Or
case_history.case_type_seq = 9 Or
case_history.case_type_seq = 13 Or
case_history.case_type_seq = 14 AND
case_history.case_open_date >= #5/1/2003# AND
client_agent_link.ca_link_to_date Is Null AND
agent.agent_initials<>&quot;SCL&quot; And
agent.agent_initials Not Like &quot;0*&quot; and
CaseFee.Case_seq IS NULL

I have not even gotten to the Problems that I am sure are in the Where Statement.

Please Help, My Brain is Fried and it is Friday after all.

Thomas

 

[VogonPoet]

Can you repost the where clause with paranthesis? That may be your problem. That is, since you are mixing ORs and ANDs there needs to be some heirarchy.

Michael

 

[Schaeffrcc]

I have been working more on this and had a Select Query woring and in attempting to modify it into a Insert Query I have run into a few new issues. This version does have the Where Clause updated.

With the following Script I get the error:

Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'CaseFee' does not match with a table name or alias name used in the query.

And the Only reference to CaseFee is in the last line of the Where Clause but I thought that is what I had to do to get it to add new records to the table?

/* insert new rows */
INSERT INTO CaseFee
SELECT case_table.case_seq,
case_table.client_seq,
case_history.case_type_seq,
case_table.offense_type_seq,
agent.agent_seq,
case_table.case_offense_date,
case_history.case_open_date,
case_history.case_close_date,
case_table.case_number,
case_table.case_sentence_date,
case_table.case_exp_date,
case_table.case_desc,
case_table.case_adult_juv

FROM (((agent INNER JOIN (client INNER JOIN client_agent_link
ON client.client_seq = client_agent_link.client_seq)
ON agent.agent_seq = client_agent_link.agent_seq)
INNER JOIN case_table ON client.client_seq = case_table.client_seq)
LEFT JOIN offense_type ON case_table.offense_type_seq = offense_type.offense_type_seq)
INNER JOIN (case_type INNER JOIN case_history ON case_type.case_type_seq = case_history.case_type_seq)
ON case_table.case_seq = case_history.case_seq

WHERE (((case_history.case_open_date)>=&quot;5/1/2003&quot AND
((case_history.case_type_seq)=6 Or
(case_history.case_type_seq)=23 Or
(case_history.case_type_seq)=9 Or
(case_history.case_type_seq)=13 Or
(case_history.case_type_seq)=14 Or
(case_history.case_type_seq)=2) AND
((client_agent_link.ca_link_to_date) Is Null) AND
(Not (agent.agent_initials)=&quot;SCL&quot; And
(agent.agent_initials) Not LIKE '%0%') and
(CaseFee.Case_Seq) is NULL)

Thanks for the Help,

Thomas

 

[pascalsql]

You need a to reference casefee in the from clause, with the appropriate join.

To simplify your problem, this is how to insert in a table.

-- First create your select
select col1, col2
from table1
join table2 on ...
where (big_condition)

-- test it and debug it until it gives you what you want

-- then, don't modify it anymore and add the insert clause
insert into casefee
(your untouched select)

But tell us why is the last where conditions (CaseFee.Case_Seq) is NULL). Do you want to exclude existing rows in casefee to avoid duplication? If so you'll need something like not exists.