haliphax wrote: > 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. Sorry, once I realize I've got another problem my little peanut figures it's related to the current problem. But, OK, I understand and will do my best. > 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. ;) Could you clarify, please? Do you mean do the query and then manipulate the results for display on a web page, for instance? I have quite a few queries to display all the books and I suspect that what you are suggesting is that I do one query joining all the stuff I need and then manhandle the 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
