SELECTing from 3 tables at once?

[Subjective Effec]

So my basic query is

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).

It is too much for my brain. . .

[Imminent]

 SELECT
 	b.isdn
 ,	b.Title
 ,	b.Authors
 ,	b.Edition
 ,	COUNT(f.isdn) AS FactualEntryCount
 ,	COUNT(s.isdn) AS ForSaleBookCount
 FROM
 	Books b
 LEFT JOIN
 	Factual f ON b.isdn = f.isdn
 LEFT JOIN
 	for_sale s ON b.isdn = s.isdn
 GROUP BY
 	b.isdn
 ,	b.Title
 ,	b.Authors
 ,	b.Edition

This is a best guess of what you want (Don't know much about that 'for_sale' table).

[Subjective Effec]

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. . .

[Subjective Effec]

Correction - there is an error I cannot understand.

This SQL reports the FactualEntryCount or the ForSaleBookCount as the highest number of the two.

So if you have 1 FactualEntryCount and 3 ForSaleBookCounts it reports both as 3

If you have 4 FactualEntryCount and 3 ForSaleBookCounts it reports both as 4

etc

Why? It counts each individually it would seem. . .

[Subjective Effec]

I've altered the SQL. This:

COUNT(s.isbn) AS ForSaleBookCount

to this:

s.isbn AS ForSaleYesNo

Now I get the isbn if there is a book for sale. I can wrap it up in a Yes?No? php script and turn it into link if it is yes - the link will go to a page that lists all of that book for sale.

And this method is just as good really. Thanks for the push in the right direction Imminent.

[r937]

when you join two unrelated -- to each other -- tables to a third, and do a GROUP BY on columns from the third, then aggregates on columns of either of the other will suffer the cross join effect

book
aaa
bbb
ccc

factual
aaa-f1
aaa-f2
bbb-f1
bbb-f2
bbb-f3
ccc-f1

forsale
aaa-s1
aaa-s2
bbb-s1
ccc-s1
ccc-s2

joined
aaa aaa-f1 aaa-s1
aaa aaa-f1 aaa-s2
aaa aaa-f2 aaa-s1
aaa aaa-f2 aaa-s2
bbb bbb-f1 bbb-s1
bbb bbb-f2 bbb-s1
bbb bbb-f3 bbb-s1
ccc ccc-f1 ccc-s1
ccc ccc-f1 ccc-s2

joined, grouped with counts
aaa 4 4
bbb 3 3
ccc 2 2

one way around it is to use COUNT(DISTINCT foo) -- it's inefficient, but it works

[Subjective Effec]

So how does one do that with my problem? Do just put:

COUNT(DISTINCT f.isdn ) AS FactualEntryCount
COUNT(DISTINCT s.isdn) AS ForSaleBookCount

?

Why is it inefficient and what difference will it practically make?

[r937]

yes, just insert the keyword DISTINCT into the COUNTs

it is inefficient because it has to count this data --

aaa aaa-f1 aaa-s1
aaa aaa-f1 aaa-s2
aaa aaa-f2 aaa-s1
aaa aaa-f2 aaa-s2
bbb bbb-f1 bbb-s1
bbb bbb-f2 bbb-s1
bbb bbb-f3 bbb-s1
ccc ccc-f1 ccc-s1
ccc ccc-f1 ccc-s2

but it only produces this result --

aaa 2 2
bbb 3 1
ccc 1 2

what difference will it practically make?

why, practically none

[Subjective Effec]

Thanks rudy. You solve all my problems

[Subjective Effec]

Ack! I've been playing. I added a 4th table. The SQL now looks like this:

SELECT books.isbn as ISBN, books.title as Title,
books.authors as Authors, books.edition as Edition,
COUNT(DISTINCT factual.isbn) AS FactualEntryCount,
COUNT(DISTINCT grammar.isbn) AS GrammarYesNo,
for_sale.isbn AS ForSaleYesNo
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
WHERE blah blah blah (this bit is fine)
GROUP BY books.isbn, books.title, books.authors, books.edition

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?

[r937]

temporarily change the GROUP BY to ORDER BY, and remove the COUNTs but leave the isbns

i think it's time for you to see what joining unrelated tables is actually doing

[Subjective Effec]

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

. . . which I believe you helped me with.

[r937]

yes, but you need to see what joining unrelated-to-each-other-but-all-related-to-the-same-common-table tables actually does, before you group on them to try to get counts

[Subjective Effec]

I do see. You get a huge list where each row corresponds to a search on your conditions. And left joins allow for data to be displayed where there are null values.

And THEN you add all this grouping and counting to try and condense the data into a more meaningful form.

Am I correct? I was taught SQL for Oracle once, a few years ago. . .

[r937]

yeah, you get a huge list, but it's not a search on your conditions, it's a cross join effect

like i tried to show in post #8

only instead of unrelated squared rows, you get unrelated cubed when you add that 4th table

[Subjective Effec]

So can it be done?

[r937]

yeah, it can be done, but it's inefficient

if i were doing it, i'd use a different approach, depending on whether you were looking for averages, counts, or whatever

[Subjective Effec]

I am quite confused now. I thought the point of relational databases was so you could do these sorts of queries.

I realise I could do several queries, instead of one big one, to get the same information.

What would you do?

[r937]

please forgive me for being the one to have to enlighten you, but running "these sorts of queries" is not the point of relational databases

the point of relational databases is to run other sorts of queries

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 outer
  join for_sale 
    on books.isbn = for_sale.isbn
 where ...
order
    by books.title
     , books.edition

[Subjective Effec]

What need of forgiveness? I am only graetful for your tutorage. I see, those sorts of queries.

So do I add sub-select thus:
(SELECT Avg(ratings.rating)
FROM books, ratings
WHERE books.isbn = ratings.isbn
GROUP BY ratings.isbn) AS Average_Rating,

into the query after "as GrammarYesNo"?

[r937]

no, not like that

like this --

 
     , ( select avg(rating)
           from ratings
          where isbn = books.isbn )
                  as Average_Rating

you may not have noticed, but these subqueries are correlated, meaning that they operate on only those rows in the subquery which match the isbn of the "outer" query

[Subjective Effec]

Ah, I think I see. And where it says "from ratings where isbn = books.isbn" it means "where ratings.isbn = books.isbn".

And nulls are left blank as in left joins? I cannot test it at the moment but I will in the next couple of days.

I'm never sure of the shortcuts one can make/take, like saying b.isbn form books b etc. <- I just re-learnt that the other day.

[r937]

no, nulls are never left blank

AVG() ignores nulls

[Subjective Effec]

I've copied and pasted that last query. I get an error:
"You have an error in your SQL syntax near 'select count(factual.isbn) from factual where isbn = books.isb' at line 5"

Can't see what is up with it though. . .

[r937]

i can't see it either, but that's because my crystal ball runs on Windows XP and it keeps crashing

mind showing me the query?