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
