Microsoft SQL Server forum - How to create SELECT?

Author

How to create SELECT?

staeri@gmail.com

2006-02-25, 9:45 am

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

markc600@hotmail.com

2006-02-25, 9:45 am

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')

David Portas

2006-02-25, 9:45 am

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
--

staeri@gmail.com

2006-02-25, 9:45 am

Thank's a lot for excellent help!

Regards,

S

staeri@gmail.com

2006-02-25, 9:45 am

Thank's a lot for excellent help!

Regards,

S

Sponsored Links