Simplifying Queries

[Surya Savarika]

Hi,
I have two query series that I wonder whether they can be compacted
into a single query:

FIRST QUERY SERIES

  cursor.execute(select d.ID, d.Name, b.SupersetID from
books_data as d join books as b on d.ID=b.BooksDataID2
  where b.BooksDataID!=b.BooksDataID2 and b.ReligionsID=%s, 
(rel_id,))
  tmp = cursor.fetchall()
  cursor.execute(select d.ID from books_data as d join books as
b on d.ID=b.BooksDataID2 join books_compilations as c
  on d.ID=c.BooksDataID where b.ReligionsID=%s, (rel_id,))
  junk_ids = [itm[0] for itm in cursor]
  poss_books_data = []
  for id, name, ss_id in tmp:
if id not in junk_ids:
  poss_books_data.append([id, name, ss_id])

SECOND QUERY SERIES

  cursor.execute(select ReligionsID from books where
BooksDataID=%s, (tmp_ids[0],))
  rel_id = cursor.fetchone()[0] # The first entry will always give
the correct value
  cursor.execute(select d.ID, d.Name, b.SupersetID from
books_data as d join books as b on d.ID=b.BooksDataID2
  where b.BooksDataID!=b.BooksDataID2 and b.ReligionsID=%s, 
(rel_id,))
  tmp = cursor.fetchall()
  cursor.execute(select d.ID from books_data as d join books as
b on d.ID=b.BooksDataID2 join books_compilations as c
  on d.ID=c.BooksDataID where b.ReligionsID=%s, (rel_id,))

I don't know that they're necessary, but here are the table definitions:

mysql describe books;
+-++--+-+-++
| Field   | Type   | Null | Key | Default | Extra  |
+-++--+-+-++
| ID  | int(11)| NO   | PRI | NULL| auto_increment |
| ReligionsID | int(11)| NO   | MUL | NULL||
| PrimaryReligion | tinyint(1) | YES  | | 0   ||
| BooksDataID | int(11)| NO   | | NULL||
| BooksDataID2| int(11)| YES  | | NULL||
| SupersetID  | int(11)| YES  | | NULL||
+-++--+-+-++
6 rows in set (0.09 sec)

mysql describe books_data;
++--
---+--+-+-+-
---+
| Field  | Type
   | Null | Key | Default |
Extra  |
++--
---+--+-+-+-
---+
| ID | int(11)
   | NO   | PRI | NULL|
auto_increment |
| Name   | varchar(30)
   | NO   | | NULL|
   |
| Label  | enum('Cannonised','Uncannonised, fully accepted','Uncannonised, \
n not fully accepted','Uncannonised, controversial') | NO   | | NULL
||
| PrimaryKey | tinyint(1)
   | YES  | | 0   |
   |
++--
---+--+-+-+-
---+
4 rows in set (0.13 sec)

mysql describe books_compilations;
+-+-+--+-+-++
| Field   | Type| Null | Key | Default | Extra  |
+-+-+--+-+-++
| ID  | int(11) | NO   | PRI | NULL| auto_increment |
| Name| varchar(30) | NO   | | NULL||
| SupersetID  | int(11) | NO   | | NULL||
| BooksDataID | int(11) | NO   | | NULL||
+-+-+--+-+-++
4 rows in set (0.20 sec)


TIA,
Savi

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Cannot Update, at least not how I'm telling it to!

[Surya Savarika]

Hi.

mysql describe unions_data;
+-+-+--+-+-++
| Field   | Type| Null | Key | Default | Extra  |
+-+-+--+-+-++
| ID  | int(11) | NO   | PRI | NULL| auto_increment |
| Title   | varchar(70) | NO   | | NULL||
| Description | text| YES  | | NULL||
| UsersID | int(11) | NO   | MUL | NULL||
+-+-+--+-+-++
4 rows in set (0.02 sec)

mysql select * from unions_data;
++---+-+-+
| ID | Title | Description | UsersID |
++---+-+-+
|  4 | Union One | descr one   |   2 |
|  5 | Union Two | descr two   |   2 |
++---+-+-+
2 rows in set (0.00 sec)

mysql update unions_data set Title='Dos' and Description='dos' where ID=5;
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'Dos'
mysql update unions_data set Title=2.2 and Description='dos' where ID=5;
Query OK, 1 row affected (0.33 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql select * from unions_data;
++---+-+-+
| ID | Title | Description | UsersID |
++---+-+-+
|  4 | Union One | descr one   |   2 |
|  5 | 0 | descr two   |   2 |
++---+-+-+
2 rows in set (0.00 sec)

Notice that it threw an error about an incorrect DOUBLE value for a
field that is a varchar. Notice that when it did decide to update, it
updated incorrectly and it updated Title but _not_ Description, yet
issued no Warnings. Am I missing something??
TIA,
Savi

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql