Difference and additions between 2 database queries... 1

[davism]

All,

I have a question that any assistance with on direction would be greatly appreciated.

What I have is in one database table I have a list of patients and in another database table I have a list of patients. What I want to do is create a return of a dataset that includes only the unique instance and from each table. The location of the database running the query really wouldn't matter.

I know I could do a query on one database with a NOT EXISTS sub query accessing the other database table and putting the information into a temporary table (or a SELECT INTO) but how do I get it the other way? Meaning from the second database table and does not exist in the database table?

Any thoughts or examples would be greatly appreciated?

Thanks

 

[gmmastros]

Sounds like a job for the UNION operator. Ex:

[tt][blue]
Select Col1, Col2
From Database1.dbo.TableName

Union

Select Col1, Col2
From Database2.dbo.TableName
[/blue][/tt]

UNION will only return distinct data.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

True...except for and I apologize I wasn't completely clear but let me do this as a example:

Select Col1, Col2, Col3, Col4, Col5, Col6

From Database1.dbo.TableName

Union

Select Col1, Col2, Col3, Col4, Col5, Col6
From Database2.dbo.TableName

but I want it ONLY IF Col4 and Col6 are unique from one of the tables where it not in the other. The rest may be duplicates.

So, let's say:
Col1 = FirstName
Col2 = LastName
Col3 = DOB
Col4 = Processed
Col5 = Submitted
Col6 = City

This inform database2.dbo.tablename may have the complete information in Col1-Col6 where as database1.dbo.tablename may not have all of them complete. In which case, I only want the information from database2.dbo.table.

Does that make sense? A union would eliminate duplicates but there is actually a duplicate based on the Col1, Col2, Col3 and Col5. I want to remove that duplicate that is not complete and replace with only the complete record.

 

[gmmastros]

Does that make sense?

Almost. Can you show some sample data and expected results? Don't post any real data, just make up stuff... Fred Flintstone, Barney Rubble, etc...


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You may try

;with cteAall as (select Col1, Col2, Col3, etc. from DB1.dbo.TableName
UNION ALL
select Col1, Col2, Col3, etc. from DB2.dbo.TableName), 

cte_Numbered as (select *, row_number() over (partition by Col4, Col6 order by Col1) as RowNum from cteAll)

select * from cte_Numbered where RowNum = 1

The above works in SQL Server 2005 and above.

PluralSight Learning Library

 

[davism]

Ok, I have been mocking up some but I will mock up more. Here goes...

database1.dbo.table

Joe Smith 1/20/1979 Yes No Davenport
Mark Jones 3/18/1954 Yes Yes Miami
Alexis Nada 7/14/1971 No No Denver
David Hardy 4/28/1965 No No Billings

database2.dbo.table

Joe Smith 1/20/1979 No No Ohio
Mark Jones 3/18/1954 No Yes Miami
Alexis Nada 7/14/1971 No No Denver

What I could end up with in the Union is this:

Joe Smith 1/20/1979 Yes No Davenport
Joe Smith 1/20/1979 No No Ohio
Mark Jones 3/18/1954 Yes Yes Miami
Mark Jones 3/18/1954 No Yes Miami
Alexis Nada 7/14/1971 No No Denver
David Hardy 4/28/1965 No No Billings

What I want is any duplicate information in database2 to by utilized instead of database1:

Joe Smith 1/20/1979 No No Ohio
Mark Jones 3/18/1954 No Yes Miami
Alexis Nada 7/14/1971 No Yes Denver
David Hardy 4/28/1965 No No Billings

The first 2 is from database2 even though there is something in database1 based on the differences from Col4 and Col6 took the database2 (which database2 will always by the final one to utilize in the situation of duplicates.)

Make more sense on that?

markros, I'm not getting at all what you are referring to there. Will that help with what I tried to provide an example on?

 

[markros]

Using SQL Server 2005 or above one more time my solution

;with cteAll as (select FirstName, LastName, DOB, FirstStatus, SecondStatus, City, 1 as Db from myDB1.dbo.Table
UNION ALL
select FirstName, LastName, DOB, FirstStatus, SecondStatus, City, 2 as Db from myDB1.dbo.Table),

-- what do we want to do in case of different DOB and City?
cteNumbered as (select *, Row_number() over (partition by LastName, FirstName, DOB order by DB DESC) as RowNum from cteAll)

select * from cteNumbered where RowNum = 1

See details here

http://blogs.lessthandot.com/index....rver/including-an-aggregated-column-s-related

PluralSight Learning Library

 

[davism]

markros,

Now, I am thoroughly confused.

What is the

;with

representing? I'm not seeing that in the link you provided.

The link also mentions it may not be the best situation for performance. What other options?

-- what do we want to do in case of different DOB and City?cteNumbered as (select *, Row_number() over (partition by LastName, FirstName, DOB order by DB DESC) as RowNum from cteAll)

What is cteNumbered from?

In addition, where is the database2 being referenced here and also, the FirstName LastName and City are not the items that are concerning. the Col4 (Processed) and Col6 (City) are the only ones of concern.

There really are not duplicates if the DOB and others you mentioned are the same. The Col4 and Col6 are the important items.

 

[markros]

CTE stands for Common Table Expression. You may read another blog to get some idea of what they can do

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/cte-and-hierarchical-queries

PluralSight Learning Library

 

[gmmastros]

I created some table variables so I could mock up your data. The code below uses the table variables to run against. You can copy/paste the code to a query window to see how it works.

Declare @db1 Table(FirstName VarChar(20), LastName VarChar(20), DOB DateTime, Processed VarChar(5), Submitted VarChar(5), City VarChar(20))
Declare @db2 Table(FirstName VarChar(20), LastName VarChar(20), DOB DateTime, Processed VarChar(5), Submitted VarChar(5), City VarChar(20))

Insert Into @db1 Values('Joe'   , 'Smith','1/20/1979','Yes','No' ,'Davenport')
Insert Into @db1 Values('Mark'  , 'Jones','3/18/1954','Yes','Yes','Miami')
Insert Into @db1 Values('Alexis', 'Nada' ,'7/14/1971','No' ,'No' ,'Denver')
Insert Into @db1 Values('David' , 'Hardy','4/28/1965','No' ,'No' ,'Billings')

Insert Into @db2 Values('Joe'   ,'Smith','1/20/1979','No','No' ,'Ohio')
Insert Into @db2 Values('Mark'  ,'Jones','3/18/1954','No','Yes','Miami')
Insert Into @db2 Values('Alexis','Nada' ,'7/14/1971','No','No' ,'Denver')

[green]-- Actual code starts here[/green]
Select  AllPeople.FirstName,
        AllPeople.LastName,
        AllPeople.DOB,
        Coalesce(DB2.Processed, DB1.Processed) As Processed,
        Coalesce(DB2.Submitted, DB1.Submitted) As Submitted,
        Coalesce(DB2.City, DB1.City) As City
From    (
        Select FirstName, LastName, DOB From [!]@DB1[/!]
        Union
        Select FirstName, LastName, DOB From [!]@DB2[/!]
        ) As AllPeople
        Left Join [!]@db1[/!] As DB1
          On  AllPeople.FirstName = DB1.FirstName
          And AllPeople.LastName = DB1.LastName
          And AllPeople.DOB = DB1.DOB
        Left Join [!]@db2[/!] As DB2
          On  AllPeople.FirstName = DB2.FirstName
          And AllPeople.LastName = DB2.LastName
          And AllPeople.DOB = DB2.DOB

If you are satisfied that the above code is working properly based on the sample data, then you can remove the top part where the table variables are populated and change the code where it is highlighted in red so that it uses your db's/tables instead.

If you have any questions about how this works, let me know and I will attempt to explain it.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

Thoroughly confused with that link as well...

 

[markros]

You were asking what

;with etc. mean

They represent Common Table Expressions which I used to produce the desired result and simplify the logic.

Is the first query (cteAll) clear for you?

(You can remove the second cte for now and just do:

;with cteAll as (select FirstName, LastName, DOB, City, Status1, Status2, "DB1" as DbName from DB1.dbo.MyTable
UNION ALL
select FirstName, LastName, DOB, City, Status1, Status2, "DB2" as DbName from DB2.dbo.MyTable)

select * from cteAll
---------------------------
The above simply selects everything.

The second step is to add row_number() using group by FirstName, LastName, DOB

So, if you have two records with the same name, the second DB will get number 1 and the record from the first DB will get number 2.

And then you select everything for record number 1 thus eliminating the extra records.







PluralSight Learning Library

 

[davism]

gmmastros,

Similar to what I was tabling about with the temporary table aspects.

One thing I'm not sure about though, doesn't Coalesce involve the selection of IS NOT NULL? that will not working entirely because the values will be not null.

Let me take a closer look at some of this. It seems to be treading down a direction direction with the temporary tables. But I need work it and look at it a little more.

 

[gmmastros]

Yes, Coalesce will return the first non-null value from it's parameter list. You said in an earlier post that if the row is found in both tables, you prefer the data from database2. That's why it appears in the parameter list first.

But, the real key here is... if the data is in one table and not the other, the left join will cause the row to be returned, but the columns from the left joined table will be NULL. To be more specific... If there is a person is db1 that does not exist in db2, the left join to the db2 table will still return a row, but the values from the table will be null and the coalesce function will return the data from database1 instead.

Re-reading that... I think I may have added to the confusion. If this doesn't make sense, let me know.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[davism]

gmmastros,

Excellent information and helped me resolve me issue. Thank you very, very much.

Very much appreciated!