Question 174779 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to match columns from one table to ids from another table

I have two tables.

‘users’ table:

id     name     book1    book2    book3    
 1     Brian    name1    name2    name3
 2     John     name3    name1    name2

‘library’ table:

id     book_names    position
 1     name1         2
 2     name2         3
 3     name3         1

What I want is:
The book1 in the ‘users’ table, to match with the id 1 in the ‘library’ table.
The book2 in the ‘users’ table, to match with the id 2 in the ‘library’ table.
The book3 in the ‘users’ table, to match with the id 3 in the ‘library’ table.

And all these in order by position from the ‘library’ table.

I have tried this:

    SELECT 
    l*, u.book1, u.book2, u.book3,
    FROM library AS l 
    LEFT JOIN users AS u 
    ON u.id = l.user_id 
    WHERE u.id = ?
    ORDER BY p.position DESC
Tags:

2

Answers


  1. 0

    I highly recommend to change your DB structure, as everyone said in comments. Something like this:

    CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE books(
    id INT PRIMARY KEY,
    name VARCHAR(100),
    position INT
);

CREATE TABLE user_book (
    user_id INT,
    book_id INT,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (book_id) REFERENCES books(id),
    PRIMARY KEY (user_id, book_id)
);

    Then you can easy manipulate with data you have:

    SELECT *
FROM books 
LEFT JOIN user_book ON books.id = user_book.book_id
LEFT JOIN users ON users.id = user_book.user_id 
WHERE users.id = ?
ORDER BY books.position DESC
    Login or Signup to reply.
  2. 0

    First of all you have to learn about How to normalize the tables.

    Next, you have to change the structure of your users table which should look like below.

    users table:

    DROP TABLE users; /* CARE : take backup if you have any data */
CREATE TABLE users 
(
   id INT NOT NULL AUTO_INCREMENT,
   book1 int,
   book2 int,
   book3 int,
   /* and so on . . . */
   PRIMARY KEY ( id )
);

    After creating, when you insert some data, it should look like below.

    id     name     book1    book2    book3    
 1     Brian    1        2        3
 2     John     3        1        2

    Here we are using id column from library table instead of name of the books. So that you could write SQL query easily.

    Note: I am not speaking about the foriegn key concept, its a bit advance, as a beginner, you will find difficult to manupulate the data manually via phpmyadmin. You could relate both tables via setting a foriegn key once you learn more about this.

    Coming back to the query, now you can write a query like below

        SELECT 
    u.name, l.name as book1, ll.name as book2, lll.name as book3
    FROM users AS u 
    LEFT JOIN library AS l ON l.id = u.book1
    LEFT JOIN library AS ll ON ll.id = u.book2
    LEFT JOIN library AS lll ON lll.id = u.book3
    WHERE u.id = ?
    /*ORDER BY p.position DESC*/

    You can skip the order by clause since all the books have been brought in a single row. If you have anymore query regarding this, pls comment below.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search