Compare Tables

[BOUKHARI]

I m VERY new to SQL I have 2 table of itemized telephone call statements made during the same month. Table 1 has all the telephone call records whereas Table 2 does not have all the calls . The common filed in both tables are dates and number dialed. Please help me to get the calls records registered in Table 1 but are not shown in table 2. please help help . thank you.

Adam

[kb.net]

Hi,

While it is always recommended to use unique keys for all tables below is the query that shall work in such case:

SELECT Phone2 + CAST(CallDate2 AS varchar) AS Criteria1, *
 FROM Table2
 WHERE (NOT ((Phone2 + CAST(CallDate2 AS varchar)) IN
 (SELECT Phone1 + CAST(CallDate1 AS varchar)
 FROM table1)))

[BOUKHARI]

Thanks a lot for your help

Tried the above query but I got the follwoing message error;

Syntax error (missing operator) in query expression

Phone2 + CAST(CallDate2 AS varchar)

Please help, thanks

[kb.net]

Hi,

What is the data type of the Phone number and CallDate in your data bases??

[hollystyles]

A better solution is to use a left join it is more efficient than using the IN clause

Select 
    t1.*
from 
    Table1 t1 
    left join Table2 t2 on t1.date_field = t2.date_field and t1.number_dialed = t2.number_dialed 
where 
    t2.date_field is null

The left join means return all records in Table1 even if there is no matching record in Table2 in which case nulls are returned for the Table2 columns. So if we specify null as the criteria in the where clause we get only records in Table1 that are unmatched, meaning they don't exist in Table2.

[BOUKHARI]

Hi

I tried this solution. It worked but the result is incomplete. more than 2000 records not included in the answer table.

Select
t1.*
from
Table1 t1
left join Table2 t2 on t1.date_field = t2.date_field and t1.number_dialed = t2.number_dialed
where
t2.date_field is null

but the result is not correct . because it gives me only records of telephone numbers that are not mentioned in Table2 at all. but there are calls to telephone numbers that exist in both table. please have a look at an extract from table 1 and table 2 below:

Table 1

ID Date Time Duration number dialed Cost
1 23/05/2007 09:27:15 AM 37 883679937260 1.56
2 25/05/2007 11:13:35 AM 79 8836793210552 0.52
3 31/05/2007 12:56:30 AM 505 8836793210552 3.36
4 31/05/2007 12:47:40 AM 433 8836793210552 2.88
5 01/05/2007 01:55:45 AM 67 8836793210552 1.44
6 02/05/2007 08:31:10 AM 223 8836793210552 1.48
7 03/05/2007 08:38:10 AM 943 8836793213112 6.28
8 10/05/2007 12:08:10 PM 103 8836793213719 2.68
9 10/05/200 12:13:40 PM 133 8836793213719 2.88
10 10/05/2007 05:10:50 PM 61 8836793213719 1.4
11 10/05/2007 02:41:10 PM 43 8846793213718 0.28
12 10/05/2007 05:07:45 PM 67 8836793213756 0.44

Table2
ID Date Time Duration number dialed Cost
1 23/05/2007 09:27:15 AM 37 883679937260 1.56
2 25/05/2007 11:13:35 AM 79 8836793210552 0.52
3 02/05/2007 08:31:10 AM 223 8836793210552 1.48
4 03/05/2007 08:38:10 AM 943 8836793213112 6.28
5 10/05/2007 12:08:10 PM 103 8836793213719 2.68
6 10/05/2007 12:13:40 PM 133 8836793213719 2.88

Please help me. thank you

[hollystyles]

Sorry I don't get what you want. First you say:
The common filed in both tables are dates and number dialed. Please help me to get the calls records registered in Table 1 but are not shown in table 2

Then you say:it gives me only records of telephone numbers that are not mentioned in Table2 at all. but there are calls to telephone numbers that exist in both table

To me these are mutualy exclusive.

Given the sample data you have posted, please post what you want to see in the result set. Then perhaps I might be able to figure out what you're after.

[BOUKHARI]

Hi,

I want to get records that are in table 1 but do not exist in table 2. i mean records not (number dialed). bcz there are records of calls made to same telephone number but do not have the same duration nor same cost .

From Table 1 and Table 2, pls see e.g of what I m after in table 3 below:

Table 3:

ID Date Time Duration (SEC) number dialed Cost
3 31/05/2007 12:56:30 AM 505 8836793210552 3.36
4 31/05/2007 12:47:40 AM 433 8836793210552 2.88
10 10/05/2007 05:10:50 PM 61 8836793213719 1.4
11 10/05/2007 02:41:10 PM 43 8836793213719 0.28
12 10/05/2007 05:07:45 PM 67 8836793213756 0.44

Table 1

ID Date Time Duration number dialed Cost
1 23/05/2007 09:27:15 AM 37 883679937260 1.56
2 25/05/2007 11:13:35 AM 79 8836793210552 0.52
3 31/05/2007 12:56:30 AM 505 8836793210552 3.36
4 31/05/2007 12:47:40 AM 433 8836793210552 2.88
5 01/05/2007 01:55:45 AM 67 8836793210552 1.44
6 02/05/2007 08:31:10 AM 223 8836793210552 1.48
7 03/05/2007 08:38:10 AM 943 8836793213112 6.28
8 10/05/2007 12:08:10 PM 103 8836793213719 2.68
9 10/05/200 12:13:40 PM 133 8836793213719 2.88
10 10/05/2007 05:10:50 PM 61 8836793213719 1.4
11 10/05/2007 02:41:10 PM 43 8846793213718 0.28
12 10/05/2007 05:07:45 PM 67 8836793213756 0.44

Table2
ID Date Time Duration number dialed Cost
1 23/05/2007 09:27:15 AM 37 883679937260 1.56
2 25/05/2007 11:13:35 AM 79 8836793210552 0.52
3 02/05/2007 08:31:10 AM 223 8836793210552 1.48
4 03/05/2007 08:38:10 AM 943 8836793213112 6.28
5 10/05/2007 12:08:10 PM 103 8836793213719 2.68
6 10/05/2007 12:13:40 PM 133 8836793213719 2.88

Thanks much

[hollystyles]

Ok then you need to join *all* the fields.

Select 
    t1.*
from 
    Table1 t1 
    left join Table2 t2 on t1.[Date] = t2.[Date] 
    and t1.[Time] = t2.[Time] 
    and t1.[Duration] = t2.[Duration] 
    and t1.[number dialed] = t2.[number dialed] 
    and t1.Cost = t2.Cost 
where 
    t2.[Date] is null

Note:
The [] around column names are because I think some of those are reserved words in SQL and that is how you escape them. I don't know if they are your actual column names.