Mismatched data between two tables and two fields

[CodyMonster]

I'm trying to get my head around how to find a situation where I might have a field where two tables match, but, where another field might not.
I'll try and explain this the best I can.
Table 1:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Lease ID[/TD]
[TD]Ticket Number[/TD]
[/TR]
[TR]
[TD]22045[/TD]
[TD]100542[/TD]
[/TR]
[TR]
[TD]22046[/TD]
[TD]100543[/TD]
[/TR]
[TR]
[TD]22047[/TD]
[TD]100544[/TD]
[/TR]
[TR]
[TD]22048[/TD]
[TD]100545[/TD]
[/TR]
</tbody>[/TABLE]








Table 2:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Lease ID[/TD]
[TD]Ticket Number[/TD]
[/TR]
[TR]
[TD]22045[/TD]
[TD]100542[/TD]
[/TR]
[TR]
[TD]22046[/TD]
[TD]100544[/TD]
[/TR]
[TR]
[TD]22047[/TD]
[TD]100543[/TD]
[/TR]
[TR]
[TD]22048[/TD]
[TD]100545[/TD]
[/TR]
</tbody>[/TABLE]









I'm having a hard time thinking of a way a query would spit out that 22046 doesn't have matching ticket numbers.
Maybe this would be easy for someone else. But I'm having a difficult time understanding how to do this.


Thanks for all the help everyone.

 

[Cjdash]

Hi Cody,

Could you pull both Ticket number fields into the query and then use an IIF statement to check they match?

IE: IIF([Table 1.Ticket Number]=[Table 2.Ticket Number],"Match","No Match")

 

[CodyMonster]

Hi Cody,

Could you pull both Ticket number fields into the query and then use an IIF statement to check they match?

IE: IIF([Table 1.Ticket Number]=[Table 2.Ticket Number],"Match","No Match")

I think, tell me if I'm wrong here, they they would all match.
Because the ticket numbers match, they just belong to the wrong lease ID.

for example:
100543 is in both tables, just under two different lease ID's

 

[james_lankford]

select 
  table_1.[Lease ID] 
from
 table_1
where 
(
  table_1.[Lease ID] 
  not in 
  (
    select 
      table_1.[Lease ID] 
    from 
      table_1
        inner join 
          table_2
            on
            table_1.[Lease ID] = table_2.[Lease ID]
            and 
            table_1.[Ticket Number] = table_2.[Ticket Number]
  )
)

 

[CodyMonster]

James,
Thanks.. But.. getting an Syntax error..

I changed the SQL to reflect what my database tables really are.. and it's the same as what you posted.

select
Truck_Tix.[Property ID]
from
Truck_Tix
where
(
Truck_Tix.[Property ID]
not in
(
select
Truck_Tix.[Property ID]
from
Truck_Tix
inner join
Restatements
on
Truck_Tix.[Property ID] = Restatement.Lease Number]
and
Truck_Tix.[Ticket Num] = Restatement.[Truck Ticket Num]
)
)

 

[xenou]

Here is a second approach.

1) First get all Ticket Numbers from both tables (first Query)
2) Then find mismatches (second Query)


First query (Called Query4):

select [Ticket Num] 
from Truck_Tix
union
select [Truck Ticket Num]
from Restatements

Second Query (called Query5):

select 
	A.[Ticket Num], 
	B.[Property ID] as [Property ID - Truck_Tix], 
	C.[Lease Number] as [Lease Number - Restatements]
from 
	((Query4 A
	left join Truck_Tix B
	on A.[Ticket Num] = B.[Ticket Num])
	left join Restatements C
	on A.[Ticket Num] = C.[Truck Ticket Num])
where 
	nz(B.[Property ID], 1) <> nz(C.[Lease Number], 1)
	and B.[Property ID] is not null
	and C.[Lease Number] is not null

the last part in the where clause is only to make sure that nulls aren't compared (if you want a null in one table to count as a mismatch, or a null in both tables to also be included as a possible mismatch, then you can adjust or even omit the where clause).

the names of the queries are irrelevant of course (but the second one must use the correct name of the first one). Advise that you use actual table names and field names - and provide datatypes! - for quicker responses - otherwise we have to make the missing pieces ourselves.

 

[CodyMonster]

Xenou,

Thank you!!!

Works great.
Looks like I need to read more SQL books..