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;
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.
HTH
Dennis Cote
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
