CODEWITHSUNDEEP
sqlsql-serversql-server-2008
poohbear
poohbear

Reputation: 452

Pulling results from two tables if first table has no matches

Trying to create a stored procedure that I can use to search for people by first name and last name. The Fname and Lname are stored together in two different tables, looking to pull from Table1 and if there isn't a match then pull from Table2 the results. 

@FName VARCHAR(20) = NULL
@LName VARCHAR(20) = NULL,

SELECT  TB1.ID, TB1.FName, TB1.LName

FROM    Table1 TB1
        LEFT JOIN Table2 TB2
          ON TB1.ID = TB2.ID

WHERE   1=1
        AND COALESCE(TB1.Fname, '')  LIKE '%' + REPLACE(COALESCE(@FName, TB1.Fname, ''), ' ', '%') + '%'
        AND COALESCE(TB1.Fname, '')  LIKE '%' + REPLACE(COALESCE(@LName, TB1.Fname, ''), ' ', '%') + '%'

What i tried so far was this and it seems to pull back all the recordes. 

WHERE   1=1
        AND COALESCE(TB1.Fname, '')  LIKE '%' + REPLACE(COALESCE(@FName, TB1.Fname, ''), ' ', '%') + '%'
        OR COALESCE(TB2.Fname, '')  LIKE '%' + REPLACE(COALESCE(@FName, TB2.Fname, ''), ' ', '%') + '%'
        AND COALESCE(TB1.Lname, '')  LIKE '%' + REPLACE(COALESCE(@LName, TB1.Lname, ''), ' ', '%') + '%'
        OR COALESCE(TB2.Lname, '')  LIKE '%' + REPLACE(COALESCE(@LName, TB2.Lname, ''), ' ', '%') + '%'

Any help or guidance would be grateful, I've google for the last day or so but i guess I am googling the wrong stuff.

Upvotes: 3

Views: 53

Answers (2)

poohbear
poohbear

Reputation: 452

After playing around with it for a while and looking at what G Mastros posted, it got me thinking about how I was trying to call two OR statements when really all I needed was one. So i changed the COLESCE statement a little and put them in one OR which in return gave me the required results.

I feel this is a works with the ability to add other request along the way as needed, so for instance if i need to search based off of Email or phone number. 

DECLARE @FName VARCHAR(100)
DECLARE @LName VARCHAR(100)

SET @FName = 'Adam'
SET @LName  = NULL,

SELECT  TB1.ID,
        COALESCE(TB1.FName,TB2.FName) AS FirstName
       ,COALESCE(TB1.LName,TB2.LName) AS LAstName

FROM    Table1 TB1
        INNER JOIN Table2 TB2
          ON TB1.ID = TB2.ID

WHERE   1=1
    AND
        (
         COALESCE(U.Unit_Owner_FName, '')  LIKE '%' + COALESCE(@FName, U.Unit_Owner_FName, '') + '%'
            AND COALESCE(U.Unit_Owner_LName, '')  LIKE '%' + COALESCE(@LName, U.Unit_Owner_LName, '') + '%'

        OR

        COALESCE(I.Insured_FName, '')  LIKE '%' + COALESCE(@FName, I.Insured_FName, '') + '%'
            AND COALESCE(I.Insured_LName, '')  LIKE '%' + COALESCE(@LName, I.Insured_LName, '') + '%'
        )

Upvotes: 1

George Mastros
George Mastros

Reputation: 24498

You should use a Full Join, making sure that you coalesce the results so that it pulls from whichever table has the data.

SELECT  Coalesce(TB1.ID, TB2.ID) As ID, 
        Coalesce(TB1.FName, TB2.FName) As FName, 
        Coalesce(TB1.LName,TB2.LName) As LName
FROM    Table1 TB1
        Full JOIN Table2 TB2
          ON TB1.ID = TB2.ID

Upvotes: 2

Related Questions