Databases
StackOverflow2013
Postsdbo
POWhere clause in sql query remove also null values
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhere clause in sql query remove also null values
    primarykey
    data
    text
    <p>I have these tables:</p> <p>'Users'</p> <pre><code>id email name last_access 1 luca@gmail.com Luca Pluto 2012-10-05 17:21:22.0 2 pippo@gmail.com Irene Pippo 2012-10-05 17:22:25.0 </code></pre> <p>'Nets_permissions'</p> <pre><code>user_id network_id perm 1 1234 3 1 1235 1 2 1235 3 </code></pre> <p>I've written this query:</p> <pre><code>SELECT u.id, u.name, n.perm FROM users as u LEFT OUTER JOIN nets_permissions as n ON u.id = n.user_id WHERE n.network_id=1234 AND n.perm &lt;&gt; 3 </code></pre> <p>because I want users that already have a permission for network_id (1234) , but also users that don't have a permission for the network_id. In other words I would have this result query:</p> <pre><code>2 null null </code></pre> <p>because the user Luca Pluto with id=1, for the net 1234, have perm=3 so I want left out. Instead, the user Irene Pippo with id=2 doesn't have any permission on the 1234 net. So it's row must have net_id and perm set to null.</p> <p>My query result is empty. I don't know why. Without the clause n.perm &lt;> 3 seems to work well, but after also the null value are left out, not only the rws with perm=3.</p> <p>I've also tried in this way:</p> <pre><code>SELECT u.id, u.name, n.perm FROM users as u LEFT OUTER JOIN (select * from nets_permissions WHERE network_id=1234) as n on u.id = n.user_id WHERE n.perm &lt;&gt; 3 </code></pre> <p>but it doesn't work. without the WHERE clause all works. After no. The result query is empty.</p> <p>How I can resolve this problem? I need that the perm column is a value or null, I can't remove this column.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USsharkbait
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload