CODEWITHSUNDEEP
sql
imthebest
imthebest

Reputation: 1

SQL query considering two tables

There is a table called xengallery_album_permissions with columns: album_id int(10), permission enum ('view', 'add'), access_type enum('public', 'followed', 'members', 'private', 'shared')

There is another table called xengallery_album with columns: album_id int(10), album_state enum('visible', 'moderated', 'deleted')

Right now I have the following query:

SELECT `album_id`
FROM `xengallery_album_permission`
WHERE `permission` = "view" AND `access_type` != "public"

But I want to filter the results and only list album_id that have album_state = visible

How can I modify my query to achieve that? Thanks!

Upvotes: 0

Views: 52

Answers (2)

Gauravsa
Gauravsa

Reputation: 6524

You can try this:

SELECT a.album_id
FROM xengallery_album_permission a
inner join xengallery_album b
on a.album_id == b.album_id
WHERE a.permission = "view" AND a.access_type != "public"
and b.album_state = "visible"

Here is a short tutorial on Inner Joins and Alias

Upvotes: 0

Max
Max

Reputation: 985

SELECT p.album_id
FROM `xengallery_album_permission` p
INNER JOIN xengallery_album a ON a.album_id = p.album_id
WHERE p.permission = "view" 
  AND p.access_type != "public"
  AND a.album_state  = "visible"

Upvotes: 1

Related Questions