SELECT question

[Ken Ray]

This is very interesting, Brian. I didn't know about table aliases. Good
stuff! However the query you provided brings back all the Entities that
have BOTH a #1 phone AND a #2 phone; I need to bring back ALL the
Entities, and list #1 phones if they have them, and #2 phones if they
have them. That is, if I have 7500 Entities records, I need to get back
7500 lines of data with the Phone 1 and Phone 2 columns either (a)
containing data in both columns, (b) containing data in one column, or
(c) not containing data in either column. So I'd need to get something
like this:

 Ken Ray (tab) ABC Inc. (tab) 8005551212 (tab) 8005551213
 Jim Bim (tab) ABC Inc. (tab) 8001112233 (tab) 
 Jon Doe (tab) Doe Inc. (tab)            (tab) 8002223334
 Don Row (tab) Row Inc. (tab)            (tab)

Any ideas?

Ken Ray
Sons of Thunder Software
Email: kray at sonsothunder.com
Web Site: http://www.sonsothunder.com/ 

> -----Original Message-----
> From: valentina-bounces at lists.macserve.net 
> [mailto:valentina-bounces at lists.macserve.net] On Behalf Of 
> Yennie at aol.com
> Sent: Thursday, April 03, 2003 12:25 PM
> To: valentina at lists.macserve.net
> Subject: Re: SELECT question
> 
> 
> 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
> ------------------------------- 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net 
> http://lists.macserve.net/mailman/listinfo/val> entina
>