Multiple recordsets?

[MDW]

Hey all

I'm not sure if there's anything that can be done about this or not. I'm displaying the contents of two different tables (using Access for development purposes, but when I go live we'll convert it o SQL Server) on an ASP page. One table, called NewAccts, contains (surprise) new accounts. The other table contains account challenges, and it's called Deducts. The two tables have a few columns in common, with AccountNum forming the PK (NewAccts) - FK (Deducts) relationship between the two tables

Not every account will have a record in Deducts. On my display page, I want to display ALL accounts, ordered by a date field in the NewAccts table. If there is a matching record in the Deducts table, I want to display it directly beneath the record from NewAccts.

The way I currently do this is I loop through the NewAccounts table, and for each record, check the Deducts table for a match. I have to think this is terribly inefficient and I'd LIKE to find something that employs just a single recordset (so that I can use GetRows and be done with it). Is this a UNION query situation? I'm honestly not sure if this can even be done - or if I've explained what I want in enough detail.

I hope someone can shed some light on this for me.

[Aaron Bertrand [MVP]]

[url]http://www.aspfaq.com/2241[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]


"MDW" <anonymous@discussions.microsoft.com> wrote in message
news:9C263BD1-0EA3-4D36-8594-BFC4314EC43D@microsoft.com...

> Hey all,
>
> I'm not sure if there's anything that can be done about this or not. I'm
> displaying the contents of two different tables (using Access for
> development purposes, but when I go live we'll convert it o SQL Server) on
> an ASP page. One table, called NewAccts, contains (surprise) new accounts.
> The other table contains account challenges, and it's called Deducts. The
> two tables have a few columns in common, with AccountNum forming the PK
> (NewAccts) - FK (Deducts) relationship between the two tables.
>
> Not every account will have a record in Deducts. On my display page, I
> want to display ALL accounts, ordered by a date field in the NewAccts
> table. If there is a matching record in the Deducts table, I want to
> display it directly beneath the record from NewAccts.
>
> The way I currently do this is I loop through the NewAccounts table, and
> for each record, check the Deducts table for a match. I have to think this
> is terribly inefficient and I'd LIKE to find something that employs just a
> single recordset (so that I can use GetRows and be done with it). Is this
> a UNION query situation? I'm honestly not sure if this can even be done -
> or if I've explained what I want in enough detail.
>
> I hope someone can shed some light on this for me.

[Bullschmidt]

If you use LEFT JOIN instead of INNER JOIN in a SQL statement you can
show all records in one table (perhaps that match some criteria) and
only those records that are linked in the other table...

Best regards,
J. Paul Schmidt, Freelance ASP Web Consultant
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Devdex [url]http://www.devdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!