Help on running a specific SQL statement

[Tekk]

So Im trying to figure out how to compare two tables and get a listing of records that do NOT appear in the other.

Table 1 contains information that Im comparing to Table 2 and I want any records in T1 that do NOT appear in T2 to show in the results. I have some examples below:

TABLE 1
T-Code		Auth_Object		Attribute	Attribute Value
-----------------------------------------------------------------------------
ME21		M_BEST_BSA		ACTVT		01
ME21		M_BEST_BSA		ACTVT		02
ME21		M_BEST_BSA		BSART		XX
ME21		M_BEST_EKG		ACTVT		01
ME21		M_BEST_EKG		EKGRP		XX

TABLE 2
T-Code		Auth_Object		Attribute	Attribute Value
-----------------------------------------------------------------------------
ME21		M_BEST_BSA		ACTVT		01
ME21		M_BEST_EKG		ACTVT		01

So when running the proposed query against the above data, I would want the following records to be included in the results:

T-Code		Auth_Object		Attribute	Attribute Value
-----------------------------------------------------------------------------
ME21		M_BEST_BSA		BSART		XX
ME21		M_BEST_BSA		ACTVT		02
ME21		M_BEST_EKG		EKGRP		XX

Any help in creating a query that will assist me in running this on a multitude of records in the tables? Thanks in advance.

[sr71000]

anything like this?

[thelist] MySQL comparing two tables

[vass0922]

Quote:

Originally Posted by sr71000

anything like this?

[thelist] MySQL comparing two tables

I would go with the join method in mentioned in this article

T-Code Auth_Object Attribute Attribute Value


select *
from table 1 t1
LEFT OUTER join table2 t2
on t1.[t-code] = t2.[t-code]
LEFT OUTER join table2 t2
on t1.[auth_object] = t2.[auth_object]
LEFT OUTER join table2 t2
on t1.[Attribute] = t2.[Attribute]
LEFT OUTER join table2 t2
on t1.[Attribute Value] = t2.[Attribute Value]
WHERE [t-code] IS NULL
OR [auth_object] IS NULL
OR Attribute IS NULL
OR [Attribute value] IS NULL

btw, this will give you half the results you want, you can figure out the rest


FYI I REALLY dont like the structure of this table

1. Spaces in column names
2. hyphens in column names
3. No standard in seperating words (I typically change case of first letter in each word AttributeValue). If you dont like changed case, then keep with underscore, with hyphens you have to use brackets and they're just annoying
4. Attribute and Attribute value probably shouldn't be in the same table.

[Rootstonian]

SELECT
A.T-CODE,
A.AUTH_OBJECT,
A.ATTRIBUTE,
A.ATTRIBUTE VALUE
FROM TABLE1 A
WHERE NOT EXISTS
(SELECT 'X'
FROM TABLE2 B
WHERE
A.T-CODE = B.T-CODE
AND A.AUTH_OBJECT = B.AUTH_OBJECT
AND A.ATTRIBUTE = B.ATTRIBUTE
AND A.ATTRIBUTE VALUE = B.ATTRIBUTE VALUE)