SQL code to combine 2 queries

Question

SQL code to combine 2 queries

goodfu

Old Hand

Points: 383
More actions
December 19, 2004 at 1:43 pm

#183715

What I’m trying to do is really very simple. I want to join 2 queries. The 2 queries are created from 2 one-to-many relationships and the main table in both relationships is the same table. The problem is that the coding for the 2 queries is so complicated I don’t know how to join the two of them or if it’s even possible.

The first query:

Basically what’s going on is I’m trying to make one-to-one out of one-to-many. Consider:

tblSubscriptions
fldSubscriptionsSubscrno                   Number      Subscription no.     (Primary key)
fldSubscriptionsSubscriberfirstname   Text            Subscriber first name
fldSubscriptionsSubscriberlastname    Text            Subscriber last name

tblSubscriptions has a one-to-many relationship with tblSubscriptionsAddresses.

tblSubscriptionsAddresses
fldSubscriptionsAddressesSubscrno   Number       Subscription no.   (Primary key)
fldSubscriptionsAddressesEffdate      Date            Effective date        (Primary key)
fldSubscriptionsAddressesHouseno    Text            House number
fldSubscriptionsAddressesStreetname Text         Street name

Fields which aren’t involved have been omitted. What I want is every record in tblSubscriptions combined (by subscription number) with the record in tblSubscriptionsAddresses that has the most recent date up to but not including today. This is for a list in a Visual Basic program. I am using Access, planning to switch to SQL server. The list needs to display 1 line for every record in tblSubscriptions. This line will show, in addition to the subscriber’s name, the subscription’s current house no. and street name, which are taken from the record in tblSubscriptionsAddresses for that subscription no. which has the highest effective date up to today. The resulting list must be sorted by the subscriber’s name (last name first). All records in tblSubscriptions must be selected, even if there is no record in tblSubscriptionsAddress (but not the other way around). The below SQL works for that. It is compicated, it may or may not be the best way, but it works.:

SELECT S.fldSubscriptionsSubscriberlastname & ", " & S.fldSubscriptionsSubscriberfirstname AS Expr1, S.fldSubscriptionsSubscrno, S.fldSubscriptionsAddressesEffdate, S.fldSubscriptionsAddressesHouseno, S.fldSubscriptionsAddressesStreetname
FROM (SELECT tblSubscriptions.fldSubscriptionsSubscrno,
NZ(tblSubscriptionsAddresses.fldSubscriptionsAddressesEffdate,'') As fldSubscriptionsAddressesEffdate,
tblSubscriptions.fldSubscriptionsSubscriberlastname,
tblSubscriptions.fldSubscriptionsSubscriberfirstname,
tblSubscriptionsAddresses.fldSubscriptionsAddressesStreetname,
tblSubscriptionsAddresses.fldSubscriptionsAddressesHouseno,
NZ(( SELECT TOP 1 t3.fldSubscriptionsAddressesEffdate
FROM tblSubscriptionsAddresses t3 WHERE t3.fldSubscriptionsAddressesSubscrno = tblSubscriptions.fldSubscriptionsSubscrno
AND t3.fldSubscriptionsAddressesEffdate < DATE() ORDER BY t3.fldSubscriptionsAddressesEffdate DESC),'') As [CurDate]
FROM tblSubscriptions
LEFT JOIN tblSubscriptionsAddresses ON (tblSubscriptions.fldSubscriptionsSubscrno = tblSubscriptionsAddresses.fldSubscriptionsAddressesSubscrno AND tblSubscriptionsAddresses.fldSubscriptionsAddressesEffdate < DATE())) S
WHERE ((S.CurDate)=.[fldSubscriptionsAddressesEffdate])
ORDER BY S.fldSubscriptionsSubscriberlastname, S.fldSubscriptionsSubscriberfirstname

Note the TOP 1 part which selects the right effective date from tblSubscriptionsAddresses.

The second query:

The second query is similar to the first. This time, we are to joining tblSubscriptions to tblSubscriptionsRoutes. (Refer to tblSubscriptions above.)

tblSubscriptionsRoutes
fldSubscriptionsRoutesSubscrno   Number   Subscription number   (Primary key)
fldSubscriptionsRoutesEffdate       Date        Effective date             (Primary key)
fldSubscriptionsRoutesRouteno    Text       Route number (Foreign key to tblRoutes)

In addition, we have to get a couple of more fields from 2 more tables:

tblRoutes
fldRoutesRouteno          Text           Route number     (Primary key)
fldRoutesPublicationid      Number     Publication i.d.     (Foreign key to tblPublications)

tblPublications
fldPublicationsId               Number       Publication i.d.    (Primary key)
fldPublicationsName         Text             Publication name

This also works:

SELECT S.fldSubscriptionsSubscriberlastname & ", " & S.fldSubscriptionsSubscriberfirstname AS Expr1, S.fldSubscriptionsSubscrno, S.fldSubscriptionsRoutesEffdate, S.fldSubscriptionsRoutesRouteno, tblRoutes.fldRoutesPublicationid, (SELECT tblPublications.fldPublicationsName FROM tblPublications WHERE tblPublications.fldPublicationsId = tblRoutes.fldRoutesPublicationid)
FROM (SELECT tblSubscriptions.fldSubscriptionsSubscrno,
NZ(tblSubscriptionsRoutes.fldSubscriptionsRoutesEffdate,'') As fldSubscriptionsRoutesEffdate,
tblSubscriptions.fldSubscriptionsSubscriberlastname,
tblSubscriptions.fldSubscriptionsSubscriberfirstname,
tblSubscriptionsRoutes.fldSubscriptionsRoutesRouteno,
NZ(( SELECT TOP 1 t2.fldSubscriptionsRoutesEffdate
FROM tblSubscriptionsRoutes t2 WHERE t2.fldSubscriptionsRoutesSubscrno = tblSubscriptions.fldSubscriptionsSubscrno
AND t2.fldSubscriptionsRoutesEffdate < DATE() ORDER BY t2.fldSubscriptionsRoutesEffdate DESC),'') As [CurDate]
FROM tblSubscriptions
LEFT JOIN tblSubscriptionsRoutes ON (tblSubscriptions.fldSubscriptionsSubscrno = tblSubscriptionsRoutes.fldSubscriptionsRoutesSubscrno AND tblSubscriptionsRoutes.fldSubscriptionsRoutesEffdate < DATE())) S LEFT JOIN tblRoutes ON S.fldSubscriptionsRoutesRouteno = tblRoutes.fldRoutesRouteno
WHERE ((S.CurDate)=.[fldSubscriptionsRoutesEffdate])
ORDER BY S.fldSubscriptionsSubscriberlastname, S.fldSubscriptionsSubscriberfirstname

My question:

Is there any way to combine these 2 queries? The result will simply be a list containing information about subscriptions, one line to a subscription, one subscription to a line. It is important to understand that tblSubscriptionsAddresses and tblSubscriptionsRoutes are independent of one another. There is no necessary coordination of dates between these 2 tables.

I would also like eventually to be able to filter the finished list.

I can post the actual Access database if anybody wants.

Thanks for looking.

Robert

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Quick solution ...

Haven't looked too much at your code - don't have two hours spare, but have you thought about creating 2 views (one for each query) and then joining those views with another new view? This will remove some of the complexities of trying to write everything as a single statement.

Then you can select directly from the new view without having to pass loads of SQL in your app. Also, if necessary, you can modify the view without having to re-release the software.

Regards

Phil

goodfu Old Hand Points: 383 More actions · Answer 2

Sounds interesting. I'll give it a shot, although I have never created a view. I assume you can create views with Access (although I'm planning to switch to Sql Server (dumb, I know (not sql server, using Access when you're planning not to use it))).

Thanks.

Robert

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Aha. No, views do not exist in Access - the closest equivalent is a straightforward query.

You will notice in Access when you are building a query (using the 'create query in Design view' option) that you are presented with a 'show tables' screen that asks you to select the tables comtaining the data that you are interested in. What you may not have noticed on this screen is that there is also a 'queries' tab. If you click on this tab, you will see a list of existing SELECT queries - and you can treat these queries much as you would treat a single physical table, selecting and joining on the available fields.

So you can mix tables and queries within a query to get the results you require.

When you move to SQL Server, the beauty of views is that

a) they usually perform better than straight SQL Select statements

b) the processing work is done on the server, rather than on the client PC (which is what happens with Access)

Cheers

Phil

David Burrows SSC Guru Points: 65144 More actions · Answer 4

SELECT S.fldSubscriptionsSubscriberlastname & ", " & S.fldSubscriptionsSubscriberfirstname AS Expr1,

S.fldSubscriptionsSubscrno,

SA.fldSubscriptionsAddressesEffdate,

SA.fldSubscriptionsAddressesHouseno,

SA.fldSubscriptionsAddressesStreetname,

R.fldRoutesRouteno,

P.fldPublicationsName

FROM ((tblSubscriptions S

LEFT JOIN

[SELECT SA2.fldSubscriptionsAddressesSubscrno,

MAX(SA2.fldSubscriptionsAddressesEffdate) AS fldSubscriptionsAddressesEffdate

FROM tblSubscriptionsAddresses AS SA2

GROUP BY SA2.fldSubscriptionsAddressesSubscrno]. AS SA1

ON S.fldSubscriptionsSubscrno = SA1.fldSubscriptionsAddressesSubscrno)

LEFT JOIN tblSubscriptionsAddresses SA ON (SA1.fldSubscriptionsAddressesEffdate = SA.fldSubscriptionsAddressesEffdate)

AND (SA1.fldSubscriptionsAddressesSubscrno = SA.fldSubscriptionsAddressesSubscrno))

LEFT JOIN (

[SELECT SR2.fldSubscriptionsRoutesSubscrno,

MAX(SR2.fldSubscriptionsRoutesEffdate) AS fldSubscriptionsRoutesEffdate

FROM tblSubscriptionsRoutes SR2

WHERE SR2.fldSubscriptionsRoutesEffdate < DATE()

GROUP BY SR2.fldSubscriptionsRoutesSubscrno]. AS SR1

LEFT JOIN (tblSubscriptionsRoutes SR

LEFT JOIN (tblRoutes R

LEFT JOIN tblPublications P ON R.fldRoutesPublicationid = P.fldPublicationsId)

ON SR.fldSubscriptionsRoutesRouteno = R.fldRoutesRouteno)

ON (SR1.fldSubscriptionsRoutesEffdate = SR.fldSubscriptionsRoutesEffdate)

AND (SR1.fldSubscriptionsRoutesSubscrno = SR.fldSubscriptionsRoutesSubscrno))

ON S.fldSubscriptionsSubscrno = SR1.fldSubscriptionsRoutesSubscrno;

Far away is close at hand in the images of elsewhere.
Anon.

goodfu Old Hand Points: 383 More actions · Answer 5

Thanks for posting the code, David.

When I tried to save it, I got thrown out of Access. I was careful to change the '[' around the 2 SELECT statements to '('. I am able to save the other queries that I have. What gives?

Robert

David Burrows SSC Guru Points: 65144 More actions · Answer 6

Just tried again with my Access 2000 db and can save the query and run it OK. Wonder if it is something to do with your database that I have not got in mine (I created a test one using the info you supplied).

Far away is close at hand in the images of elsewhere.
Anon.

goodfu Old Hand Points: 383 More actions · Answer 7

Phil,

I created a query that calls my 2 queries and that is working just fine in Access. Don't think it will work in VB though.

David,

It's strange. Access seems to be working okay (see above). I do have a lot of tables and queries in it (most of the queries are test junk). I'm going to try creating a new database to see what happens.

goodfu Old Hand Points: 383 More actions · Answer 8

It happened again with my new database! I don't suppose you could post your sample db?

Phil Parkin SSC Guru Points: 247180 More actions · Answer 9

Hmmmm ... maybe you should accelerate your migration to SQL Server and all of your problems (well, this one anyway) will go away

I'd be surprised if VB cannot execute native Access queries, but it's been a while since I wrote any stuff like that, so I can't remember for sure.

Good luck

Phil