Unmatched Records 1

[SteveJR]

Hi Guys,

I know this has been asked many times but can someone help me with an unmatched query please.

I have 2 tables:

Table A Columns:
TableName
GenTableID
ElementName
DefaultValue

Table B Columns:
Intrnl_Name
Ctree_Tbl (Contains Same value as Table A.TableName)
Ctree_Fld (Contains Same value as Table A.ElementName)
Ctree_Value

Table A contains a list of tables and every element that exists in that Table.

Table B should contain the same as Table A but can sometimes get out of date.

What I would like is a query that will show me any new fields that are in Table A but not Table B.

Can anyone help?

Thanks,

Steve

 

[PHV]

Something like this ?
SELECT A.*
FROM [Table A] AS A LEFT JOIN [Table B] AS B ON A.TableName = B.Ctree_Tbl AND A.ElementName = B.Ctree_Fld
WHERE B.Ctree_Tbl Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SteveJR]

Thanks PHV. Almost exaclty what I need. The problem with the results is that Table A can contain a lot of rows that might not be in Table B. I really only want to see any missing rows for the TableName that is in both Table A and Table B.

For example, Table A contains 5 Rows:

TableName GenTableId ElementName DefaultValue
Account 1 sis_tbl_ver 0
Account 1 pan 123456
Activity 2 pan 112233
Card 3 pan 445566
Card 3 fm_ts 0

Table B Contains 2 rows:

Intrnl_Name Ctree_Tbl Ctree_Fld Ctree_Value
Acct Vers Account sis_tbl_ver 20
Card Pan Card pan 123456

What I want the query to return is the 2 rows from Table A for the missing records for Account and Card. Is this possible?

Thanks,

Steve

 

[PHV]

Perhaps this ?
SELECT A.*
FROM [Table A] AS A LEFT JOIN [Table B] AS B ON A.TableName = B.Ctree_Tbl AND A.ElementName = B.Ctree_Fld
WHERE B.Ctree_Tbl Is Null
AND A.TableName In (SELECT B.Ctree_Tbl FROM [Table B])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SteveJR]

Hi PH,

The query now returns no results!

It looks as though it should work.

Steve

 

[SteveJR]

PH,

Scrap my previous response got it to work!

Needed to remove the "B." in the (SELECT B.Ctree_Tbl FROM [Table B]) line.

Thank you very much for your help.

Steve

 

[PHV]

Sorry for the typo :~/

 

[SteveJR]

No Problem PH. You have saved me a lot of time with your help.

Thanks,

Steve