CODEWITHSUNDEEP
sqlsql-server
Vanojx1
Vanojx1

Reputation: 5584

Sql Server Master/Detail search query

I have 2 tables:

Customers

AccountId Cdescr other customer cols...
1000      ABC

Branch

AccountId BranchId Bdescr other branch cols...
1000      1        AAA
1000      2        BBB

I cannot find a way to achieve this

AccountId BranchId Cdescr Bdescr branchCols... customerCols...
1000      0        ABC    NULL   NULL          VALUES...
1000      1        NULL   AAA    VALUES...     NULL
1000      2        NULL   ABC    VALUES...     NULL

On the customer table missing branchId column should be set to 0 by default.

I need to be able to search for both Cdescr and Bdescr and every match on Customer table should pick up the related branches. If mathing only on the branch table anyway the related customer row should be picked up

Using a FULL OUTER JOIN joining on branchId is actually not working

SELECT *
FROM (
    SELECT *, 0 as branchId
    FROM Customers
    WHERE CONCAT(an_descr1, an_descr2) LIKE '%SEARCH_STRING%'
) a
FULL OUTER JOIN Branch d ON  d.branchId = a.branchId

In the current query im not able to search in the branch table

Upvotes: 1

Views: 129

Answers (3)

Tyron78
Tyron78

Reputation: 4187

Try this:

DECLARE @tCust TABLE(
AccountId INT
,Cdescr NVARCHAR(10)
);

DECLARE @tBranch TABLE(
AccountId INT
,BranchId INT
,Bdescr NVARCHAR(10)
);


INSERT INTO @tCust VALUES(1000, 'ABC');
INSERT INTO @tBranch VALUES(1000, 1, 'AAA'), (1000, 2, 'BBB');

WITH cte AS(
  SELECT ISNULL(b.AccountId, c.AccountId) AccountId, ISNULL(b.BranchId, 0) BranchId, bDescr, cDescr
    FROM @tCust c
    FULL OUTER JOIN @tBranch b ON b.AccountId = c.AccountId
  UNION ALL
  SELECT c.AccountId, 0 BranchId, NULL bDescr, cDescr
    FROM @tCust c
 )
 SELECT *
   FROM cte
   WHERE CONCAT(Bdescr, Cdescr) LIKE '%ABC%'

Upvotes: 1

Indent
Indent

Reputation: 4967

Try this using coalesce to convert null to empty string:

SELECT
    *
FROM
    Customers C
    FULL OUTER JOIN Branch B ON       
        C.AccountId = B.AccountId
where
    CONCAT(
      coalesce(C.an_descr1,''),
      coalesce(C.an_descr2,''),
      coalesce(B.another_descr,'')
    ) LIKE '%SEARCH_STRING%'

Upvotes: 0

Indent
Indent

Reputation: 4967

You need use UNION.

  • First query you select Customers fields with null value for Branch fields
  • Second Query you select Branch fields with null value for Customers fields.

You need select explicity all the fields. All the query need of UNION provide fields in same order and same type.

select
    AccountId,
    0 as BranchId,
    Customers.Cdescr,
    null as Bdescr ,
    Customers.C1,
    Customers.C2,
    null as B1,
    null as B2
from
    Customers
union all
select
    Branch.AccountId,
    Branch.BranchId,
    null Cdescr,
    Branch.Bdescr ,
    null as C1,
    null as C2,
    Branch.B1,
    Branch.B2
from
    Branch

Upvotes: 0

Related Questions