none
How to get not related data or not exist from another table sql query ? RRS feed

  • Question

  • Problem

    I cannot do sql query display not related records or not exist data

    based on policycode and branchcode ?

    I have two tables 

    shipcustomer

    financecustomer

    this tables related with each others by customerCode and branchcode and policycode

    shipcustomer

    policycode int pk

    BranchCode int pk

    customerCode int

    FinanceCustomer

    serial  int  pk

    BranchCode int pk

    policycode int

    CustomerCode int

    i need to select data from shipcustomer have same customername on table financecustomer and different on branchcode

    or not exist meaning not have policycode related

    sample data

    shipcustomer table
    policycode BranchCode customerCode 
    122           1         104
    200           1         303
    300           1         305
    450           1         90
    FinanceCustomer table
    
    serial  BranchCode policycode CustomerCode
    1         2          122          104 
    2         2          300          305
    3         1          200          303
              

    Result must show

    policycode BranchCode customerCode 
    122           1         104
    300           1         305
    450           1         90

    so that i need query give me result above that have same data on two tables but different on branchcode

    so that i display policycode 122 and 300 because branchcode different from two tables

    and policycode 450 display because not have related record on table financecustomer .

    actually i need to display same policycode on two tables and branchcode =1 

    and  policycode on table shipcustomer and not have related records on table

    financecustomer table 



    • Edited by engahmedbarbary Sunday, May 26, 2019 3:43 AM write more details
    Sunday, May 26, 2019 3:39 AM

Answers

  • Hi,

    In order to get all three column from the shipcustomer table in the result SET, and meet the requirement of the OP (assuming I understood his requirement), we can use simple LEFT JOIN between the tables, as I wrote from the start.

    Please check if this solve your needs engahmedbarbary:

    -------------------------------------- DDL: create the table(s)
    DROP TABLE IF EXISTS shipcustomer DROP TABLE IF EXISTS FinanceCustomer GO create table shipcustomer (policycode int, BranchCode int, customerCod int) create table FinanceCustomer (policycode int, BranchCode int, customerCod int) GO
    -------------------------------------- DML: insert some sample data insert into shipcustomer(policycode, BranchCode, customerCod) values (122,1,104) insert into shipcustomer(policycode, BranchCode, customerCod) values (200,1,303) insert into shipcustomer(policycode, BranchCode, customerCod) values (300,1,305) insert into shipcustomer(policycode, BranchCode, customerCod) values (450,1,90) insert into FinanceCustomer(policycode, BranchCode, customerCod) values (122,2,104) insert into FinanceCustomer(policycode, BranchCode, customerCod) values (300,2,305) insert into FinanceCustomer(policycode, BranchCode, customerCod) values (200,1,303) insert into shipcustomer values (777,888,999) insert into FinanceCustomer values (777,888,111) GO ----------------------------------------- Present the data SELECT policycode, BranchCode, customerCod FROM shipcustomer SELECT policycode, BranchCode, customerCod FROM FinanceCustomer GO ----------------------------------------- Solution SELECT t1.policycode, t1.BranchCode, t1.customerCod-- , t2.* FROM shipcustomer t1 LEFT JOIN FinanceCustomer t2 ON t1.policycode = t2.policycode where (t1.customerCod = t2.customerCod and t1.BranchCode <> t2.BranchCode) OR (t2.policycode is null) GO

    * Next time Please Post the DDL+DML yourself! There is no reason that we will spend time on something that you can do, instead using that time to help to 2 more people. Thanks


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Sunday, May 26, 2019 10:58 AM
    Moderator

All replies

  • Good day,

    Instead of struggle with stores about what your tables structure and the sample data, you should simply provide queries to create the tables and insert the sample data. This way we could also create the same table and test solution before we post it. PLEASE ALWAYS provide queries to create the relevant table(s) and to insert some sample data!

    In the meantime, we can also provide general solution and description for the question.

    In first glance it seems like simple LEFT JOIN between the shipcustomer table and FinanceCustomer table will do the trick. You can use CTE and JOIN the tables inside it. After you join the tables you can select from the CTE only the rows that fit the condition of:

    (data from FinanceCustomer table is null) or (policycode and customerCode on both tables is the same but BranchCode is not the same)

    * Please try to implement this solution. If you have any issue to implement it then please provide the missing information to save our time queries to create the relevant table(s) and to insert some sample data.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, May 26, 2019 5:18 AM
    Moderator
  • create table #shipcustomer (policycode int, BranchCode int, customerCod int) 
    create table #FinanceCustomer  (policycode int, BranchCode int, customerCod int) 

    insert into #shipcustomer values (122,1,104)
    insert into #shipcustomer values (200,1,303)
    insert into #shipcustomer values (300,1,305)
    insert into #shipcustomer values (450,1,90)

    insert into #FinanceCustomer values (122,2,104)
    insert into #FinanceCustomer values (300,2,305)
    insert into #FinanceCustomer values (200,1,303)


    select * from #shipcustomer
    EXCEPT  
    select * from #FinanceCustomer

    policycode BranchCode customerCod
    122 1 104
    300 1 305
    450 1 90

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 26, 2019 7:01 AM
    Answerer
  • Hi,

    If you have the following two rows

    insert into #shipcustomer values (777,888,999)
    insert into #FinanceCustomer values (777,888,111)

    Then according to the OP request "same data on two tables but different on branchcode" this row should not be returned in the result since they have the same BranchCode, but using simple EXCEPT on all columns will return it.

    * I hope to see you Uri on Thu, May 30 2019 at PASS Global Hebrew meeting, and please inform me if you want to speak in the group in future meetings. It will be great to register you to lecture in the group.


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    Sunday, May 26, 2019 8:49 AM
    Moderator
  • Hi Ronen

    Same policy code, same branchcode?

    Yep, specify only needed columns :-)

    select policycode,BranchCode from #shipcustomer
    EXCEPT  
    select policycode,BranchCode from #FinanceCustomer


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 26, 2019 9:32 AM
    Answerer
  • plesss

    Oh my God
    I have also encountered this problem
    If you find an answer for that, also inform me

    how  do you link making in this page

    Sunday, May 26, 2019 9:43 AM
  • This will work

    select policycode,BranchCode from #shipcustomer
    EXCEPT  
    select policycode,BranchCode from #FinanceCustomer


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 26, 2019 9:46 AM
    Answerer
  • Hi,

    In order to get all three column from the shipcustomer table in the result SET, and meet the requirement of the OP (assuming I understood his requirement), we can use simple LEFT JOIN between the tables, as I wrote from the start.

    Please check if this solve your needs engahmedbarbary:

    -------------------------------------- DDL: create the table(s)
    DROP TABLE IF EXISTS shipcustomer DROP TABLE IF EXISTS FinanceCustomer GO create table shipcustomer (policycode int, BranchCode int, customerCod int) create table FinanceCustomer (policycode int, BranchCode int, customerCod int) GO
    -------------------------------------- DML: insert some sample data insert into shipcustomer(policycode, BranchCode, customerCod) values (122,1,104) insert into shipcustomer(policycode, BranchCode, customerCod) values (200,1,303) insert into shipcustomer(policycode, BranchCode, customerCod) values (300,1,305) insert into shipcustomer(policycode, BranchCode, customerCod) values (450,1,90) insert into FinanceCustomer(policycode, BranchCode, customerCod) values (122,2,104) insert into FinanceCustomer(policycode, BranchCode, customerCod) values (300,2,305) insert into FinanceCustomer(policycode, BranchCode, customerCod) values (200,1,303) insert into shipcustomer values (777,888,999) insert into FinanceCustomer values (777,888,111) GO ----------------------------------------- Present the data SELECT policycode, BranchCode, customerCod FROM shipcustomer SELECT policycode, BranchCode, customerCod FROM FinanceCustomer GO ----------------------------------------- Solution SELECT t1.policycode, t1.BranchCode, t1.customerCod-- , t2.* FROM shipcustomer t1 LEFT JOIN FinanceCustomer t2 ON t1.policycode = t2.policycode where (t1.customerCod = t2.customerCod and t1.BranchCode <> t2.BranchCode) OR (t2.policycode is null) GO

    * Next time Please Post the DDL+DML yourself! There is no reason that we will spend time on something that you can do, instead using that time to help to 2 more people. Thanks


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]



    Sunday, May 26, 2019 10:58 AM
    Moderator