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
