CODEWITHSUNDEEP
mysqlsqlselectjoin
Jon Eagleson
Jon Eagleson

Reputation: 23

MYSQL - QUERY FROM TWO TABLES

Question - let's say I have 2 tables.

Table 1 - name is permission_list, columns are ID (unique ID), col_ID, user_ID

Table 2 - name is list_entries, Columns are ID (unique ID), title, description, status

I want to select all the rows from table 2 that have status of 'public' as well as all the rows from table 2 that the ID from table 2 shows up in table 1 (under the column col_ID) AND if the user_ID in table 1 matches a certain value. So, anything public, or anything that this specific user has listed under the permissions table. This query would also remove duplicates - in case the user gets a public entry listed in their permissions_list, it wouldn't show up twice.

Hope that makes sense!

Upvotes: 0

Views: 68

Answers (3)

EliD
EliD

Reputation: 11

SELECT table_2.status, table_2.ID, table_1.col_ID
FROM table_1 JOIN table_2
WHERE table_2.status = 'public'
    AND table_2.ID = table_1.col_ID
    AND table_1.user_ID = 'certain value'
    ;

Try this

Upvotes: 0

vhu
vhu

Reputation: 12788

Here you go:

SELECT DISTINCT table2.* from table2
LEFT JOIN table1 USING (id)
WHERE status='public'
OR user_ID='someuser';

Upvotes: 1

Jasmine
Jasmine

Reputation: 4029

You need to get some education on JOIN for your first thing, and the second thing is called DISTINCT.

Start here... https://www.google.com/

You have not specified your join condition so we can't give you code samples really. Also the way you worded your question, I'm not entirely sure you don't want a UNION. Read up on those concepts and come back here when you can improve the question.

Upvotes: 0

Related Questions