Simplified Query

[james.sriot]

Ok, I have an interesting issue where I want to condense multiple queries being run on one page into one query if possible.

Here is the scenario. This is a manager section to manage links and whether or not someone has access to them or not.

The layout is as follows (on the webpage):


Group 1 Group 2 Group 3
Link 1 x
Link 2 x x
Link 3 x

There are 3 tables being worked with. One table houses the links, another the groups and a linking table in the middle. So that layout is the following:

LINKS LNK GROUPS
id id id
link link_id group
group_id


If the a group can view a link then it is added to the LNK table. So, for example, assuming that the links have an ID of (1,2,and 3) and the groups have the same (1,2,and 3) the linking table would look something like this (with what is checked in the example

LNK
id link_id group_id
1 1 1
2 2 2
3 2 3
4 3 2


With that, here is what I am trying to accomplish. In my script I have to return the LINKS from the links table as well as the GROUPS from the groups table. I have a simple return for this so it displays them going in the correct direction.

What I want to do is for each row returned I want to associate the columns that should be checked by returning a 1 or a 0. I am not sure how to do this because when using the linking table all that will match is what is properly linked. I want to be able to say that there should be at least three returns per row (one for each group) then go to the next row get the link and check 1 .... 2..... 3 and if there is a match in the linking table return a 1 so i can display that on the screen.

This will avoid having to run a loop of queries for each stop on the grid.

Hopefully not too confusing. Any ideas would be appreciated.

[james.sriot]

In the above post - imagine that the spacing actually worked where I have the "X" grid

[chrisrlong]

This better not be for homework!

And your table and column names are awful, so I changed them slightly (to protect the guilty)

CREATE TABLE LINK_T (LINK_ID NUMBER, LINK_NAME VARCHAR2(30));
CREATE TABLE GROUP_T (GROUP_ID NUMBER, GROUP_NAME VARCHAR2(30));
CREATE TABLE GROUP_LINK_T (GROUP_LINK_ID NUMBER, GROUP_ID NUMBER , LINK_ID NUMBER);

INSERT INTO LINK_T(LINK_ID, LINK_NAME) VALUES(1,'LINK 1');
INSERT INTO LINK_T(LINK_ID, LINK_NAME) VALUES(2,'LINK 2');
INSERT INTO LINK_T(LINK_ID, LINK_NAME) VALUES(3,'LINK 3');

INSERT INTO GROUP_T(GROUP_ID, GROUP_NAME) VALUES(1,'GROUP 1');
INSERT INTO GROUP_T(GROUP_ID, GROUP_NAME) VALUES(2,'GROUP 2');
INSERT INTO GROUP_T(GROUP_ID, GROUP_NAME) VALUES(3,'GROUP 3');

INSERT INTO GROUP_LINK_T(GROUP_LINK_ID, GROUP_ID, LINK_ID) VALUES(1,1,1);
INSERT INTO GROUP_LINK_T(GROUP_LINK_ID, GROUP_ID, LINK_ID) VALUES(2,2,2);
INSERT INTO GROUP_LINK_T(GROUP_LINK_ID, GROUP_ID, LINK_ID) VALUES(3,2,3);
INSERT INTO GROUP_LINK_T(GROUP_LINK_ID, GROUP_ID, LINK_ID) VALUES(3,3,2);


SELECT
L.LINK_NAME ,
MAX(DECODE(G.GROUP_ID,1,'X',NULL)) AS G1_FLG ,
MAX(DECODE(G.GROUP_ID,2,'X',NULL)) AS G2_FLG ,
MAX(DECODE(G.GROUP_ID,3,'X',NULL)) AS G3_FLG
FROM
LINK_T L ,
GROUP_LINK_T GL ,
GROUP_T G
WHERE
GL.LINK_ID (+)= L.LINK_ID AND
G.GROUP_ID (+)= GL.GROUP_ID
GROUP BY
L.LINK_NAME
ORDER BY
L.LINK_NAME;