How to create select query

I need help with creating a select query which only shows projects
from tblProject where ProjectID exists in the specified interval in
tblProjectType (ProjectIDFrom, ProjectIDTo).

tblProject:
ProjectID

tblProjectType
ProjectTypeID
ProjectIDFrom
ProjectIDTo

I'm very grateful for help!

Best regards,

// S

From the naming of your tables, it sounds like a project can have only
one project type. Rather than putting a range of project ids in the
project type table, it would probably be more appropriate to put the
ProjectTypeId in the Project table.

tblProject:
ProjectID
ProjectTypeID


tblProjectType
ProjectTypeID


The advantage of this is that any project can be changed from one type
to another later and the project ID doesn't matter. The types are
likely more stable than the project id numbers anyway. You don't want
your project id values to NEED to be changeable.

If you model your database this way, all you need for your select
is...

SELECT *
FROM tblProject
WHERE ProjectTypeID = 123

Now having said that, here's what you requested...

SELECT tblProject.*, tblProjectITypeID.ProjectTypeID FROM tblProject
INNER JOIN tblProjectType ON tblProject.ProjectID BETWEEN
tblProjectType.ProjectIDFrom AND tblProjectType.ProjectIDTo
WHERE tblProjectType.ProjectTypeID = 123

-Eric Isaacs

You should seriously consider Eric's advice about changing your schema.
However, to answer your question as asked, you could do

Select p.ProjectID
From tblProject p
Inner Join tblProjectType t On p.ProjectID Between t.ProjectIDFrom And
t.ProjectIDTo;

Tom

wrote in message
news:352358a5-5876-41f7-89b4-3781b930dbbe-at-f63g2000hsf.googlegroups.com...
>I need help with creating a select query which only shows projects
> from tblProject where ProjectID exists in the specified interval in
> tblProjectType (ProjectIDFrom, ProjectIDTo).
>
> tblProject:
> ProjectID
>
> tblProjectType
> ProjectTypeID
> ProjectIDFrom
> ProjectIDTo
>
> I'm very grateful for help!
>
> Best regards,
>
> // S

On 19 Aug, 17:08, "Tom Cooper"
wrote:
> You should seriously consider Eric's advice about changing your schema.
> However, to answer your question as asked, you could do
>
> Select p.ProjectID
> From tblProject p
> Inner Join tblProjectType t On p.ProjectID Between t.ProjectIDFrom And
> t.ProjectIDTo;
>
> Tom
>
> wrote in message
>
> news:352358a5-5876-41f7-89b4-3781b930dbbe-at-f63g2000hsf.googlegroups.com...
>
>
>
> >I need help with creating a select query which only shows projects
> > from tblProject where ProjectID exists in the specified interval in
> > tblProjectType (ProjectIDFrom, ProjectIDTo).
>
> > tblProject:
> > ProjectID
>
> > tblProjectType
> > ProjectTypeID
> > ProjectIDFrom
> > ProjectIDTo
>
> > I'm very grateful for help!
>
> > Best regards,
>
> > // S- Dölj citerad text -
>
> - Visa citerad text -

Thank you for the help! One project can belong to many project types
that's why the schema looks like this.

// S

> Thank you for the help! One project can belong to many project types
> that's why the schema looks like this.

S,

If one project can have many project types and project types can be
associated with many projects you have a many to many relation. Given
your original tables...

tblProject:
ProjectID

tblProjectType
ProjectTypeID
ProjectIDFrom
ProjectIDTo

....If you just create a table in between that represents the relation
between these, you can replicate that many to many relationship...


tblProject:
ProjectID

tblProjectType
ProjectTypeID

tblProjectProjectType
ProjectID
ProjectTypeID

....that extra table gives you a one to many relationship between
project and projectprojecttype and a one to many relationship between
projecttype and project, which in total gives you the many to many
relationship between project and project type. But this can handle
lots of data without the issue of maintaining the keys between range
values.

What you did could also work if the ranges cross over each other
(allowing the many project types), but I think you'll find it more
difficult to maintain in the long run. You'll always have to figure
out what key to use for your project that falls within the correct
ranges to identify which project types it is, whereas with this other
model, you just have to select which project types the project is (and
you can remove them too without changing the key.)

I hope that helps!

-Eric Isaacs