SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 36
Thread: SELECTing from 3 tables at once?
-
May 19, 2004, 15:24 #1
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
SELECTing from 3 tables at once?
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. . .
-
May 19, 2004, 20:19 #2
- Join Date
- May 2003
- Location
- Auckland
- Posts
- 309
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Code: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
-
May 20, 2004, 02:15 #3
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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. . .
-
May 20, 2004, 03:05 #4
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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. . .
-
May 20, 2004, 04:18 #5
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
May 20, 2004, 04:36 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 20, 2004, 12:36 #7
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
May 20, 2004, 13:25 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 20, 2004, 14:18 #9
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Thanks rudy. You solve all my problems
-
May 23, 2004, 16:38 #10
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
May 23, 2004, 16:44 #11
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 23, 2004, 17:02 #12
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
May 23, 2004, 17:04 #13
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 23, 2004, 17:14 #14
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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. . .
-
May 23, 2004, 18:53 #15
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 24, 2004, 00:03 #16
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
So can it be done?
-
May 24, 2004, 04:46 #17
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
-
May 24, 2004, 07:31 #18
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
May 24, 2004, 08:30 #19
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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
Code: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
-
May 24, 2004, 16:51 #20
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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"?
-
May 24, 2004, 17:42 #21
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
no, not like that
like this --
Code:, ( select avg(rating) from ratings where isbn = books.isbn ) as Average_Rating
-
May 25, 2004, 07:23 #22
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
May 25, 2004, 07:45 #23
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
no, nulls are never left blank
AVG() ignores nulls
-
May 25, 2004, 08:22 #24
- Join Date
- Mar 2004
- Location
- Europe
- Posts
- 214
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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. . .
-
May 25, 2004, 09:24 #25
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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?
Bookmarks