?
Solved

Inner Join SQL query question

Posted on 2012-09-04
7
Medium Priority
?
460 Views
Last Modified: 2012-09-05
Hi,

 I have one table where I have records of screens with functions for each one of the screens and a bit field for the access flag

 TABLE: ScreenFunctions

 Screen          Function_ID          Permit
 -------------------------------------------------------------
 Screen1          1                        1
 Screen1          2                        1

 and I have another table where a have the description of the functions

 TABLE: Functions

 Function_ID          Function
 ----------------------------------------------------
 1                           INSERT
 2                           UPDATE
 3                           DELETE
 4                           SELECT

 I need a query where I can get the rest of the functions with a Permit value of NULL if does not exist in the ScreenFunctions table, something like this

 Screen            Function         Permit
 ---------------------------------------------------------------
 Screen1           INSERT            1
 Screen1           UPDATE          1
 Screen1           DELETE            NULL
 Screen1           SELECT            NULL


Thanks
Oscar
0
Comment
Question by:negreteo
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 38364855
would you be expecting something more like this?

Screen            Function         Permit
 ---------------------------------------------------------------
 Screen1           INSERT            1
 Screen1           UPDATE          1
 NULL                DELETE          NULL
 NULL              SELECT            NULL

Since you don't have screen functions associated with function ID 3 or 4... I don't see how you associated it with Screen 1 in your sample desired result.

supposing my assumptions is correct... I think the following would work.

select ScreenFunctions.screen, Functions.function, ScreenFunctions.permit
from Functions 
left join ScreenFunctions
On ScreenFunctions.Function_ID = Functions.Function_ID
Order by Functions.Function_ID

Open in new window

0
 

Author Comment

by:negreteo
ID: 38364959
I need to show in a grid the null association between screen and all functions, even thought it does not exist a record between them

I tried your query and I do not get the null values records , I get 1 for all functions though

This is what I am getting

Screen            Function         Permit
 ---------------------------------------------------------------
 Screen1           INSERT            1
 Screen1           UPDATE          1
 Screen1           DELETE            1 (Should be NULL)
 Screen1           SELECT            1 (Should be NULL)


Thanks
0
 
LVL 12

Accepted Solution

by:
Habib Pourfard earned 2000 total points
ID: 38364961
SELECT DISTINCT
        SF1.Screen,
        [Function],
        SF2.Permit
FROM    Functions F 
        CROSS JOIN ScreenFunctions SF1 
        LEFT JOIN ScreenFunctions SF2 ON SF2.Screen = SF1.Screen AND SF2.Function_ID = F.Function_ID AND SF2.Permit = 1
ORDER BY SF1.Screen

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 19

Expert Comment

by:Ken Butters
ID: 38365183
Only way that Permit is Non-Null.... would be if you have additional records in screenFunctions Table not listed in your example.

You would have to have additional records where functionID is equal to 3, or 4.
0
 

Author Comment

by:negreteo
ID: 38365591
Actually the last query from "pourfard" works perfectly,  I just need one last change in the query, I just realized that also the Screen name is in another table and it is linked to the ScreenFunctions by a Screen_ID field, how do I add this to the query without affecting it?

Table: Screens
Screen_ID      Screen
----------------------------------------
1                    Screen1


Thanks
0
 
LVL 41

Expert Comment

by:Sharath
ID: 38365709
You can try like this.
select S.Screen,F.[Function],
       case when SF.Screen_ID = S.Screen_ID then SF.Permit end Permit
  from Functions F
  left join ScreenFunctions SF on F.Function_ID = SF.Function_ID
  cross join Screens S 
  order by S.Screen_ID

Open in new window

Tested with sample data.
declare @ScreenFunctions table (Screen_ID int, Function_ID int, Permit int)
insert @ScreenFunctions values (1,1,1),(1,2,1)
declare @Functions table (Function_ID int, [Function] nvarchar(20))
insert @Functions values (1,'INSERT'),(2,'UPDATE'),(3,'DELETE'),(4,'SELECT')
declare @Screens table (Screen_ID int, Screen nvarchar(20))
insert @Screens values (1,'Screen1')

select S.Screen,F.[Function],
       case when SF.Screen_ID = S.Screen_ID then SF.Permit end Permit
  from @Functions F
  left join @ScreenFunctions SF on F.Function_ID = SF.Function_ID
  cross join @Screens S 
  order by S.Screen_ID

/*
Screen	Function	Permit
Screen1	INSERT	1
Screen1	UPDATE	1
Screen1	DELETE	NULL
Screen1	SELECT	NULL
*/  

Open in new window

0
 
LVL 12

Expert Comment

by:Habib Pourfard
ID: 38366517
SELECT  S.Screen,
        F.[Function],
        SF.Permit
FROM    Functions F 
        CROSS JOIN Screens S
        LEFT JOIN ScreenFunctions SF ON SF.Screen = S.Screen AND SF.Function_ID = F.Function_ID AND SF.Permit = 1
ORDER BY S.Screen

Open in new window

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question