How to create SELECT?

I have the two following tables:

tblProject:
ProjectID
5001
5002
6001
6002
7001
7002

tblProject_type:
ProjectTypeID ProjectIDFrom ProjectIDTo
A                   5000               5999
A                   7000               7999

I need to create a SELECT statement which shows all records from
tblProjects WHERE ProjectTypeID = A. In this case every project except
6001 and 6002.

Unfortunately I can't have the ProjectTypeID in tblProject because each
project can belong to many project types. I know it sounds crazy but
that's the way my customers company is organized.

I'm very grateful for help in the right direction!

Regards,

S

CREATE TABLE  tblProjects(ProjectI
D INT)
INSERT INTO  tblProjects(ProjectI
D) VALUES(5001)
INSERT INTO  tblProjects(ProjectI
D) VALUES(5002)
INSERT INTO  tblProjects(ProjectI
D) VALUES(6001)
INSERT INTO  tblProjects(ProjectI
D) VALUES(6002)
INSERT INTO  tblProjects(ProjectI
D) VALUES(7001)
INSERT INTO  tblProjects(ProjectI
D) VALUES(7002)

CREATE TABLE  tblProject_type(Proj
ectTypeID  CHAR(1),ProjectIDFro
m
INT,ProjectIDTo INT)
INSERT INTO  tblProject_type(Proj
 ectTypeID,ProjectIDF
rom,ProjectIDTo)
 VALUES('A',5000,5999
)
INSERT INTO  tblProject_type(Proj
 ectTypeID,ProjectIDF
rom,ProjectIDTo)
 VALUES('A',7000,7999
)


SELECT ProjectID
FROM tblProjects
WHERE EXISTS (SELECT * FROM tblProject_type
WHERE ProjectID BETWEEN ProjectIDFrom AND ProjectIDTo
AND ProjectTypeID='A')

sta...@gmail.com  wrote:
> I have the two following tables:
>
> tblProject:
> ProjectID
> 5001
> 5002
> 6001
> 6002
> 7001
> 7002
>
> tblProject_type:
> ProjectTypeID ProjectIDFrom ProjectIDTo
> A                   5000               5999
> A                   7000               7999
>
> I need to create a SELECT statement which shows all records from
> tblProjects WHERE ProjectTypeID = A. In this case every project except
> 6001 and 6002.
>

Try:

SELECT projectid
FROM tblProject AS P
WHERE NOT EXISTS
(SELECT *
FROM tblProject_type AS T
WHERE P.projectid BETWEEN T.projectidfrom AND T.projectidto
AND T.projecttypeid = 'A');

> Unfortunately I can't have the ProjectTypeID in tblProject because each
> project can belong to many project types. I know it sounds crazy but
> that's the way my customers company is organized.

Instead of what you posted, a more common solution would be:

CREATE TABLE  tblProject_ProjectTy
pe
(projectid INTEGER NOT NULL
REFERENCES tblProject (projectid),
projecttypeid CHAR(1) NOT NULL
REFERENCES tblProjectType (projecttypeid),
PRIMARY KEY (projectid, projecttyypeid);

There isn't necessarily anything wrong with what you posted and your
version certainly could make for a smaller table but it's also hard to
avoid redundancy. Specifically, you would have to use a trigger to
prevent overlapping ranges of rows for the same type - otherwise you
could get duplicate rows out of joins, which could give you incorrect
results.

The benefit of my  tblProject_ProjectTy
pe version is that there is no
redundancy and joins to the table are always equijoins.

Hope this helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Thank's a lot for excellent help!

Regards,

S

Thank's a lot for excellent help!

Regards,

S