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,
SCREATE TABLE tblProjects(ProjectID INT)
INSERT INTO tblProjects(ProjectID) VALUES(5001)
INSERT INTO tblProjects(ProjectID) VALUES(5002)
INSERT INTO tblProjects(ProjectID) VALUES(6001)
INSERT INTO tblProjects(ProjectID) VALUES(6002)
INSERT INTO tblProjects(ProjectID) VALUES(7001)
INSERT INTO tblProjects(ProjectID) VALUES(7002)
CREATE TABLE tblProject_type(ProjectTypeID CHAR(1),ProjectIDFrom
INT,ProjectIDTo INT)
INSERT INTO tblProject_type(ProjectTypeID,ProjectIDFrom,Projec tIDTo)
VALUES('A',5000,5999)
INSERT INTO tblProject_type(ProjectTypeID,ProjectIDFrom,Projec tIDTo)
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_ProjectType
(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_ProjectType 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/...US,SQL.90).aspx
--|||Thank's a lot for excellent help!
Regards,
S|||Thank's a lot for excellent help!
Regards,
S
)