SQL-Query with joining two tables

0
votes

I am breaking my brain on a SQL, maybe someone can give me a hint.

Example setup: 

CREATE TABLE album
(
  name text NOT NULL,
  id serial NOT NULL,
  user_id bigint NOT NULL
);

CREATE TABLE album_share
(
  id integer NOT NULL,
  user_id bigint NOT NULL,
  album_id bigint NOT NULL,
  permission character varying(20)
 );

INSERT INTO album_share VALUES (21, 23, 8, 'OWNER');
--INSERT INTO album_share VALUES (22, 22, 8, 'READ');

INSERT INTO album VALUES ('album1', 8, 23);
INSERT INTO album VALUES ('album2', 12, 23);
INSERT INTO album VALUES ('album3', 13, 22);
INSERT INTO album VALUES ('album4', 15, 22);



--Expecting with user_id=23
-- album1,album2
--!! Failed: album1 is not in the result !!

SELECT * from album a 
LEFT JOIN album_share share ON share.album_id = a.id 
where (a.user_id = 23 or share.user_id = 23)  
  and (share.permission is null or share.permission != 'OWNER');

--Expecting with user_id=22
-- album3,album4
-- Works fine

SELECT * from album a 
LEFT JOIN album_share share ON share.album_id = a.id 
where (a.user_id = 22 or share.user_id = 22)  
  and (share.permission is null or share.permission != 'OWNER');

The example is also available online: http://rextester.com/DCD25332

I am trying to select from two tables joined and filterd by their data. It is better explained in the fiddle. The first query with user_id=23 doesn't select the album, I think its because of the filtering, but I can not solve it.

4
sqlpostgresqlpostgresql-9.4
The reason you are not getting 'album1' in your first query is due to this condition share.permission is null or share.permission != 'OWNER'. Since album_id = 8 permission is set to OWNER, your SQL criteria will always be FALSE. - Maruli

4 Answers

1
votes

The first query does not return a row with album1 because the associated album_share.permission is OWNER, which is filtered out as it does not satisfy the condition permission IS NULL OR permission != 'OWNER'.

1
votes

The album1 has a relation to the album_share table and 'share.permission is null' condition never going to be true in that case.

Changing it like below must work,

SELECT * from album a 
LEFT JOIN album_share share ON share.album_id = a.id 
where (a.user_id = 23 or share.user_id = 23 or share.permission != 'OWNER');
0
votes

It is not because you say you don't want it, look here:

share.permission != 'OWNER'

This is the same than when the share permision IS NOT 'OWNER'. if you want the oposite just say '=' and not '!='.

--Expecting with user_id=23 -- album1,album2 --!! Failed: album1 is not in the result !!

SELECT * from album a LEFT JOIN album_share share ON share.album_id = a.id where (a.user_id = 23 or share.user_id = 23) and (share.permission is null or share.permission != 'OWNER');

0
votes

If you execute the below query you will get 2 records.

   SELECT * from album a 
   LEFT JOIN album_share share 
   ON share.album_id = a.id 
   where (a.user_id = 23 or share.user_id = 23);

Output

And then when you add the second condition in where clause it filters one record.I don't know if you really want to check if it's permission is not equal to OWNER. If you want both the records then you will have to modify the second query and your query will look something like this:

SELECT * from album a 
LEFT JOIN album_share share 
ON share.album_id = a.id 
where (a.user_id = 23 or share.user_id = 23)
and (share.permission is null or share.permission = 'OWNER');

Output:

Output

Your Query:

SELECT * from album a 
LEFT JOIN album_share share 
ON share.album_id = a.id 
where (a.user_id = 23 or share.user_id = 23)
and (share.permission is null or share.permission != 'OWNER');

Output:

Output-3