Get all records from a table along with a selected record in single MySQL query statement

Question

I'm pretty bad when it comes to designing database queries, can I have some help?

Here are the 2 tables (samples):

TABLE user: id, name, relationship_type_id
TABLE relationship_type: relationship_type_id (fk), relationship, contributing

Now, I want to use a single query to get:

All the relationship_type table's records (i.e. SELECT * FROM relationship_type)
And the specific relationship of the user who has id = 5

So the result is something like this (assuming table relationship_type has only 2 records)

"data": [
        {
            "relationship": "Friend" // for user id = 5
        },
        {
            "relationship_type_id": "1"
            "relationship": "Partner"
        },
        {
            "relationship_type_id": "2"
            "relationship": "Friend"
        }
    ],

SELECT columns FROM table1 JOIN table2 ON columnA = columnB [WHERE other conditions] — jarlh
– jarlh, Commented May 12, 2015 at 7:29

Qaisar Satti · Accepted Answer · 2015-05-12 07:52:37Z

1

SELECT user.id, user.name
FROM user
LEFT JOIN relationship_type
ON user.relationship_type_id=relationship_type.relationship_type_id WHERE user.id=5;

edited May 12, 2015 at 7:52

answered May 12, 2015 at 7:30

Qaisar Satti

2,7622 gold badges20 silver badges36 bronze badges

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

4 Comments

nogias Over a year ago

Oops, it lists everything.

Qaisar Satti Over a year ago

off course it is simple query to fetch record from two tables put you conditions and customize it. just use user.relationship_type_id=relationship_type.relationship_type_id where and put condition the data will be filter.

nogias Over a year ago

Is there anyway I could list everything in table relationship_type as well?

Qaisar Satti Over a year ago

relationship_type.relationship add column after column user.name with comma separated you will get you desired column

shA.t · Accepted Answer · 2015-05-12 10:06:18Z

0

SELECT 
     rt.*, u.*
FROM 
     relationship_type rt
  LEFT JOIN 
     user u
    ON rt.relationship_type_id = u.relationship_type_id
WHERE 
     u.id = 5
UNION
SELECT *
FROM relationship_type;

edited May 12, 2015 at 10:06

shA.t

17.1k5 gold badges59 silver badges121 bronze badges

answered May 12, 2015 at 7:50

marijnz0r

93410 silver badges23 bronze badges

1 Comment

nogias Over a year ago

It's close. Is there anything I could list everything within table relationship_type as well?

Collectives™ on Stack Overflow

Get all records from a table along with a selected record in single MySQL query statement

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related