List:General Discussion« Previous MessageNext Message »
From:shawn l.green Date:July 20 2014 8:51pm
Subject:Re: Simplifying Queries
View as plain text  
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
Thread
Simplifying QueriesSurya Savarika12 Jul 2014
  • Re: Simplifying Queriesshawn l.green20 Jul 2014