Subqueries instead of Looping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Subqueries instead of Looping

Author

Topic

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-23 : 14:25:04

This may not be the proper forum for asking this, but I'm trying to write a Select statement that compares two Visual Foxpro tables to find any differences between them.

I have a VFP CUST table that does NOT have a timedate stamp field. In order to see if it has changed since a particular date, a copy of that table is made and is called CUST_DUPE. In the future, a comparison of these tables is made in order to see if any records have changed since the Cust_dupe table was made.

In order to do this comparison, I do a simple Select * from cust and then, using this recordset, loop through each record with a select * from cust_dupe where field1<>field1, field2<>field2, etc.

Obviously, this takes a very long time as there are 35,000 records.

Is there a subquery way to do the above which would take much less time?

Thanks in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-03-23 : 14:41:42

This should be a little faster:

SELECT C.*
FROM CUST C LEFT JOIN CUST_DUPE D
ON C.field1=D.field1 AND C.field2=D.field2 AND C.field3=D.field3
WHERE D.field1 IS NULL

Just add the remaining columns to the JOIN clause and leave the WHERE as it is. The LEFT JOIN will pull everything from the CUST table, and will JOIN to the dupe table. The WHERE clause tests the dupe table for rows that don't have a match (return Null) and will filter out matching rows, leaving only the unmatched rows in CUST. This is how MS Access works with it's Find Unmatched wizard. There are other ways to do this using EXISTS, but I can never remember how to write them.

Jay or Arnold or Jeff might be able to help.

The only problem with this is that it won't distinguish between a newly inserted row and one that had one or more columns modified, nor will it show rows that were deleted.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-23 : 15:06:38

A problem with joins is also NULLS will not join together, so watch out for that.

I always use GROUP BY's for comparisons:

select Min(Table) as Table, (fields)
FROM
(
SELECT 'Table1' as Table, (table1 fields)
FROM Table1
UNION ALL
SELECT 'Table2' as Table, (table2 fields -- must line up)
FROM Table2
)
GROUP BY (all fields)
HAVING COUNT(*) = 1

That will return all records in either table that don't match completely, or that don't exist in the other table. Runs quickly, too.

- Jeff

Edited by - jsmith8858 on 03/23/2003 15:06:52

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-23 : 15:23:58

Thanks for the reply. As a novice, I'm trying to understand your code. Note I've included some of my data in your code.

1. select Min(Table) as Table, (fields)

I'm not clear what Min(Table) and Table refer to and what I'm supposed to include in (fields)

2. FROM
(
SELECT 'Table1' as Table, (custnumber, firstname, lastname, etc)
FROM Cust

I'm not sure what I'm supposed to put inside the single quotes and why.

3. UNION ALL
SELECT 'Table2' as Table, (custnumber, firstname, lastname, etc)
FROM CustDupe
)
Ditto. I'm not sure what I'm supposed to put inside the single quotes and why.

4. GROUP BY (custnumber, firstname, lastname, etc)

Do I put all of my fields that are in this table in the GROUP BY?

HAVING COUNT(*) = 1

Thanks for your help.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-24 : 00:45:23

quote:

Thanks for the reply. As a novice, I'm trying to understand your code. Note I've included some of my data in your code.

1. select Min(Table) as Table, (fields)

I'm not clear what Min(Table) and Table refer to and what I'm supposed to include in (fields)

select Min(Table) as Table, custnumber, firstname, lastname, etc

quote:

2. FROM
(
SELECT 'Table1' as Table, (custnumber, firstname, lastname, etc)
FROM Cust

I'm not sure what I'm supposed to put inside the single quotes and why.

select 'Cust' as Table, custnumber, firstname, lastname, etc

quote:

3. UNION ALL
SELECT 'Table2' as Table, (custnumber, firstname, lastname, etc)
FROM CustDupe
)
Ditto. I'm not sure what I'm supposed to put inside the single quotes and why.

Select 'CustDupe' as Table, custnumber, firstname, lastname, etc

quote:

4. GROUP BY (custnumber, firstname, lastname, etc)

Do I put all of my fields that are in this table in the GROUP BY?

HAVING COUNT(*) = 1

Thanks for your help.

yes put all your fields from both table cust & custDupe

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 08:23:09

Thanks for the reply. What exactly does Min(Table) do? I'm familiar with Min() but only as it relates to retrieving the Minimum value of a column.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-24 : 08:29:55

Table is just a field in each of the subqueries, returning the table name of each table so you can tell which one the data is coming from. You have to assign a value to this field yourself, as I have done:

"Table1" as Table

Note that Table is a reserved word in SQL so you should probably put [] around it so SQL doesn't take it literally.

We can't GROUP BY the table field, because EVERY group will then have a count(*) of 1, but we would like to return the table name for rows that don't match. Rows that don't match will only have a count of 1, so there is only 1 value for the table field, so taking the MIN() or the MAX() is arbitrary -- I chose MIN but MAX is fine as well.

Sorry, this can be a complicated technique ... I can explain more as needed. Really dissect it carefully, though, and you can probably figure it out.

- Jeff

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 08:40:56

Thanks Jeff, that helps greatly!

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 08:43:52

Is there a good tutorial or book that explains the syntax of complex Selects? I have Ben Forta's Teach Yourself SQL but it doesn't get into Selects with this much complexity.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-03-24 : 08:47:08

quote:

Is there a good tutorial or book that explains the syntax of complex Selects? I have Ben Forta's Teach Yourself SQL but it doesn't get into Selects with this much complexity.

So a book called Teach Yourself SQL doesn't cover SELECTs? I guess I'm not surprised. I don't think SELECT is covered is the MCDBA program either... (If there was an emoticon for rolling-ones-eyes, I would put that here)

Start with Books Online to learn the syntax. Pick up Joe Celko's SQL for Smarties for tips and tricks. I think Rob like a book by some guy named Ken something ... he'll be along shortly to push that book on you, I'm sure....

Jay White
{0}

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 08:55:07

Yes, Teach Yourself SQL is all about Selects, Inserts, and Updates. However, it doesn't cover or analyze the code just discussed. I'll check out your recommendation. Thanks!

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 10:20:19

Ok, I'm still trying to get this to work. Unfortunately it must work on VFP tables instead of SQL 2000 tables.

It's been recommended to me on the VFP forums to break apart the statement.

1. SELECT 'cust' as table, custnum, firstname, lastname FROM cust
UNION ALL
SELECT 'cust_dupe' as table, custnum, firstname, lastname FROM cust_dupe
INTO CURSOR crsTemp

2. select Min(table) as table, custnum, firstname, lastname FROM crsTemp
GROUP BY custnum, firstname, lastname
HAVING COUNT(*) = 1

However this retrieves 5,000 + records instead of the 7 records that I know have changed.

What might I be doing wrong?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-03-24 : 10:24:23

Why are you using a cursor !!????

If you'd like to use a middle step, (or need to due to linked tables to VPF), insert all of your data into 1 table, then then query that table GROUPING BY all fields (except for the table name). If the GROUP BY sums up only 1 row, there is not an exact matching row in both tables ...

Consider:
Table, CustID, FName, LastName
T1, 1, Jeff, Smith
T2, 1, Jeff, Smith
T1, 2, Rob, Volk
T2, 2, Jay, White
T1, 3, Arnold, Fribble
T1, 4, Jerry, Seinfeld
T2, 4, Jerry, Seinfeld

If you SELECT Min(Table), CustID, Fname, Lastname, count(*) from the above, and GROUP BY CustID, Fname, Lastname

you get

T1, 1, Jeff, Smith, 2
T1, 2, Rob, Volk, 1
T2, 2, Jay, White,1
T1, 3, Arnold, Fribble, 1
T1, 4, Jerry, Seinfeld, 2

Now, filter out the ones with a count(*) of 2 using the HAVING clause, and you get:

T1, 2, Rob, Volk, 1
T2, 2, Jay, White,1
T1, 3, Arnold, Fribble, 1

and if you examine the above, you see those are the rows that are not exactly the same in both tables.

No need for a cursor ....

- Jeff

Edited by - jsmith8858 on 03/24/2003 10:31:03

RichardSteele
Posting Yak Master

160 Posts

Posted - 2003-03-24 : 10:45:33

Actually I was using a query of a query in ColdFusion. However, that's similar to using a cursor in VFP.

I'll try your alternative suggestion. That should work fine. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources