SELECT question

[Yennie at aol.com]

Unless I'm missing something here, it seems like there is an easier way than 
cross joins (which Valentina won't do and I've never had to use anywhere). 
You just have to grab more than one row of data- where it gets icky is 
getting everything on one line, but that's usually not vital.

SELECT FullName,CompanyName,PhoneType,PhoneNumber FROM Entities,Phones WHERE 
(Phones.EntityLink *= Entities.RecID) ORDER BY 
CompanyName,FullName,Phones.Order

This should give you one row for each number (where an empty column means no 
numbers):

Ken Ray (tab) ABC Inc. (tab) home (tab) 8005551212
Ken Ray (tab) ABC Inc. (tab) cell     (tab) 8005551213
Jim Bim (tab)   ABC Inc. (tab) home (tab) 8005551214
Jon Doe   (tab) Doe Inc   (tab)           (tab)

Is this what you were looking for?

If you really wanted say, 2 numbers on one row, you could do something ickier 
with table aliases:

SELECT 
FullName,CompanyName,p1.PhoneType,p1.PhoneNumber,p2.PhoneType,p2.PhoneNumber 
FROM Entities,Phones p1,Phones p2 WHERE (p1.EntityLink *= Entities.RecID) AND 
(p1.Order = 1) AND (p2.EntityLink *= Entities.RecID) AND (p2.Order = 2) ORDER 
BY CompanyName,FullName,Phones.Order

I dunno what Valentina will make of that query, but I believe it's valid.

HTH.


> > 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.
> 





------------------------------
Brian Yennie
Chief Technology Officer
QLD Learning, LLC
www.QLDLearning.com

PH: (904)-997-0212
EMAIL: Yennie at aol.com
-------------------------------