Re: [sqlite] How do I coalesce values in a join?
D. Richard Hipp said: >Can anybody suggest some kind of extended SQL syntax that >could be added to SQLite that would make this kind of thing >easier? I don't know if an equivalent is in the SQL standard, but MySQL added such a feature in their newish 4.1 release, called GROUP_CONCAT (see syntax at this url): http://www.mysql.com/doc/en/GROUP-BY-Functions.html Regardless of the details of how you do it, yours should definately be implemented as a function used with GROUP BY, just like the standard COUNT or AVG or SUM et al are. -- Darren Duncan - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] How do I coalesce values in a join?
On Mon, Dec 22, 2003 at 03:27:27PM -0700, Dennis Cote wrote: > > > I'm all ears for better ideas :-) > > > > Shouldn't this query be checking the type of the phone records? I think you > need something like the following query to get the correct type of phone > number in each column. Yes, that's what I'm doing. I just omitted the type check to keep the mail shorter. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] How do I coalesce values in a join?
> > selectpeople.id, > > people.first, > > people.last, > > home.phone as home, > > cell.phone as cell, > > work.phone as work, > > fax .phone as fax > > from people > > left join phones as home (people.id = home.id) > > left join phones as work (people.id = work.id) > > left join phones as cell (people.id = cell.id) > > left join phones as fax (people.id = fax .id); > > This does exactly what I want, with the changes: > > ...from people > left join phones as home ON people.id=home.id ... > > etc. > > I'm all ears for better ideas :-) > Shouldn't this query be checking the type of the phone records? I think you need something like the following query to get the correct type of phone number in each column. select people.id, people.first, people.last, home.phone as home, cell.phone as cell, work.phone as work, fax .phone as fax from people left join phones as home on people.id = home.id and home.type = 'home' left join phones as work on people.id = work.id and work.type = 'work' left join phones as cell on people.id = cell.id and cell.type = 'cell' left join phones as fax on people.id = fax .id and fax.type = 'fax'; It seems to me there should be a better way though. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] How do I coalesce values in a join?
This is how I have always done it and currently do it using SQLite. Not sure
if this is standard, but some DBMS' I have encountered support an
'immediate-if' type function (on Oracle it's called DECODE, on Access and
Foxpro, it's slightly different and is called IIF). This function can be
used to do this transpose type operation by using an aggregate query, ie,
let's say you have a table like the original poster's with names and phone
numbers. We'll simplify it to a single table with name, phone type and phone
number. Let's say phone types are A, B and C, and for each phone number a
person has, they have one entry in this table:
Andrew A 000-
Andrew B 111-
Andrew C 222-
So to get the desired result, on Oracle you would do:
SELECT name, max(decode(type,'A',phone)) as phoneA,
max(decode(type,'B',phone)) as phoneB,
max(decode(type,'C',phone)) as phoneC
FROM phones group by name
I implement a function called equalcheck for SQLite using the
sqlite_create_function. This function takes 3 parameters, it returns the
value of the third parameter if the first two are equal, otherwise it
returns null:
void equalCheck(sqlite_func* fn,int nump,const char**parameters)
{
if(!strcmp(parameters[0], parameters[1])) {
sqlite_set_result_string(fn,parameters[2],-1);
} else {
sqlite_set_result_string(fn,0,0);
}
}
Then I can execute the following statement to get the results, which in the
original poster's database would be one row for each person, and phones
broken out into columns:
select name,
max(equalcheck(type,'A',phone)) as aphone,
max(equalcheck(type,'B',phone)) as bphone,
max(equalcheck(type,'C',phone)) as cphone
from phones group by name
So my suggestion would be to add the equivalent of the Oracle DECODE
function to the list of supported functions, since it would facilitate this
type of transpose operation.
-Original Message-
From: D. Richard Hipp [mailto:[EMAIL PROTECTED]
Sent: Monday, December 22, 2003 4:27 PM
To: [EMAIL PROTECTED]
Subject: Re: [sqlite] How do I coalesce values in a join?
Scott Lamb wrote:
> Ron Aaron wrote:
>
>> I have two tables:
>> people (id,first,last)
>>
>> and phones (id, type, phone)
>
>
> ...
>
>> What I would like to do, is 'coalesce' the phones to produce another
>> table:
>>
>> peopleandphones (id,first,last,home,cel,work,fax)
>>
>> So I could make a much nicer report. But I cannot figure out how to
>> do this!
>
>
> Raw SQL doesn't really do that well.
Can anybody suggest some kind of extended SQL syntax that
could be added to SQLite that would make this kind of thing easier?
--
D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] How do I coalesce values in a join?
On Mon, Dec 22, 2003 at 02:52:14PM -0600, Scott Lamb wrote: > Otherwise, you can do it in this limited situation with a kludgy query, > something like: > > selectpeople.id, > people.first, > people.last, > home.phone as home, > cell.phone as cell, > work.phone as work, > fax .phone as fax > from people > left join phones as home (people.id = home.id) > left join phones as work (people.id = work.id) > left join phones as cell (people.id = cell.id) > left join phones as fax (people.id = fax .id); Thanks, Scott - This does exactly what I want, with the changes: ...from people left join phones as home ON people.id=home.id ... etc. I'm all ears for better ideas :-) - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [sqlite] How do I coalesce values in a join?
How about something like: create table peopleandphones insert into peopleandphones from people.id, people.name... where people.id = phone.id; Or, more generally: insert into from where The create for "new-table" could be implicit even. David LeBlanc Seattle, WA USA > -Original Message- > From: D. Richard Hipp [mailto:[EMAIL PROTECTED] > Sent: Monday, December 22, 2003 13:27 > To: [EMAIL PROTECTED] > Subject: Re: [sqlite] How do I coalesce values in a join? > > > Scott Lamb wrote: > > Ron Aaron wrote: > > > >> I have two tables: > >> people (id,first,last) > >> > >> and phones (id, type, phone) > > > > > > ... > > > >> What I would like to do, is 'coalesce' the phones to produce another > >> table: > >> > >> peopleandphones (id,first,last,home,cel,work,fax) > >> > >> So I could make a much nicer report. But I cannot figure out how to do > >> this! > > > > > > Raw SQL doesn't really do that well. > > Can anybody suggest some kind of extended SQL syntax that > could be added to SQLite that would make this kind of thing > easier? > > -- > D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 > > > - > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] How do I coalesce values in a join?
Scott Lamb wrote: Ron Aaron wrote: I have two tables: people (id,first,last) and phones (id, type, phone) ... What I would like to do, is 'coalesce' the phones to produce another table: peopleandphones (id,first,last,home,cel,work,fax) So I could make a much nicer report. But I cannot figure out how to do this! Raw SQL doesn't really do that well. Can anybody suggest some kind of extended SQL syntax that could be added to SQLite that would make this kind of thing easier? -- D. Richard Hipp -- [EMAIL PROTECTED] -- 704.948.4565 - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: [sqlite] How do I coalesce values in a join?
Ron Aaron wrote: I have two tables: people (id,first,last) and phones (id, type, phone) ... What I would like to do, is 'coalesce' the phones to produce another table: peopleandphones (id,first,last,home,cel,work,fax) So I could make a much nicer report. But I cannot figure out how to do this! Raw SQL doesn't really do that well. The ideal way is to have a program on the client designed for this sort of thing. It's usually called a matrix report or cross-tabulation. Commercial packages that do this include Oracle Reports, Crystal Reports, and Microsoft Access. But for something free that you can use with SQLite, Jasper Reports might do it (not sure). Otherwise, you can do it in this limited situation with a kludgy query, something like: selectpeople.id, people.first, people.last, home.phone as home, cell.phone as cell, work.phone as work, fax .phone as fax from people left join phones as home (people.id = home.id) left join phones as work (people.id = work.id) left join phones as cell (people.id = cell.id) left join phones as fax (people.id = fax .id); ...but you'll have to add to the query every time you add a phone type, which is why I call it a kludge. Good luck. Scott Lamb - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] How do I coalesce values in a join?
Hi all - This may be a boneheaded question, but I cannot figure out how to accomplish what I want. I have two tables: people (id,first,last) and phones (id, type, phone) Where each person in the 'people' table might have up to four phones. I can easily get a report of phones by joining the people and phones: select p.first,p.last,ph.phone from people as p, phones as ph where p.id = ph.id; The problem is that this produced one line per person/phone combination. What I would like to do, is 'coalesce' the phones to produce another table: peopleandphones (id,first,last,home,cel,work,fax) So I could make a much nicer report. But I cannot figure out how to do this! Help, anyone? - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
