How to show 0 or null values if record is not found in reference table

Question

0.00/5 (No votes)

See more:

I have 3 tables UserMaster , pagesMaster and pageRightMaster

UserMaster table stores the users data,
pagesMaster stores the pages data,
pageRightMaster stores the records of which user have what permissions.

here are table structure

https://i.stack.imgur.com/9OiOR.png[^]

Now i want to display all the pages from pages master table user wise with their permissions. If no permission record found for user for particular page, it should display as 0.

it means, if user selects the userid 2, then all the pages from pages_master table will be displayed along with it's page_permissions. If there is no record in page_permission table for particular user that means the user have no permission and it should display as 0.

What I have tried:

I tried with multiple join queries but it's vain

Posted 21-Jul-19 23:56pm

Naikniket

Updated 30-Jul-19 18:21pm

Add a Solution

2 solutions

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)

Dominic Burford · Answer 1 · 2019-07-22T00:41:00

Solution 1

If you're doing a JOIN between tables and there is no matching record, then a NULL value is used to denote this. Instead of looking for 0 look for NULL instead.

Posted 22-Jul-19 0:41am

Dominic Burford

Comments

Naikniket 22-Jul-19 6:45am

I am using this query
select * from pages_mst u
left join user_page_rights_mst up on u.id = up.fk_page_id
left join users on up.fk_userId = users.id

Which gives me all pages with null values as said, but if i want to see for specific user

select * from pages_mst u
left join user_page_rights_mst up on u.id = up.fk_page_id
left join users on up.fk_userId = users.id where users.id=1

Then it only shows the pages that are in permission table. not nulled records

Richard Deeming 22-Jul-19 15:17pm

Move the condition into the join:

SELECT * FROM pages_mst u
LEFT JOIN user_page_rights_mst up ON u.id = up.fk_page_id And up.fk_userId = 1
LEFT JOIN users ON up.fk_userId = users.id

Putting it in the WHERE clause runs the filter too late, and removes the records where users.id is NULL.

Dominic Burford 22-Jul-19 7:21am

The user is not returned in the query when you add the WHERE clause. Are they there before you add the WHERE clause?

Harsh.Shah.ifour · Answer 2 · 2019-07-30T18:22:00

Solution 3

use LEFT JOIN to display rows from "UserMaster", which is not in "pageRightMaster" e.g.

select * from UserMaster us 
left join pageRightMaster pg on u.id = pg.fk_userId

Posted 30-Jul-19 18:22pm

Harsh.Shah.ifour