SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 36
  1. #1
    SitePoint Addict
    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. . .

  2. #2
    SitePoint Addict
    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
    This is a best guess of what you want (Don't know much about that 'for_sale' table).

  3. #3
    SitePoint Addict
    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. . .

  4. #4
    SitePoint Addict
    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. . .

  5. #5
    SitePoint Addict
    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.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    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?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks rudy. You solve all my problems

  10. #10
    SitePoint Addict
    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?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Addict
    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.

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Addict
    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. . .

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    So can it be done?

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    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?

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Addict
    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"?

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Addict
    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.

  23. #23
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  24. #24
    SitePoint Addict
    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. . .

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •