JOIN TABLES with multiple columns using Query in Mysql

Question

I need to join tables but couldn't really understand what is on the websites. Could you help me?

I have the tables

Products

ID / product_name / product_description / product_code /

Users

ID / username / username_id / password /

Checkedout

ID /product_name /product_des /username /username_id /checkedout_date / return_date /

How can I join from Products (product_name / product_description ) and from Users (username / username_id) to Checkedout?

Thanks very much.

Hello and welcome to StackOverflow. Please take some time to read the help page, especially the sections named "What topics can I ask about here?" and "What types of questions should I avoid asking?". And more importantly, please read the Stack Overflow question checklist. You might also want to learn about Minimal, Complete, and Verifiable Examples. — Ahmet Emre Kilinc
– Ahmet Emre Kilinc, Commented Nov 23, 2017 at 18:28

RToyo · Accepted Answer · 2017-11-23 18:39:58Z

0

Joining tables is fairly simple. I'll leave it to you to continue your learning, but here is how you join those tables, so that you have a reference to better understand what you're learning.

The idea is that you just have to list your tables (in this case, I've added arbitrary aliases to each table - c, p, and u), and specify whether you want an inner or outer join, and then specify which columns to join on. You join on columns that have the same value in both tables. For example, Checkedout has a username_id and the Users table has an ID. You can use all the same logic you would use in a WHERE clause when specifying what to join on (including AND and OR)

select
    *
from
    Checkedout c
    inner join Products p
        on (c.product_name = p.product_name and c.product_des = p.product_description)
    inner join Users u
        on (c.username_id = u.ID)

edited Nov 23, 2017 at 18:39

answered Nov 23, 2017 at 18:33

RToyo

2,8771 gold badge18 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Aman · Accepted Answer · 2017-11-23 19:17:43Z

0

First you need to update your database schema so that the tables are normalized. Suggested schema for Checkedout:

Checkedout - id, product_id, user_id, checkedout_date, return_date

Now you can write a join query very easily(something like this):

select u.username, p.product_name, co.checkedout_date from Checkedout as co join Users u on u.id = co.user_id join Products p on p.id = co.product_id where u.id = 1;

answered Nov 23, 2017 at 19:17

Aman

3704 silver badges14 bronze badges

Collectives™ on Stack Overflow

JOIN TABLES with multiple columns using Query in Mysql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related