Lookup for amount payable if conditions met or not

Question

I am trying to create table 3 by querying data from two other tables.

I would be grateful if anyone can point me in the right direction. I have been going round in circles rewriting code and I can't see the wood for the trees now.

Table 1
Payee Referrer,
Accident Type,
Expert Type,
Amount

Table 2
Case No
Payee Referrer
Accident Type
Expert Type

Create Table 3 on the basis of this logic

Select Case No from Table 2 AND Amount from Table 1

IF Payee Referrer (Table 2) = Payee referrer (Table 1)

AND Accident Type and Expert Type (Table 2) = Accident Type and Expert Type (Table 1)

OR Payee Referrer (Table 2) = Payee referrer (Table 1)

AND Accident Type (table 2) = Accident Type (Table 1)

OR Payee Referrer (Table 2) = Payee referrer (Table 1)

AND Expert Type (table 2) = Expert Type (Table 1)

IF None of the above combinations exist then

get the Amount (Table 1) where Accident Type and Expert Type (Table 1) = 'DEFUALT'

Answer 1

Not sure which columns between two tables can be used to JOIN. It is better to post some sample data and the output you expect. Try this:

SELECT Table2.[Case No], Table1.[Amount]
FROM Table1, Table2
WHERE Table2.[Payee Referrer] = Table1.[Payee Referrer]
    AND (
        Table2.[Accident Type] = Table1.[Accident Type] OR
        Table2.[Expert Type] = Table1.[Expert Type]
    )
OR NOT (
    Table2.[Payee Referrer] = Table1.[Payee Referrer]
    AND (
        Table2.[Accident Type] = Table1.[Accident Type] OR
        Table2.[Expert Type] = Table1.[Expert Type]
    )
) AND Table1.[Accident Type] = 'DEFUALT' AND Table1.[Expert Type] = 'DEFUALT'

Answer 2

Hi @Tom Sanderson ,

Thank you so much for posting here.

We recommend that you post CREATE TABLE statements for your tables(table1 and table2) together with INSERT statements with sample data. We also need to see the expected result of the sample and more details about the logic of table3 like adding '(' and ')'.

You could also refer below example and check whether it is helpful to you.

 DROP TABLE IF EXISTS table1  
 DROP TABLE IF EXISTS table2  
 DROP TABLE IF EXISTS table3  
      
 create table table1   
 (  
 PayeeReferrer varchar(10),  
 AccidentType varchar(10),  
 ExpertType varchar(10),  
 Amount int  
 )  
      
 create table table2  
 (  
 CaseNo int,  
 PayeeReferrer varchar(10),  
 AccidentType varchar(10),  
 ExpertType varchar(10)  
 )  
      
 create table table3  
 (  
 CaseNo int,  
 Amount int  
 )  
      
 insert into table1 values  
 ('PRA','ATB','ETC',10),  
 ('PRB','ATB','ETD',20),  
 ('PRC','ATB','ETC',30),  
 ('PRD','ATC','ETD',40),  
 ('PRE','ATC','ETC',50),  
 ('PRF','ATC','ETD',60),  
 ('PRG','DEFUALT','DEFUALT',70)  
      
 insert into Table2 values  
 (1,'PRA','ATB','ETC'),  
 (2,'PRB','ATB','ETD'),  
 (3,'PRC','ATC','ETC'),  
 (4,'PRD','ATB','ETD'),  
 (5,'PRE','ATC','ETE'),  
 (6,'PRF','ATC','ETD'),  
 (7,'PRG','ATE','ETF')  
      
 INSERT INTO table3  
 SELECT  B.CaseNo,A.Amount  
 FROM table1 A,Table2 B  
 WHERE   
 A.PayeeReferrer=B.PayeeReferrer AND   
 (  
 (A.AccidentType=B.AccidentType AND A.ExpertType=B.ExpertType)  
 OR  
 (A.AccidentType=B.AccidentType)  
 OR   
 (A.ExpertType=B.ExpertType)  
 OR   
 (A.AccidentType = 'DEFUALT' AND A.ExpertType = 'DEFUALT')  
 )  
      
 SELECT * FROM table3  

Output:

CaseNo Amount  
1 10  
2 20  
3 30  
4 40  
5 50  
6 60  
7 70  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hot issues November--What can I do if my transaction log is full?
Hot issues November--How to convert Profiler trace into a SQL Server table

Answer 3

In the future, would you mind actually playing by basic netiquette? Or if you can't do that, tell us why you didn't post any DDL. I'm also curious as to why you think names like table 1 and table 2 are helpful. Did you know that the table must have a key? This is not an option; this is the definition. I'm also trying to figure out why he would split was essentially the same data into two tables. Have you just copied paper notes into tables without doing any design whatsoever?

CREATE TABLE Accidents
(case_nbr CHAR(15) NOT NULL PRIMARY KEY,
accident_type CHAR(5) NOT NULL CHECK(accident_type IN (...),
expert_type CHAR(5) NOT NULL CHECK(expert _type IN (...),
something_amount DECIMAL(12,2) NOT NULL,
payee_referrer_duns CHAR(9) NOT NULL
REFERENCES REFERRERS (payee_referrer_duns)
);

Besides being a useless name, your table 3 should not exist. Why are you storing bad data and then trying to patch it on the fly in the database that should've been done in the input tier of your tiered architecture. Stage your data outside the database, scrub it, get a case number and then insert into the schema.