how do i get the column name and or value?

Question

select 
t.slotmachinebk,
t.gamingdate, 
t.freeplaydownloaded, 
t.freeplayadjusted, 
t.freeplayplayed, 
t.freeplayabandoned, 
t.freeplaybalance 
from (select * from freeplay.egmfreeplay union all select * from Change.EgmFreePlay) t where not exists (select * from  testtable where

slotmachinebk = t.slotmachinebk and
auditdate = t.gamingdate and
freeplaydownloaded = t.freeplaydownloaded and
freeplayadjusted =  t.freeplayadjusted and
freeplayplayed = t.freeplayplayed and
freeplayabandoned = t.freeplayabandoned and 
freeplaybalance = t.freeplaybalance)

Well I have this tsql query that gives me the records that dont match between testtable and freeplay.egmfreeplay....but how do I modify this query to get the column name/value that dont match, column names being the columns used in subquery ANDs...

Answer 1

You could put a bunch of 'CASE WHEN' statements in your SELECT query

CASE WHEN a.column1 <> b.column1 THEN 1 ELSE 0 END AS column1_diff

Then you would see in your result set a '1' if that column was different for that record or a '0' if it wasn't different

EDIT

I attempted to refactor your query to something that will work.

SELECT      t.*,
            CASE WHEN t.slotmachinebk       <> z.slotmachinebk      THEN 1 ELSE 0 END AS slotmachinebk_diff,
            CASE WHEN t.gamingdate          <> z.gamingdate         THEN 1 ELSE 0 END AS gamingdate_diff,
            CASE WHEN t.freeplaydownloaded  <> z.freeplaydownloaded THEN 1 ELSE 0 END AS freeplaydownloaded_diff,
            CASE WHEN t.freeplayadjusted    <> z.freeplayadjusted   THEN 1 ELSE 0 END AS freeplayadjusted_diff,
            CASE WHEN t.freeplayplayed      <> z.freeplayplayed     THEN 1 ELSE 0 END AS freeplayplayed_diff,
            CASE WHEN t.freeplayabandoned   <> z.freeplayabandoned  THEN 1 ELSE 0 END AS freeplayabandoned_diff,
            CASE WHEN t.freeplaybalance     <> z.freeplaybalance    THEN 1 ELSE 0 END AS freeplaybalance_diff,
FROM        testtable z
LEFT JOIN   (
            SELECT      * 
            FROM        freeplay.egmfreeplay 
            UNION ALL 
            SELECT      * 
            FROM        Change.EgmFreePlay
            ) t
        ON  z.slotmachinebk         = t.slotmachinebk
        AND z.auditdate             = t.gamingdate
        AND z.freeplaydownloaded    = t.freeplaydownloaded
        AND z.freeplayadjusted      = t.freeplayadjusted
        AND z.freeplayplayed        = t.freeplayplayed
        AND z.freeplayabandoned     = t.freeplayabandoned
        AND z.freeplaybalance       = t.freeplaybalance
WHERE       t.id IS NOT NULL -- this will only select those in 'freeplay.egmfreeplay' and 'Change.EgmFreePlay' that are not in 'testtable', I am not sure if 'id' actually exists, but you want to use something that will never be NULL in those two tables

Answer 2

If I'm understanding your question properly, you want rows from each table where the records are different... if you have an identity column to match them on, you could join on that and then add your where clause to only pull records where those columns are different.

Answer 3

LEFT OUTER JOIN

I'd don't know the key column names, but try this...

select 
    CASE WHEN ISNULL(t.slotmachinebk           ,'')!=ISNULL(z.XXX                ,'') THEN 'slotmachinebk'       ELSE '' END AS slotmachinebk        --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.gamingdate         ,'')!=ISNULL(z.gamingdate         ,'') THEN 'gamingdate'          ELSE '' END AS gamingdate           --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplaydownloaded ,'')!=ISNULL(z.freeplaydownloaded ,'') THEN 'freeplaydownloaded'  ELSE '' END AS freeplaydownloaded   --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplayadjusted   ,'')!=ISNULL(z.freeplayadjusted   ,'') THEN 'freeplayadjusted'    ELSE '' END AS freeplayadjusted     --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplayplayed     ,'')!=ISNULL(z.freeplayplayed     ,'') THEN 'freeplayplayed'      ELSE '' END AS freeplayplayed       --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplayabandoned  ,'')!=ISNULL(z.freeplayabandoned  ,'') THEN 'freeplayabandoned'   ELSE '' END AS freeplayabandoned    --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
        ,CASE WHEN ISNULL(t.freeplaybalance    ,'')!=ISNULL(z.freeplaybalance    ,'') THEN 'freeplaybalance'     ELSE '' END AS freeplaybalance      --<<<<wrap all columns in this, make sure you make the ISNULL alternate value match the datatype of the column
    from (select * from freeplay.egmfreeplay union all select * from Change.EgmFreePlay) t 
    LEFT OUTER JOIN testtable z ON t.KEY=z.KEY  --<<<<<<<<key names???
    where not exists (select * from  testtable where
                             slotmachinebk = t.slotmachinebk and
                             auditdate = t.gamingdate and
                             freeplaydownloaded = t.freeplaydownloaded and
                             freeplayadjusted =  t.freeplayadjusted and
                             freeplayplayed = t.freeplayplayed and
                             freeplayabandoned = t.freeplayabandoned and 
                             freeplaybalance = t.freeplaybalance
                      )

Answer 4

select t.slotmachinebk
       ,t.gamingdate
       , t.freeplaydownloaded
       , t.freeplayadjusted
       , t.freeplayplayed
       , t.freeplayabandoned
       , t.freeplaybalance 
from (select *  freeplay.egmfreeplay union all select * from Change.EgmFreePlay) t
left join testtable tt on 
      tt.slotmachinebk = t.slotmachinebk 
      and tt.auditdate = t.gamingdate 
      and tt.freeplaydownloaded = t.freeplaydownloaded 
      and freeplayadjusted =  t.freeplayadjusted 
      and tt.freeplayplayed = t.freeplayplayed 
      and tt.freeplayabandoned = t.freeplayabandoned 
      and tt.freeplaybalance = t.freeplaybalance
where tt.whateverTheHeckMyIdColumnIs is null

This should get you whatever is in your union that does not have an exact match in testtable. Of course you shouldn't not use select * either, especially not in union (this query would break the first time someone changed a table structure in one but not both of the tables in the union). Learn to specify your columns. If you have no id on testtable, create one first or the change the where clause to a field that cannot be ever be null.