|
|
Posted by David Portas on 02/23/06 12:58
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/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|