1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Mysql newbie question

Discussion in 'Databases' started by Rosemaeyg, Mar 12, 2016.

  1. #1
    I have 3 tables
    TABLE #1 "users"
    3 columns
    User-ID int (11)
    Location varchar (250)
    Age int (11)
    Primary-key (User-ID)

    TABLE #2 "books"
    5 columns
    ISBN varchar (13)
    Book-title varchar(255)
    Book-author varchar (255)
    Year-of-publication int(10)
    Publisher varchar (255)

    TABLE #3 "Book-raitings"
    3 columns
    User-ID int (11)
    ISBN varchar (13)
    Book-rating int (11)
    SEMrush

    Primary-key (user-id, ISBN)

    The Location column varchar will be of format "city, state, country)

    I have to generate top 10 books per each country into a separate table. Generate SQL dump.

    Thank you for your help in advance.
     
    Rosemaeyg, Mar 12, 2016 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,630
    Likes Received:
    725
    Best Answers:
    152
    Trophy Points:
    470
    #2
    You should rethink your structure. Seriously.
    First, why do you have three different types of info in the location column? Why isn't the location column it's own table, or multiple tables, where you can input all countries, all cities and all states? If this is supposed to be for multiple countries, the logic should also have a way to catch multiple ways of writing a town-name (unless you have selects for all of these) - because people will write things differently, or wrong.

    Also... int(11) for age? You expect to have users that are over 100 million years old? int(3) should be more than enough for age. Or you can store their birthdays instead, which will allow you to have an updated age each passing year - a hard-copy age is kinda irrelevant after 1 year...

    TABLE #1 "users"
    6 columns
    User-ID int (11)
    Location_city varchar(250)
    Location_state varchar(250)
    Location_country varchar(250)
    Age smallint (3)
    Primary-key (User-ID)

    TABLE #2 "books"
    6 columns
    ID (int 11)
    ISBN varchar (13)
    Book-title varchar(255)
    Book-author varchar (255)
    Year-of-publication int(10)
    Publisher varchar (255)
    Primary key (ID)

    TABLE #3 "Book-ratings"
    3 columns
    User-ID int (11)
    ID int(11)
    Book-rating int (11) //this should probably also be changed - depending on what type of rating-system you have, this could probably be way less than int(11)

    To get the top 10 books of any given country, you would just do something like:
    SELECT *.t1,*.t2,*.t3 FROM Book-ratings t1 LEFT JOIN books t2 ON t1.ID = t2.ID LEFT JOIN users t3 ON t1.User-ID = t3.User-ID WHERE Location_country = 'selectec country' ORDER BY Book-rating DESC LIMIT 10
     
    PoPSiCLe, Mar 13, 2016 IP
  3. Rosemaeyg

    Rosemaeyg Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    1
    #3



    Thank you very much, ill try this and keep practicing. I'm new to Mysql but trying to learn fast. Once again thank you very much. small last question how do i create a sql dump file with the result of that query?
     
    Rosemaeyg, Mar 13, 2016 IP