The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

SELECTing from 3 tables at once?

SELECT books.isbn as ISBN, books.title as Title, books.authors as Authors, books.edition as Edition
FROM books WHERE 1=1
AND books.isbn = $search_isbn
AND books.title = $search_title
AND books.authors = $search_authors
AND books.edition = $search_edition

If you leave any field empty it is ignored so you can get a list of books by the same author for example.

Simple enough. But I need to get some extra info to append to the table that is generated. I have 2 another querys that work fine alone:

SELECT COUNT(factual.id) as NoFactual, books.isbn as ISBN, books.title as Title, books.authors as Authors, books.edition
FROM books, factual
WHERE books.isbn=$isbn
AND books.isbn=factual.isbn
GROUP BY books.isbn

and

SELECT books.isbn, title, authors
FROM books, for_sale
WHERE books.isbn=for_sale.isbn
AND books.category1 = '$for_sale_category'
ORDER BY title DESC

What I really need is to combine them all so that I get a table with the headings: isbn, title, authors, edition, NoFactual(number of factual entries), and for_sale(or even number for sale).

Holy Crap! This works a treat. Perfect! Thanks so much - this is a great development. It cuts out 2 whole avenues of query (as you can see).

Do you need to know more about the for_sale table? it is for_sale (id, isbn, seller, location, email, phone) but I may split it into for_sale (seller_id, isbn) and seller (seller_id, seller, location, email, phone).
No difference though as each instance of for_sale will equate to the same book.

I must learn more SQL, I must learn more SQL, I must learn more SQL. . .

The numbers have gone wrong again. I get told all my FactualEntryCounts and GrammarYesNos are 1, even when books have 2 or 3. . .
And if I remove all the for_sale parts I still get this error (I thought the DISTINCT keywords would sort it out but they don't seem to be working anymore).
How do I fix it?

The tables generated are slightly different. There are repeats in them and the counted numbers are just the isbns.

Why do you say they are unrelated tables? The primary key for the books table is the isbn, it is the foreign key for all the others.

What I need is the book details, the number of corresponding entries in factual and grammar, whether there are any entries (irrespective of numbers) in for_sale and the average rating from a table called ratings.

I already have this to generate the average rating of a book:

SELECT ratings.isbn AS ISBN, Avg(ratings.rating) AS Average_Rating,
books.title AS Title, books.authors AS Authors, books.edition AS Edition
FROM books, ratings
WHERE books.isbn = ratings.isbn
GROUP BY ratings.isbn, books.title, books.authors
ORDER BY Average_Rating DESC