How to change SQL to show data from database that a user does not have in their account

Question

0.00/5 (No votes)

See more:

Hi,

I have a SQL command that pulls data from a database to show a list of systems a user has access to but as i am new to SQL i am unsure of how to change it to show what systems the user does not have access to.

So if i have access to 3 of 5 systems i need the command to show me the other 2 systems i do not have access to.

SQL

select udesc.description
from u_systems_dev usys, u_user_systems_dev udesc
where usys.system_id = udesc.id
and   usys.user_id = 'username';

I know it sounds simple but this has stumped for a couple days after trying to take parts of it to make a new command i cant get it working.

Would it be something like this?..

XML

select udesc.description
from u_usystems_dev usys, u_user_systems_dev udesc
where usys.system_id <> udesc.id
and   usys.user_id <> 'username';

Thank you

Posted 11-Jan-15 23:31pm

Member 11355710

Updated 12-Jan-15 1:11am

v3

Add a Solution

2 solutions

Solution 1

Try this: it should show every row from usys (with the row data - probably some name - you want to show) and the description for those items the user has rights to.

If you want only udesc.description then write ISNULL(udes.description, "NO RIGHTS") - ISNULL is sql server function, in oracle use NVL(udesc.description, "NO RIGHTS")

Both functions will return text NO RIGHTS if udesc.description is NULL (i.e. the user has no rows in udesc.

SQL

select usys.some-field, udesc.description
from u_systems_dev usys
LEFT JOIN u_user_systems_dev udesc ON  usys.system_id = udesc.id
WHERE
usys.user_id = 'username';

If this helps please take time to accept the solution. Thank you.

Posted 11-Jan-15 23:57pm

Sinisa Hajnal

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sumit Jawale · Accepted Answer · 2015-01-12T00:10:00

Solution 2

Hi Try this :

SQL

select udesc.description
from u_systems_dev usys
where usys.system_id NOT IN (selct id from u_user_systems_dev )

Posted 12-Jan-15 0:10am

Sumit Jawale