SELECT question

[Ruslan Zasukhin]

on 4/3/03 9:23 AM, Ken Ray at kray at sonsothunder.com wrote:

> Ruslan,
> 
> I'm trying to put together a SELECT statement and I can't quite get how
> to do it in Valentina. Here's the situation: I have two tables, one
> called Entities and one called Phones. Each Entity is a person or
> company, and can have one or more Phones. For the purpose of my
> question, assume the following table structure (field names are listed
> below the table name):
> 
> Entities
> --------
> FullName
> CompanyName
> 
> 
> Phones
> ------
> EntityLink
> PhoneNumber
> PhoneType
> Order
> 
> 
> The "EntityLink" field in the Phones table holds the RecID of a related
> Entity. The "Order" field holds a number indicating the priority order
> to display an Entity's phone numbers in my interface. So for example, if
> Entity #100 had three phone numbers (work, home and cell) and they were
> to be displayed in the order "cell" then "work" then "home", the records
> in the Phones table would be:
> 
> 100 (tab) 8005551212 (tab) cell (tab) 1
> 100 (tab) 8005551213 (tab) work (tab) 2
> 100 (tab) 8005551214 (tab) home (tab) 3
> 
> The problem is that I want to retrieve a set of records that will get
> the FullName and CompanyName from the Entities table, and the
> PhoneNumber whose Order=1, and then the PhoneNumber whose Order=2. So
> for our Entity #100 (who might have the FullName "Ken Ray" and the
> CompanyName "ABC Inc."), the retrieved record from the SELECT would be:
> 
> Ken Ray (tab) ABC Inc. (tab) 8005551212 (tab) 8005551213
> 
> I've been able to get the first three columns with this query:
> 
> SELECT Entities.FullName,Entitites.CompanyName,Phones.PhoneNumber
> FROM Entities,Phones
> WHERE Entities.RecID = Phones.EntityLink
> AND Phones.Order = 1
> 
> ... but I can't figure out how to get the second phone number. And my
> query will also NOT select those Entities that don't have a phone
> number; but like a normal address book, I need to show ALL the Entities
> in the Entities table, and if they don't have a #1 or #2 phone number,
> it should just show blank in those columns.
> 
> How can I get this data with a single query? And if I can't, what
> queries do I need to run to get this info?

Hi Ken,

1) I think you cannot get this with single query.
It seems in Standard SQL this can do CROSS JOIN, but I do not remember now
exactly. In any case Valentina do not support CROSS JOIN.

2) May be you can try run around this using query

 SELECT Entities.FullName,Entitites.CompanyName,Phones.PhoneNumber
 FROM Entities,Phones
 WHERE Entities.RecID = Phones.EntityLink
 AND Phones.Order >=2


And now you can write own procedure that CORRECTLY fill your listbox or
other control. You need read main info from e.g. First record, and second
phone from second row. Then main info from third and second phone from
forth, and so on.

3) To show entities with no phones you need to use LEFT JOIN

 WHERE Entities.RecID *= Phones.EntityLink


-- 
Best regards,
Ruslan Zasukhin      [ I feel the need...the need for speed ]
-------------------------------------------------------------
e-mail: ruslan at paradigmasoft.com
web: http://www.paradigmasoft.com

To subscribe to the Valentina mail list go to:
http://listserv.macserve.net/mailman/listinfo/valentina
-------------------------------------------------------------