SELECTing from 3 tables at once?

[Subjective Effec]

This is the full code. The PHP works with my original SQL so I reckon its ok.

$newsql="select 
	books.isbn as ISBN, 
	books.title as Title, 
	books.authors as Authors, 
	books.edition as Edition, 
	(select count(factual.isbn)
    	from factual
        Where isbn = books.isbn ) 
        	as FactualEntryCount, 
	(select count(grammar.isbn)
         from grammar
         where isbn = books.isbn )
         	as GrammarYesNo, 
	for_sale.isbn as ForSaleYesNo
  	from books
	left outerjoin for_sale 
    on books.isbn = for_sale.isbn
 	WHERE 1=1";
	if ($search_isbn){$newsql=$newsql . " AND books.isbn = '" . $search_isbn ."'";}
	if ($search_title){$newsql=$newsql . " AND books.title = '" . $search_title ."'";} 
	if ($search_authors){$newsql=$newsql . " AND books.authors = '" . $search_authors ."'";}
	if ($search_edition){$newsql=$newsql . " AND books.edition = '" . $search_edition ."'";}
	if ($search_category){$newsql=$newsql . " AND (books.category1 = '" . $search_category ."'" 
	. " OR books.category2 = '" . $search_category ."'"
	. " OR books.category3 = '" . $search_category ."')";}
	$newsql=$newsql . "order by books.title, books.edition";

[r937]

ANDs and ORs may need parenthesizing

instead of running this query, echo it out to see what it looks like after all the variables have been substituted

[Subjective Effec]

select books.isbn as ISBN, books.title as Title, books.authors as Authors, books.edition as Edition,
(select count(factual.isbn) from factual Where isbn = books.isbn ) as FactualEntryCount,
(select count(grammar.isbn) from grammar where isbn = books.isbn ) as GrammarYesNo,
for_sale.isbn as ForSaleYesNo from books
left outerjoin for_sale on books.isbn = for_sale.isbn
WHERE 1=1
AND (books.category1 = 'biology'
OR books.category2 = 'biology'
OR books.category3 = 'biology')
order by books.title, books.edition

The error I get is
"Error performing query: You have an error in your SQL syntax near 'select count(factual.isbn) from factual Where factual.isbn = book' at line 6"

[r937]

say, you don't happen to be using mysql, by any chance?

mysql doesn't support subqueries prior to version 4.1

[Subjective Effec]

I am using mySQL, version 3 :P .

What do I do?

[r937]

what do you do? you make sure you mention that early on in any thread asking for sql



you'll have to run separate queries

[Subjective Effec]

Ok. Sorry, I did not know that mySQL had that issue. You cannot set foreign keys either. Odd that it is so popular - free I guess.

So separate queries for each section such as book details, then the number of factual, number of grammar, number of for_sale and ave rating?

I can see I'm going to have some issues matching results up in a table but I can probably do it. I'd rather limit the number of queries though.

I'll give it a go.

Thanks again for all your help.

[Subjective Effec]

I'm reverting back to using the intial joins with DISTINCT in the COUNTS but it doesn;t work - just gives a value of 1 regardless. Taking the distinct out gives the highest nubmer across the tables.

Can it be fixed? If so, how?

[r937]

you'll have to run three separate queries

[Subjective Effec]

Is this puuely a mySQL problem? If so it is a very poor show. If the only thing stopping me is the lack of support for sub-selects I will not be happy.

My strategy has changed now. Instead of counting everything I want to join the tables so that I can just know IF there are any corresponding entries in the factual, grammar and for_sale tables. Seems to work so far but it really is sub-standard.

It's a real shame because I wanted to have the average rating too. . .

Ack!

[Subjective Effec]

Ok. I'm updating this so that people who come after me (in years to come ) will see how I solved it.

My final query was:

SELECT books.isbn AS ISBN, books.title AS Title, books.authors AS Authors, books.edition AS Edition,
	factual.isbn AS FactualEntryCount,
	grammar.isbn AS GrammarYesNo,
	for_sale.isbn AS ForSaleBookCount,
	Avg(ratings.rating) AS Average_Rating	
	FROM books
	LEFT JOIN factual
	ON books.isbn = factual.isbn
	LEFT JOIN grammar
	ON books.isbn = grammar.isbn
	LEFT JOIN for_sale
	ON books.isbn = for_sale.isbn
	LEFT JOIN ratings
	ON books.isbn = ratings.isbn
	WHERE if ($search_isbn){$newsql_1=$newsql_1 . " AND books.isbn = '" . $search_isbn ."'";}
	if ($search_title){$newsql_1=$newsql_1 . " AND books.title = '" . $search_title ."'";} 
	if ($search_authors){$newsql_1=$newsql_1 . " AND books.authors = '" . $search_authors ."'";}
	if ($search_edition){$newsql_1=$newsql_1 . " AND books.edition = '" . $search_edition ."'";}
	if ($search_category){$newsql_1=$newsql_1 . " AND (books.category1 = '" . $search_category ."'" 
	. " OR books.category2 = '" . $search_category ."'"
	. " OR books.category3 = '" . $search_category ."')";}
	$newsql_1=$newsql_1 . "GROUP BY books.isbn, books.title, books.authors, books.edition";

The output is packaged into a table that gives all the "books" table details, whether there are corresponding factual, grammar or for_sale entries in the respective tables and the average rating for each book (or no rating).

Using if-else users can click through to details of the factual, grammar or for_sale tables as the isbn is wrapped up in the "yes".