CODEWITHSUNDEEP
sqlpostgresqljoin
bnnoor
bnnoor

Reputation: 696

How to compare attribute in other table

I have two table :

table 1:

users
========+===========
user_id | user_name
--------+-----------
1       | bn
2       | gn
3       | en

table 2:

user_attr
========+===========+=============
user_id | attr_name | attr_value
--------+-----------+-------------
1       | sell      | 1
2       | first     | 245
1       | au        | 246
2       | sell      | 1

In PostgreSQL I want the user id from table users who doesn't have a 'au','first' or 'sell' in user_attr table .

I tested below query but not work :

SELECT users.user_id
FROM users 
    INNER JOIN user_attrs ON users.user_id = user_attrs.user_id 
WHERE
    users.group_id = '$group' AND (user_attrs.attr_name != 'first' 
    AND user_attrs.attr_name != 'sell' AND user_attrs.attr_name != 'au') 
LIMIT 1;

Upvotes: 0

Views: 55

Answers (2)

Shahid Ahmad
Shahid Ahmad

Reputation: 774

    SELECT users.user_id 
      FROM users 
INNER JOIN user_attrs ON users.user_id = user_attrs.user_id 
     WHERE users.group_id = '$group' 
       AND user_attrs.attr_name NOT IN ('first','au','sell') LIMIT 1";

Upvotes: 0

Raffaello.D.Huke
Raffaello.D.Huke

Reputation: 552

I get what problem you got. If the data is the same as you gave us, it's very simple, you use inner join and attrs table only got user_id =1 and 2. so you got nothing. just try left join:

SELECT users.user_id FROM users 
    LEFT JOIN user_attrs ON users.user_id = user_attrs.user_id 
WHERE users.group_id = '$group'
   AND (user_attrs.attr_name != 'first' 
   AND user_attrs.attr_name != 'sell'
   AND user_attrs.attr_name != 'au') 
LIMIT 1

Upvotes: 1

Related Questions