Hello Surya,
Part of the problem may be that you are so focused on the details that
might have lost sight of the purpose.
On 7/12/2014 8:24 AM, Surya Savarika wrote:
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,))
Are you trying to find the names of any supersets that contain any book
that has a certain ReligionsID value? (list1)
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,))
Are you trying to find a list of compilations that contain any books
that has a certain ReligionsID value? (list2)
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])
This seems to be a process by which you determine if there are any books
in list 1 (the first query) that are not in list 2 (the second query).
Did I understand that correctly?
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
Determine the ReligionsID for a particular book.
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,))
Find all the related books that share the same ReligionsID value.
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,))
Find any compilations that contain the same ReligionsID value.
Did I decode those questions properly?
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)
If you can verify that I have correctly stated what you are trying to
do, we (the list) can try to help you do those steps more efficiently.
If I have misinterpreted your intentions, please describe what it is you
are looking for and we can work from there.
Yours,
--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
