Need help with a Complicated Query

Need help with a Complicated Query

Post by Ash Bonebrak » Fri, 20 Apr 2001 22:24:47



I have a table for organizations, programs and programdetails.

tblOrg has fields:
orgID - Primary Key
orgname
address
state
orglevel

tblPrograms has fields:
programID - Primary Key
programName
programType

tblProgramDetials
detailID - Primary Key
programID
orgID

This allows me to assign multiple programs to organizations based on the
relationships through Program Details.

OK my problem, which may be a complicated query or table design

I need to be able to search for an organization which within a chosen state
which has a selected group of programs.
Sounds easy but let me demonstrate my problem.
My query is as fallows:

SELECT tblOrg.OrgName, tblOrg.state, tblOrg.orglevel, tblprograms.programid,
tblprograms.programname
FROM
    tblOrg INNER JOIN
    tblprogramdetails ON
    tblOrg.ORGID = tblprogramdetails.orgid INNER JOIN
    tblprograms ON
    tblprogramdetails.programid = tblprograms.programid
WHERE
   ((tblOrg.state IN ('alabama','alaska')) AND (tblprograms.programid IN
(2,3)))

This gives me all organizations in alabama or alaska which have either
programid 2 or 3
I want all organizations in alabama or alaska which have BOTH programid 2
AND 3

Since each program-to-organization assignment is an individual record, How
can I get these results?

Thanks for the help,
Ash

 
 
 

Need help with a Complicated Query

Post by Pavel Vorontso » Sat, 21 Apr 2001 00:15:53


Hello!

May I advise you to post the complete CREATE TABLE and INSERT statements
with sample data? It will be much more understandable then. See comments
below in text

Quote:> I have a table for organizations, programs and programdetails.

> tblOrg has fields:
> orgID - Primary Key
> orgname
> address
> state
> orglevel

> tblPrograms has fields:
> programID - Primary Key
> programName
> programType

> tblProgramDetials
> detailID - Primary Key
> programID
> orgID

Can you describe the meaning of the detailID column?
Your design may have sence only if you wants to have THE SAME program -
organization links, but I can't imagine such a situation. The common
approach for establishing many-to-many relationship is:
CREATE TABLE tblProgramDetails
( programID int NOT NULL FOREIGN KEY REFERENCES tblPrograms (ProgramID),
orgID int NOT NULL FOREIGN KEY REFERENCES tblOrg (orgID),
CONSTRAINT pk PRIMARY KEY (programID, orgID));

Quote:

> This allows me to assign multiple programs to organizations based on the
> relationships through Program Details.

> OK my problem, which may be a complicated query or table design

> I need to be able to search for an organization which within a chosen
state
> which has a selected group of programs.
> Sounds easy but let me demonstrate my problem.
> My query is as fallows:

> SELECT tblOrg.OrgName, tblOrg.state, tblOrg.orglevel,

tblprograms.programid,

Quote:> tblprograms.programname
> FROM
>     tblOrg INNER JOIN
>     tblprogramdetails ON
>     tblOrg.ORGID = tblprogramdetails.orgid INNER JOIN
>     tblprograms ON
>     tblprogramdetails.programid = tblprograms.programid
> WHERE
>    ((tblOrg.state IN ('alabama','alaska')) AND (tblprograms.programid IN
> (2,3)))

> This gives me all organizations in alabama or alaska which have either
> programid 2 or 3
> I want all organizations in alabama or alaska which have BOTH programid 2
> AND 3

This is a pure example of the relational division. In SQL it may be done by
several ways. Try

SELECT tblOrg.OrgName, tblOrg.state, tblOrg.orglevel, tblprograms.programid,
 tblprograms.programname
 FROM
     tblOrg, tblPrograms
 WHERE
    tblOrg.state IN ('alabama','alaska') AND
NOT EXISTS( SELECT 1 FROM tblProgramDetails pd
WHERE pd.orgID = tblOrg.orgID AND
          NOT EXISTS (SELECT 1 FROM tblProgramDetails pd1
           WHERE pd.orgID = pd1.orgID AND pd1.programID IN (2,3) AND
            pd1.programID = tblPrograms.programID))

- Show quoted text -

Quote:

> Since each program-to-organization assignment is an individual record, How
> can I get these results?

> Thanks for the help,
> Ash


 
 
 

Need help with a Complicated Query

Post by Leopol » Sat, 21 Apr 2001 00:18:13


You didn't provide test data and expected result, so this is an untested
attempt. Also the ProgramName in the select list will be for ProgramID 3
only, not ProgramID 2. Basically, the last join to tblprograms doesn't seem
to bring much.
Does this help?

SELECT tblOrg.OrgName, tblOrg.state, tblOrg.orglevel, tblprograms.programid,
tblprograms.programname
FROM
    tblOrg INNER JOIN
    tblprogramdetails T1 ON
    tblOrg.ORGID = T1.orgid AND T1.programid = 2 INNER JOIN
    tblprogramdetails T2 ON
    tblOrg.ORGID = T2.orgid AND T2.programid = 3 INNER JOIN
    tblprograms ON
    T2.programid = tblprograms.programid
WHERE
   tblOrg.state IN ('alabama','alaska')



Quote:> I have a table for organizations, programs and programdetails.

> tblOrg has fields:
> orgID - Primary Key
> orgname
> address
> state
> orglevel

> tblPrograms has fields:
> programID - Primary Key
> programName
> programType

> tblProgramDetials
> detailID - Primary Key
> programID
> orgID

> This allows me to assign multiple programs to organizations based on the
> relationships through Program Details.

> OK my problem, which may be a complicated query or table design

> I need to be able to search for an organization which within a chosen
state
> which has a selected group of programs.
> Sounds easy but let me demonstrate my problem.
> My query is as fallows:

> SELECT tblOrg.OrgName, tblOrg.state, tblOrg.orglevel,

tblprograms.programid,

- Show quoted text -

Quote:> tblprograms.programname
> FROM
>     tblOrg INNER JOIN
>     tblprogramdetails ON
>     tblOrg.ORGID = tblprogramdetails.orgid INNER JOIN
>     tblprograms ON
>     tblprogramdetails.programid = tblprograms.programid
> WHERE
>    ((tblOrg.state IN ('alabama','alaska')) AND (tblprograms.programid IN
> (2,3)))

> This gives me all organizations in alabama or alaska which have either
> programid 2 or 3
> I want all organizations in alabama or alaska which have BOTH programid 2
> AND 3

> Since each program-to-organization assignment is an individual record, How
> can I get these results?

> Thanks for the help,
> Ash

 
 
 

Need help with a Complicated Query

Post by Dmitri Kuznetso » Sat, 21 Apr 2001 13:23:30


Short data will help

General note, taken from some university book:

IN test a membership of elemnt in a set, like Your set is

SET A {
a,b,c,d,e,f,g

Quote:}

and you can test with IN set A if values are explicit members a,x and y.
To check if row is within a set, means checking a set A be a subset ob set B

To do this, use JOIN operators:

CREATE MyFriends
(
Name    ..
Age       ..
Sex       ..
)

CREATE YourFriends
(
Name    ..
Age       ..
Sex       ..
)

To select our friends (i want to show here also what to do if ther is no
unique person_id, otherwise you could have used select *from myfriends where
id in (select id form yourfriends))

SELECT    M.*
FROM        MyFriends AS M INNER JOIN YourFriends AS Y ON (M.Name=Y.Name)
AND (M.Age = Y.Age) AND (M.Sex=Y.Sex)

I would also like to point out how not in can be made (from MySQL
workarounds):
This selects my friends only:
SELECT    M.*
FROM       MyFriends AS M LEFT JOIN YourFriends AS Y ON (M.Name=Y.Name) AND
(M.Age = Y.Age) AND (M.Sex=Y.Sex)
WHERE    Y.Name IS NULL

General note on IN

Using IN often forces SQL server to:
generate a list of all Friend_IDs first and then match them to the existing
Friend_IDs in other table. Indicies can not be used effectively here,
because first of all all rows are retrieved and then compared, while within
inner join this can be "merged".

If You do not have a table with parameters, then create a temporary one and
use stored procedure. Or create a "params" table like

Query_ID    State            product
1                'texas'            'sqlserver'
1                'harjumaa'        'nt workstation'
1                'bayern'        'ms acces'
2..
2..
3..

and after performing  join, restict usin query_ID


Quote:> I have a table for organizations, programs and programdetails.

> tblOrg has fields:
> orgID - Primary Key
> orgname
> address
> state
> orglevel

> tblPrograms has fields:
> programID - Primary Key
> programName
> programType

> tblProgramDetials
> detailID - Primary Key
> programID
> orgID

> This allows me to assign multiple programs to organizations based on the
> relationships through Program Details.

> OK my problem, which may be a complicated query or table design

> I need to be able to search for an organization which within a chosen
state
> which has a selected group of programs.
> Sounds easy but let me demonstrate my problem.
> My query is as fallows:

> SELECT tblOrg.OrgName, tblOrg.state, tblOrg.orglevel,

tblprograms.programid,

- Show quoted text -

Quote:> tblprograms.programname
> FROM
>     tblOrg INNER JOIN
>     tblprogramdetails ON
>     tblOrg.ORGID = tblprogramdetails.orgid INNER JOIN
>     tblprograms ON
>     tblprogramdetails.programid = tblprograms.programid
> WHERE
>    ((tblOrg.state IN ('alabama','alaska')) AND (tblprograms.programid IN
> (2,3)))

> This gives me all organizations in alabama or alaska which have either
> programid 2 or 3
> I want all organizations in alabama or alaska which have BOTH programid 2
> AND 3

> Since each program-to-organization assignment is an individual record, How
> can I get these results?

> Thanks for the help,
> Ash

 
 
 

1. I need help with a complicated query - Thanks!

Dear newsgroupies and groupettes,

I'm wondering if the following query can be programmed. I know I could
probably go around it with Excel, but I'd rather do it direct if at all
possible.

I have the following tables:

TABLE users:
shopper_id: unique identifier for each user

TABLE projects:
shopper_id: identifier for the project owner
project_id: unique project identifier

Every user can potentially own several projects.

I'd like a listing showing every user and the total number of projects
they're working on:

shopper_id        total_projects
-----------        --------------
XYZ-1234        5
ABC-3245        0
ADE-2356        7

(...)

Thanks for any help,

Carlos E. Granier

2. Sybase/Mainframe connectivity

3. Need help with complicated query

4. table variable vs temptable

5. Need Help with Complicated Query

6. AS/400 to SQL Server

7. Database: Need help with complicated query...

8. can someone help with complicated query...

9. Requesting help with complicated query

10. Help with complicated update query

11. Help with complicated? query

12. Complicated query: help please