WEB mySQL crew: couple of questions for you.

Discussion in 'OT Technology' started by brds, Dec 11, 2008.

  1. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,646
    Likes Received:
    15
    Location:
    Atlanta
    I'm helping my buddy out with his mySQL final project and i've hit a couple of snags... i'll be posting in here as i run into each problem.

    First problem i've come accross... I need to select all the users and then list if they've checked out a book from the fictitious library.

    I ran this query:

    Code:
    SELECT CONCAT(PATRONS.fName, ' ', PATRONS.lName) AS 'Name', PUBLICATIONS.title, CHECKEDOUT.dueBack
    FROM PATRONS
    INNER JOIN PUBLICATIONS
    ON PATRONS.checkoutID = PUBLICATIONS.publicationID
    INNER JOIN CHECKEDOUT
    ON PATRONS.patronID = CHECKEDOUT.patronID
    ORDER BY PATRONS.fName;
    
    but it only returns those users who are listed in the checkedout table.


    How do i list all entries in the patrons table and not just those in the checkedout table as well?
     
  2. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,646
    Likes Received:
    15
    Location:
    Atlanta
    Question 2:

    How do i list the information in a row for every row that has the same information in one of two specific columns?

    For example, in the library assignment, each publication has an ISBN number, or an ISSN number (depending on if it's a book (isbn) or magazine(issn).) I need to find, and list, all of the information for each publication who's isbn or issn number appears more than once.
     
  3. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    Question 1: Use a LEFT JOIN instead of INNER JOIN
    Question 2: I don't understand the question
     
  4. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,646
    Likes Received:
    15
    Location:
    Atlanta
    1 - Left join didn't work. I ran the following command:
    Code:
    SELECT CONCAT(PATRONS.fName, ' ', PATRONS.lName) AS 'Name', PUBLICATIONS.title, CHECKEDOUT.dueBack
    FROM PATRONS
    LEFT JOIN PUBLICATIONS
    ON PATRONS.checkoutID = PUBLICATIONS.publicationID
    INNER JOIN CHECKEDOUT
    ON PATRONS.patronID = CHECKEDOUT.patronID
    ORDER BY PATRONS.fName;
    
    and got the same results as the inner join



    2 - Let's say there's 5 columns, id, isbn, issn, title, author. I need to select all of the entries where the same isbn or issn appears more than once.
     
  5. biawokauns

    biawokauns New Member

    Joined:
    Sep 18, 2001
    Messages:
    19,893
    Likes Received:
    0
    Location:
    Republic of Kalifornia
    if this is the mysql final, your buddy is fucked...he doesn't understand even the most basic shit?
     
  6. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,646
    Likes Received:
    15
    Location:
    Atlanta
    It's for the final project.. there's no test. I've been able to help him out as best i can, but i haven't done this shit in over a year really.
     
  7. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    You still have an inner join in your query which is why it won't be working!

    So for 2, are you trying to find duplicate entries?
     
  8. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,646
    Likes Received:
    15
    Location:
    Atlanta
    for 2 - yes, where either the isbn or issn is duplicated
     
  9. jdw

    jdw New Member

    Joined:
    Dec 31, 2005
    Messages:
    4,429
    Likes Received:
    0
    Location:
    ND

    is all of the information in the duplicate rows the same?
     
  10. whup

    whup I wish you had children and.. so that I could step

    Joined:
    Feb 12, 2007
    Messages:
    1,603
    Likes Received:
    0
    Allowing duplicates to be added is bad design.

    Find which codes are duplicated with this:

    SELECT Isbn, COUNT(Isbn) AS NumberOfDuplicates FROM `PUBLISHERS` GROUP BY Isbn HAVING ( COUNT(Isbn) > 1 )

    Then do the same for Issn.

    Ditch the duplicates, then once you're done add a unique index on those columns so no more duplicates can be added.
     
  11. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,646
    Likes Received:
    15
    Location:
    Atlanta
    yep
     
  12. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,646
    Likes Received:
    15
    Location:
    Atlanta
    I know... his design is terrible, but it is what it is. He had to get it approved a while ago, so it's too late to change it.
     

Share This Page