On Tue, Mar 31, 2009 at 5:15 PM, PJ <af.gourmet@xxxxxxxxxxxx> wrote: > 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? Please refrain from top-posting. It makes your threads MUCH harder to follow. If you're now wanting to filter conditionally and on multiple fields, I would suggest doing that after the fact in PHP. Grab the data you need with your SQL statement and then use PHP to manipulate the results to your liking based on user preference/input. Don't forget--you're working with another language besides SQL... you don't have to do EVERYTHING on the database. ;) -- // Todd -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
