kyle.smith wrote:
> What about using "IN", something like:
>
> SELECT * FROM book WHERE id IN (SELECT bookID FROM book_authors WHERE
> authID IN (SELECT author.id FROM authors WHERE last_name LIKE
> "$Auth%"));
>
> You could use LEFT instead of LIKE, too.
Well, I learned something here... but that also revealed another,
hidden, problem which I had not considered - the order by clause which I
had stupidly not included in my example:
$SQL = "SELECT * FROM book
WHERE id IN (SELECT bookID
FROM book_author WHERE authID IN (SELECT author.id
FROM author WHERE LEFT(last_name, 1 ) = '$Auth'))
ORDER BY $sort $dir
LIMIT $offset, $records_per_page ";
I now realize that to keep things as simple as possible in the rest of
the code, I must join 1 column to the query result and that is last_name
from the author table.
the spelling, column and table names are spelled correctly. Without the
ORDER BY I get the right results. I'll try to JOIN the author table ???
But I see that I may be trying to do too much - I thought of showing how
many books were listed under each letter of the alphabet but I don't see
how it can be done in any simiple way as it would mean that I would have
to do the select once with the ORDER BY and a second time without it
just to get the number of listing. If there are a lot of books, like
thousands, it might slow down things.
I suppose I could live with ORDER BY "title" as that does not require
another effort.
Any thoughts or suggestions?
>
>
>
>
>
> -----Original Message-----
> From: PJ [mailto:af.gourmet@xxxxxxxxxxxx]
> Sent: Tuesday, March 31, 2009 2:06 PM
> To: Jim Lucas
> Cc: Chris; php-general@xxxxxxxxxxxxx
> Subject: Re: formulate nested select
>
> Gentlemen & all others,
> The problem was partly fixed with ' ' around $Auth... but...
> somehow, I see that the results do not work with the rest of the script.
> The results give a an array within an array - so this mucks up all the
> rest which is set up to deal with only the book table. The count() is
> off as it shows the results_per_page setting and the pagination is off -
> it shows 10 books but only displays 5 entries (from 5 arrays which, I
> suppose is the reason for the coun() showing 10. The first page shows 5,
> but the second indicates 7 books but displays only 6...
> Now, I suppose that there are 2 ways to fix things:
> 1. Redo the rest of the script (a royal pain, I suspect) or 2. SELECT
> only the books that are attributed to the targeted authors - which is
> what I wanted to do in the first place. Something like:
>
> $SQL = "SELECT * FROM book b
> WHERE b.id = (SELECT book_author.bookID WHERE book_author.authID
> = (SELECT author.id WHERE LEFT(author.last_name, 1 ) = '$Auth')";
>
> I want to avoid joins as that seems to screw up the rest of the code
> which is in an include page that needs to be repeated as long as there
> are letters in the alphabet.
> I'll try to figure something out, but as somebody not too optimistic
> once said: "it sure don't look too good" (American, I believe...) :-)
>> Jim Lucas wrote:
>>> Chris wrote:
>>>> PJ wrote:
>>>>> I cannot find anything on google or the manuals/tutorials that
>>>>> gives some kin of clear explanation of how to so nested selects
>>>>> with where or whatever.
>>>>> I have three tables: books, authors and book-authors.
>>>>> I need to retrieve only those books whose author's names begin with
> A.
>>>>> I have tried several maniipulations of where and select with select
>
>>>>> subqueries and I cannot get results from the queries.
>>>>> For example
>>>>> "SELECT * FROM book b, book_authors c (SELECT id FROM author WHERE
>>>>> LEFT(author.last_name = $Auth )) as a WHERE a.id = c.authID && b.id
>
>>>>> = c.bookID ....<snip>
>>>> Not really a php question :P
>>>>
>>>> You don't need a subquery for this. You can join all of the tables
>>>> together and just use the where clause to cut down your results, but
>
>>>> I'll give an example of both.
>>>>
>>>> select *
>>>> from
>>>> books b inner join book_authors c on (b.id=c.bookId) inner join
>>>> authors a on (a.id=c.authorId) where left(a.last_name = 'A');
>>> correct me if I'm wrong, but did you use the left() function
>>> in-correctly?
>>>
>>> The documentation shows a different way to use it then you describe.
>>>
>>> Something more like the following:
>>>
>>> WHERE
>>> LEFT(a.last_name, 1) = 'A';
>>>
>>> But that would be case-sensitive...
>>>
>>> So, something like this would work better IMHO
>>>
>>> WHERE
>>> UPPER(LEFT(a.last_name, 1)) = 'A';
>>>
>>> or
>>>
>>> WHERE
>>> a.last_name ILIKE 'A%';
>>>
>>> would do the trick
>>>> or
>>>>
>>>> select *
>>>> from
>>>> books b inner join book_authors c on (b.id=c.bookId) where
>>>> c.authorId in ( select id from authors where left(last_name='A')
>>> Again...
>>>
>>> SELECT id FROM authors WHERE LEFT(last_name, 1) = 'A')
>>>
>>> but yet again, case-sensitive...
>>>
>>> SELECT id FROM authors WHERE UPPER(LEFT(last_name, 1)) = 'A') or
>>> SELECT id FROM authors WHERE last_name ILIKE 'A%'
>>>
>>> would do the trick
>>>
>>>> );
>>>>
>>>
>> Thank you for the suggestions, gentlemen.
>> As to the case sensitivity, since the authors' names must be written
>> with the first letter in uppercase, even "Anonymous" or "Unknown" I
>> assume I don't need to specify uppercase. Or does it really make a
>> difference? Glad to learn of the option, though.
>>
>> I'm just starting on the listing of the books by author and just
>> realized that the sorting should be by author (last name). Can I
>> foresee a problem in that since the last_name is in associative tables
>
>> and not in the book table? Or does the JOIN incorporate the last_name
>> in the results?
>
>
> --
> unheralded genius: "A clean desk is the sign of a dull mind. "
> -------------------------------------------------------------
> Phil Jourdan --- pj@xxxxxxxxxxxxx
> http://www.ptahhotep.com
> http://www.chiccantine.com/andypantry.php
>
> --
> PHP General Mailing List (http://www.php.net/) To unsubscribe, visit:
> http://www.php.net/unsub.php
>
>
--
unheralded genius: "A clean desk is the sign of a dull mind. "
-------------------------------------------------------------
Phil Jourdan --- pj@xxxxxxxxxxxxx
http://www.ptahhotep.com
http://www.chiccantine.com/andypantry.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
