On 15/05/2009 2:37 PM, Dennis Cote wrote: > Evan Burkitt wrote: >> This isn't a Sqlite question per se, but I know there are some SQL gurus >> here who might have some insight into this problem. I apologize for >> being off-topic; I can be shameless when I need help. :)> >> >> I have three tables, N, P and E. N contains the fields id and name. The >> other two each contain the fields id, type and addr. P holds phone >> numbers, E email addresses. In P, the type field is always 'phone'; in >> the P it is always 'email'. They are all related on id. >> >> I want to build a single query that will return a result set consisting >> of N.name, P/E.type and P/E.addr. That is, it contains the like-named >> fields of both P and E. For example: >> >> -name-------- -type--- -addr--------- >> "John Smith", "phone", "123-555-1212" >> "John Smith", "email", "john.sm...@domain.com" >> "Bill Jones", "phone", "123-555-1213" >> "Jane Johnson", "email", "j...@anotherdomain.com" >> >> and so forth. The order of the names and types is not important. >> >> Is this possible? >> >> > > This (untested) SQL should do what you want. > > select N.name as name, C.type as type, C.addr as addr > from N > join (select id, type, addr from P > union > select id, type, addr from E) as C > on C.id = N.id > order by N.name, C.type;
or slightly differently: select N.name, P.type, P.addr from N join P on N.id = P.id union select N.name, E.type, E.addr from N join E on N.id = E.id order by 1, 2; > > The union combines all the data from your P and E tables so they can be > joined to the N table using the id filed. The result is sorted by the > order by clause which you could drop if you really don't care about the > order of the results. > > Your database would be simpler if you simply combined these two tables > into a single table in the first place. It would eliminate the need to > combine them for this type of query. The tables already have a type > field to distinguish the email adresses from the phone numbers, so there > is no need to put them in separate tables. Very good advice. Evan, consider what you would have to do if you wanted to distinguish what sub-type of phone number (work, home, cell aka mobile, work fax, home fax) and what type of e-mail (home, work) plus other contact methods (skype, pager, ...) --- this is not an unreasonable requirement at all for a contacts database, even a home-grown one. Cheers, John _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
