[sqlite] UNION QUERY

[TW]

Hi,

I have this query:

SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=6 OR classnumber=7
AND hd="B"
UNION
SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=8 OR classnumber=9
AND hd="A"
ORDER BY random() LIMIT 2;

And I get this error:

SQL near line 1: 1st ORDER BY term does not match any column in the 
result set

The data that I need is all in the same table, but I need to run 
queries based 
on the classnumber.  I tried a number of different ways but couldn't figure 
this out, even 
after googling.  I figured that this was similar to the query I used in MySQL, 
except I 
learned that the ORDER BY clause had to be put at the end of the SELECT 
statements.

I actually need to have a limit on each SELECT clause so that I only 
get one 
record from either classnumbers 6 or 7 and then one record from classnumbers 8 
or 9, randomly 
chosen.  I thought that I needed to write it like this:

SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=6 OR classnumber=7
AND hd="B"
ORDER BY random() LIMIT 1
UNION
SELECT title, artist, hd, bank, bookmark, genre, class, classnumber
FROM music
WHERE classnumber=8 OR classnumber=9
AND hd="A"
ORDER BY random() LIMIT 1;

...but that doesn't work either.  Notice that I put only 1 semicolon at 
the very end 
of the compound statement.  If I order it by one of the columns, the query 
works, but only for 
one SELECT statement (I can't get the UNION to work at all).

What is wrong with the way I wrote this query?  Thanks.

-- 
VR~
TW
Email: [EMAIL PROTECTED]
"Knowledge Is Power"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Query Issues: Duplicates In UNION Query

[TW]

Hi,

I have been doing a Union query and would like to obtain distinct 
results.
Via the manual, the correct way to accomplish this is to do a UNION statement 
(versus 
doing a UNION ALL).

Problem: I want the order of the select statements left intact after 
the UNION 
select statements.

Example: I have some records like this ->

FName   LName   SideHeight
--
Momma   Joe A   1
Gnu Org A   3
Daddy   KaneB   2
Joe Diddy   B   4

So, when I combine a compount select statement with a UNION, I want the 
height to increase 
in ascending order, but, I ALSO want the side column to alternate between A and 
B.  If 
I do the UNION ALL I'll get something like ->

FName   LName   SideHeight
--
Momma   Joe A   1
Joe Diddy   B   4
Gnu Org A   3
Joe Diddy   B   4
Gnu Org A   3
Daddy   KaneB   2

See the duplicates (I'm disregarding the ascending height for 
this part)?  
If I do the UNION I get this ->

FName   LName   SideHeight
--
Gnu Org A   3
Joe Diddy   B   4
Momma   Joe A   1
Daddy   KaneB   2

See how the heights are all off (disregarding the alternating 
sides in this 
part)?  How should I write this thing so that I get the ascending height ALONG 
WITH the alternating
sides according to the order of the select statements that I put in the query?  
Thanks.
    
-- 
VR~
TW
Email: [EMAIL PROTECTED]
"Knowledge Is Power"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Issues: Duplicates In UNION Query

[TW]

> Order is never guaranteed in a SQL statement's resultset, unless this 
> statement specifies an ORDER BY clause. If some statement without such a 
> clause happens to give you an order you want, it does so by accident. If 
> you want a particular order, say so in the statement.

Yeah, which would work for the height example, but then would still 
mess up the alternation of the side column.

> It would help if you showed the actual statement you have now, and the 
> results you expect.

Apologies.

It would look like this:

FName   LName   SideHeight
--
IgorTande   A   1
Telly   WillB   1
JohnSmith   A   1
Bob HopeB   1
Sandy   Rivera  A   2
Bobby   Sangria B   2
JaneTaneA   2
Tom Jones   B   2

SELECT Fname, Lname, Side, Height FROM (SELECT * FROM table WHERE 
Side='A' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1) UNION
SELECT Fname, Lname, Side, Height FROM (SELECT * FROM table WHERE 
Side='B' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1) UNION
SELECT Fname, Lname, Side, Height FROM (SELECT * FROM table WHERE 
Side='A' AND ((Height=1) OR (Height=2)) ORDER BY random() LIMIT 1);

With the UNION statement above I might get:

FName   LName   SideHeight
--
JaneTaneA   2
IgorTande   A   1
Tom Jones   B   2

The Height is out of order (not 1, 2, 2).  If I use a UNION ALL 
then I might get:

FName   LName   SideHeight
--
JohnSmith   A   1
Telly   WillB   1
JohnSmith   A   1

See the duplicate?  I'm doing this in a C++ program, so I'm thinking 
that 
I might have to run individual SELECT statements for each one in the order that 
I
need. 

-- 
VR~
TW
Email: [EMAIL PROTECTED]
"Knowledge Is Power"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Query Issues: Duplicates In UNION Query

[TW]

> Frankly, I don't understand why you would ever need such a beast, and I 
> probably don't want to know (I suspect it will give me nightmares). In 
> any case, assuming you really have a reason for this monster, try 
> something like this:

Yeah, I need it, and the query that I auto-generate from C does this 
for 80 records.

Thanks for the help Igor.  I wish it were simpler.

-- 
VR~
TW
Email: [EMAIL PROTECTED]
"Knowledge Is Power"
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite3_open Wrapper Issue

[TW]

I made a safe_sqlite3_open function like this:

/* From sqlite.c --> query_and_populate function */
int safe_sqlite3_open(char *tablename, sqlite3 *db)
{
   int retval;

   retval = sqlite3_open(tablename, db);

   if(retval != SQLITE_OK)
  HandleError(retval, "sqlite3_open", "Can't open database: 
%s", sqlite3_errmsg(db));

   return retval;
 }

In normal usage, I call the function like this:

/* From safecalls.c --> safe_sqlite3_open */
sqlite3 *db;

rc = safe_sqlite3_open(tablename, &db);

When I run make, though, I get these errors:

gcc -g -c cursedj.c -o cursedj.o 
gcc -g -c musicinfo.c -o musicinfo.o 
gcc -g -c sqlite.c -o sqlite.o
sqlite.c: In function ‘query_and_populate’:
sqlite.c:14: warning: passing argument 2 of ‘safe_sqlite3_open’ 
from incompatible pointer type
gcc -g -c winmanip.c -o winmanip.o 
gcc -g -c output.c -o output.o 
gcc -g -c dhandler.c -o dhandler.o
gcc -g -c help.c -o help.o 
gcc -g -c file.c -o file.o
gcc -g -c safecalls.c -o safecalls.o
safecalls.c: In function ‘safe_sqlite3_open’:
safecalls.c:152: warning: passing argument 2 of ‘sqlite3_open’ 
from incompatible pointer type
gcc -Wall -g -lm -lncurses -lsqlite3 -o cursedj cursedj.o 
musicinfo.o sqlite.o winmanip.o \
output.o dhandler.o help.o file.o safecalls.o

I know that they're just warnings, but, how can I pass the sqlite3 
pointer to my safe function 
without getting any errors?  I've tried making the function parameters "*&", 
"&", passing the "address of" 
to the function...all to no avail, except what I have now, but I get those 
warnings, BUT, the code does 
work and my program runs the way it is above.

-- 
VR~
TW
Email: twilliams...@elp.rr.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Passing FIELD Object To Function

[TW]

I have a function (populate_edit_form) declared in a header as such:

   /* recmanip.h */
   
   #ifndef RECMANIP_H
   #define RECMANIP_H
   
   #include 
   #include 
   #include 
   #include "winmanip.h"
   #include "dhandler.h"
   #include "sqlite.h"
   
   #define NFIELDS 31 
   #define FORM_WIDTH 30
   #define FORM_STARTY 2
   #define FORM_STARTX 1
   #define CAPTION_WIDTH 15
   
   struct form {
   const char title[15];
   char value[30];
   };
   
   int add_record(void);
   int populate_edit_form(FIELD *field[NFIELDS], FORM *, WINDOW *);
   int print_in_middle(WINDOW *, int, int, int, char *, chtype);
   
   #endif /* RECMANIP_H */

In one of my files, I call this function like this:

   /* recmanip.c */
   
   #include "recmanip.h"
   
   int add_record(void)
   {
   FIELD *field[NFIELDS];
   FORM *myform;
   WINDOW *form_win;
   int ch, i=0, j=0; 
   struct form form_data[] = {
   {"id", "" }, {"title", "" }, {"artist", "" }, {"hd", "" },
   {"bank", "" }, {"bookmark", "" }, {"active", "" }, {"digitalinput", 
"" },
   {"genre", "" }, {"price", "" }, {"year", "" }, {"remixer", "" },
   {"labelid", "" }, {"class", "" }, {"classnumber", "" }
   };
   
   ===> populate_edit_form(field, myform, form_win);
   
   

   }

   int populate_edit_form(FIELD *field[NFIELDS], FORM *myform, WINDOW 
*form_win)
   {
   int i=0, j = 0, rows = 0, cols =0;
   int maxy, maxx;
   int form_win_height, form_win_width;
   int form_win_starty, form_win_startx;
   char *hd_choices[] = { "A", "B", NULL };
   char *active_choices[] = { "Y", "N", NULL };
   char *digital_choices[] = { "Yes", "No", NULL };
   char *field_names[] = {
   "ID", "Title", "Artist", "HD", "Bank", "Bookmark", "Active", 
   "Digital Input", "Genre", "Price", "Year", "Remixer", "Label",
   "Class Type", "Class Number"
   };
   
   /* Initialize the fields */
   /* num rows, num cols, start row, start col, off-screen rows, num 
buffers */
   for(i=0; ihttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Passing FIELD Object To Function

[TW]

> Please forgive me if SQLite has a built-in forms package that I haven't 
> noticed, but the one quoted #include line above doesn't appear to 
> qualify your question as being "on-topic" for this mailing list 
> ("General Discussion of SQLite Database ").
> 
> What forms package are you using? Does it have an associated mailing 
> list or discussion forum?

Oh, sorry guys!  I meant to send this to ncurses.  My 
apologies!!  Thanks, John!

-- 
VR~
TW
Email: twilliams...@elp.rr.com
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users