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]
Re: [sqlite] how can I use sqlite within windows managed code?
Sqlite API is ANSI C, there's no reason why is needed. Surely
you are free to use the dll with any windows program and refer to windows
API at the same time.
Sqlite can be used in managed code (C#) as demonstrated with Mono
(http://www.go-mono.com) sqlite .net data provider. There were a rather
serious one-line bug that was in mono 0.28 release (it was identified and
patches were posted in email list before 0.29 was out, so probably fixed
already).
One leftover issue, is the C callback function for sqlite_exec
int Callback(void *pArg, int argc, char **argv, char **columnNames){
return 0;
}
It is generally cdecl but Microsoft C# P/Invoke supports only STDALL for
callback (not sure how mono handles this). Maybe a paralell
sqlite_exec_stdcallback could be defined for C# support to use P/Invoke
directly.Luke- Original Message -
From: "Tim McDaniel" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, December 19, 2003 11:13 AM
Subject: RE: [sqlite] how can I use sqlite within windows managed code?
The code example you give does not look like managed code, if by "managed
code" you mean .NET code.
Your example looks like plain Windows C++ code.
> -Original Message-
> From: Kayhan Yuksel [mailto:[EMAIL PROTECTED]
> Sent: Friday, December 19, 2003 10:33 AM
> To: '[EMAIL PROTECTED]'
> Subject: [sqlite] how can I use sqlite within windows managed code?
>
>
> To whom it may concern,
> I would like to know how can I use sqlite in a windows
> managed code ?I am trying to modify the code at
> "..\Dev-Cpp\Examples\WinMenut" that comes with the devcpp
> 4.9.8.0 I have copied the sqlite.h to include directory,
> included the sqlite.h as modified some part of the
> code to something like that : case IDM_DOSYAYENI:
> {
> sqlite* p_db = sqlite_open("c:\test.sdb", 0777, 0);
> MessageBox( hwnd, (LPSTR) " 'test'
> Veritabanı hazır.",
> (LPSTR) szClassName,
> MB_ICONINFORMATION | MB_OK );
> return 0;
> }
> and I got this error :
> [Linker error] undefined reference to `sqlite_open'
>
> Is there an example that uses with sqlite?
>
> Yours sincerely+happy new year,
>
> Kayhan YUKSEL
>
>
> -
> 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] Network Probs with SQLITE.exe
Hello Markus, Monday, December 22, 2003, 9:30:38 PM, you wrote: MH> is it correct, that SQLite.exe cannot open the data base files in a network? MH> Client and Server are both WinXP pro. if I enter the following: sqlite MH> y:\datadases\john.db, SQLite say >> Unable to open database MH> "y:\batadases\john.db": unable to open database y:\batadases\john.db << MH> If the database is on Win98 , there are no problems, only the MH> combination WinXP to WinXP does not work. May be NTFS permissions? -- Best regards Sergey Startsev SQLite Analyzer - GUI tool to manage SQLite databases. http://www.kraslabs.com/sqlite/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
[sqlite] Network Probs with SQLITE.exe
Hello, is it correct, that SQLite.exe cannot open the data base files in a network? Client and Server are both WinXP pro. if I enter the following: sqlite y:\datadases\john.db, SQLite say >> Unable to open database "y:\batadases\john.db": unable to open database y:\batadases\john.db << If the database is on Win98 , there are no problems, only the combination WinXP to WinXP does not work. Any ideas ? Best Regards Markus - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
