How to select nth (or below) address record

[mikeopolo]

Hi:

As a general observaton I'd say addresses cause more coding trouble than any other piece of data...here's my current issue.

I have an address table which can contain up to 5 addresses per customer. The address table has a location field (1 = mailing, 2 = street, etc.

I want to select the 2nd (street) address or the 1st (mailing) if no street address exists, or return null if there are no adddresses.

The code I have so far is:

SELECT distinct addr.cardrecordid, addr.location, cus.name, ;
streetline1 as delivery1,;
FROM customers as cus;
LEFT OUTER JOIN address as addr ON addr.cardrecordid= cus.cardrecordid ;
where addr.location <=2 OR addr.location is null ;
GROUP BY addr.cardrecordid;
INTO CURSOR tmpaddr readwrite

From the resulting table, this *looks* like it will work, but will it? It's the join using <= that I'm not sure about - what's the guarantee of getting the 2nd adddress if they both exist?

Regards
Mike

 

[Olaf Doschke]

If you number your adresses in the location field, location = 1: main address, location = 2: secondary address, this will work, yes.

ON addr.cardrecordid = cus.cardrecordid in he first place will get you all addresses. The guarantee thet you get the 2nd adress if one exists is the <=2 (in comparison to <2).

the WHERER clauses "OR addr.location is NULL" will guarantuee you the customer head data, even if there are no address records.

It's totally okay.

Bye, Olaf.

 

[Olaf Doschke]

you'll only miss the second stored address, if location=2 could be deleted and location=3 exists.

Then you'd need a subselect with ...TOP 2 ... ORDER BY location, which would get you 0,1 or 2 addres records, even 3 or 4 if there would be several with the same location number.

Bye, Olaf.

 

[StewartUK]

I agree with your observation about addresses being complicated to code for!

As Olaf says, your query will return both mailing and street addresses where they both exist.

I see you have a GROUP BY - I think I'm right in saying that this won't necessarily ensure that the street address is kept rather than the mailing address.

What version of VFP are you using?

Hope that helps,

Stewart
PS If you want to get the best response to a question, please check out FAQ184-2483 first.

 

[Olaf Doschke]

Hi Stwart, thanks for catching that. The group by is not ansi compliant, so mikeopolo seems to use VFP7 or earlier
or works with ENGINEBEHAVIOUR 70

This will give him exactly one record, but he might want the one with the MIN(location) with the values belonging to the record with the min location. For that, he'll need two selects in his older VFP version or a subselect.

Bye, Olaf.

 

[mikeopolo]

Hi all

I'm using version 9 but with "eb70" turned on. The combination of group by and distinct definitely gets me only one address each time (I was having trouble with that), and it 'seems' to pick #2 if there is one, or #1 if no #2 exists. I'm still checking to see what it does if both exist.

Regards
Mike

 

[Olaf Doschke]

I'd simply skip the group by and take the first record of the result as the result...

Bye, Olaf.

 

[mikeopolo]

I've discovered that customers with no addresses were not being reported, due to an error in my original select statement; so just to set the record straight and not mislead future readers:

SELECT distinct CUS.cardrecordid, addr.location, cus.name, ;
streetline1 as delivery1,;
FROM customers as cus;
LEFT OUTER JOIN address as addr ON addr.cardrecordid= cus.cardrecordid ;
where addr.location <=2 OR addr.location is null ;
GROUP BY CUS.cardrecordid;
INTO CURSOR tmpaddr readwrite