Re: [sqlite] SQLl question

[Evan Burkitt]

On 05/15/2009 03:41, Dennis Cote wrote:
>
> 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.
>
Thanks to Dennis, John and Patty for your answers. I had the feeling 
this was not difficult, but up to now I've managed to avoid learning 
about SQL unions.

This isn't my database, so I live with what's there. In fact, P and E 
aren't even tables. They're subqueries, and this solution is going to be 
part of a larger query that extracts data from one (complex and large) 
DB2 database and puts it into another, simpler one in SQLite. Both 
source and target are already specified and the program design is such 
that the adaptation must be done completely in the query.

Thanks again.

-evan
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLl question

[Patty Lindsay]

Try

select n.name, p.type, p.addr
from p
join name on p.id = n.id
union
select n.name, e.type, e.addr
from e
join name on e.id = n.id

Patty

On Fri, May 15, 2009 at 12:14 AM, 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?
>
> -evan
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLl question

[John Machin]

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

Re: [sqlite] SQLl question

[Dennis Cote]

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