asked    Perry     2018-10-22       sql-server       105 view        1 Answer

[SOLVED] Two databases with same tables and column names, How to pull out one column with similar data while all the other columns showing the differences?

I have 2 databases named 'OK' and 'TX' with same table named 'E_Model' and columns like: 'Product_ID' , 'Description', 'Warranty' and 'Category' I want to have the list of Product ID's which both the tables have and want to know the difference in all the other column at the same time.

Desired result should be like:

**'*Database' 'Product_ID' , 'Description', 'Warranty'  'Category'***
OK           LB9456        COFFEE 436      NULL          10      
TX           LB9456         TOASTER 956      1           12
OK           QR3300         APPLE  31        3           15
TX           QR3300         ORANGE 45        5           20

I tried to run the following query:

SELECT  Product_ID
FROM  OKSV..E_MODEL
INTERSECT
SELECT  Product_ID
FROM  TXSV..E_MODEL
union all
SELECT  Product_ID
FROM  TXSV..E_MODEL
INTERSECT
SELECT  Product_ID
FROM  OKSV..E_MODEL

I did get the list of the similar Product_IDs in both the tables of the different databases. but when I used the following query:

SELECT  Product_ID, Description, Warranty, Category
FROM  OKSV..E_MODEL
INTERSECT
SELECT  Product_ID,Description, Warranty, Category
FROM  TXSV..E_MODEL
union all
SELECT  Product_ID,Description, Warranty, Category
FROM  TXSV..E_MODEL
INTERSECT
SELECT  Product_ID
FROM  OKSV..E_MODEL

It just showed me a different list. Basically, I am trying to know the differences in description, category and warranty of similar product ids in both the tables. I am doing this so that I could clean the data. I am new to this please help, as I am now doing this one by one which will take a lot of time.

  1 Answer  

        answered    Barret     2018-10-22      

Here is one way to do this (now matching your updated specifications). I have two separated CTEs for this as it should make it clearer what I am doing, but the same thing can be done using other queries.

WITH TXInfo AS (  -- Get all info from TX DB with ID matches
    SELECT Product_ID,
        Description, 
        Warranty, 
        Category
    FROM  TXSV..E_MODEL
    WHERE Product_ID IN (
        SELECT  Product_ID
        FROM  OKSV..E_MODEL
        INTERSECT
        SELECT  Product_ID
        FROM  TXSV..E_MODEL
        )
),
MisMatches as (  -- Get all info from OK DB with data mismatches
    SELECT  
         om.Product_ID,
         om.Description,
         om.Warranty,
         om.Category
    FROM  OKSV..E_MODEL om
    INNER JOIN TxInfo ti
        ON om.Product_ID = ti.Product_ID
    WHERE om.Description <> ti.Description
        OR om.Warranty <> ti.Warranty
        OR om.Category <> ti.Category
    )

SELECT 'OK' as [Database], -- Select all OK data
    mm.Product_Id,
    mm.Description,
    mm.Warranty,
    mm.Category
FROM Mismatches mm
UNION
SELECT 'TX',  -- Select only TX data that didn't match
    ti.Product_Id,
    ti.Description,
    ti.Warranty,
    ti.Category
FROM TXInfo ti
INNER JOIN Mismatches mm
    ON ti.Product_Id = mm.Product_Id
ORDER BY Product_Id, 
    1 desc -- Will not let us order by the name of the field here.




Your Answer





 2018-10-22         Hiram

Add fields based on number (Jquery)

I am trying to add X fields according to the number type in the number field or so when the value increases.My template:<div class="form-group row"> <label for="inputEmail3" class="col-sm-10 col-form-label">{l s='Nombre de voyageurs' mod='blocksouhaits'}</label> <div class="col-sm-2"> <input type="number" class="form-control" id="nbVoyager"> </div></div><div class="form-group row"> <div class="col-sm-6"> <input type="text" class="form-control" id="lastnameVoyager" placeholder="Nom"> </div> <div...
 jquery                     1 answers                     46 view
 2018-10-22         Julia

Close Animation On Body Click

How can I close an animation when i press on body , not the element that make the animation appear/dissapear?$('#btt').click(function() { $('.box').slideToggle('slow');});.box { width: 200px; height: 300px; background-color: #00000025; display: none;}<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script><button id="btt">PressIt</button><div class="box"></div>You need to do two things:Use slideUp() when the body is clickedStop propagating the click event when you click .box element so that the body cl...
 jquery                     1 answers                     44 view
 2018-10-22         Zenobia

Click on <a> text to checked input

How can I make sure that when I click on the text in <a> the checkbox is activated? Now when someone click on the text of the checkbox the drop down menu closes.This is a simple snippet:<div id="divChooseMacro" class="dropdown-menu" aria labelledby="choose-macro"> <a class='dropdown-item' data-value='pippo' style='cursor: pointer;'> <input type='checkbox' value='pippo'/>&nbsp; pippo </a></div>I try to set a cursor: pointer in the tag <a> but without success I suggest you to use label tag to relate it to the che...
 javascript                     2 answers                     48 view