Searching Multiple Tables

Hi All,

I am building a search page for our HR database. I am struglling to create a
query which will be able to search multiple related tables.

The main database holds the contacts i.e

ContactId,
ContactName

Then I have two simple tables which hold the Skills and Roles we use

Skills Table
SkillId
SkillName

RolesTable
RoleId
RoleName

I then have two further tables to allow skills per user to be stored in the
table
UserSkills

ContactId
SkillId

and also have UserRoles where I store ContactId, RoleId

From my search page I want to search users who have preformed a certain role
in the past and/or have certain skill. What I am looking for here is someone
to help me build the top string in my page so I can do something like this

SQL = "Select ..... (this is the one I cannot work out"
SQL = SQL & " where roleid=" request.form("roles") and Skillid=" &
request.form("skillid")

If I create joines it gives me multiple records for the same contact.

Hope this all makes sense.

Regards
Jas

Re: Searching Multiple Tables

JP SIngh wrote:
> Hi All,
>
> I am building a search page for our HR database.

What database? Type and version please.

> I am struglling to
> create a query which will be able to search multiple related tables.
>
> The main database holds the contacts i.e
>
> ContactId,
> ContactName
>
> Then I have two simple tables which hold the Skills and Roles we use
>
> Skills Table
> SkillId
> SkillName
>
> RolesTable
> RoleId
> RoleName
>
> I then have two further tables to allow skills per user to be stored
> in the table
> UserSkills
>
> ContactId
> SkillId
>
> and also have UserRoles where I store ContactId, RoleId
>
> From my search page I want to search users who have preformed a
> certain role in the past and/or have certain skill.

"and" and "or" are mutually exclusive when speaking from a perspective
of boolean logic. You need to better define your requirements. I suspect
you actually mean "or", but in your attempted statement below, you are
using "and" ....

> What I am looking
> for here is someone to help me build the top string in my page so I
> can do something like this
>
> SQL = "Select ..... (this is the one I cannot work out"
> SQL = SQL & " where roleid=" request.form("roles") and Skillid=" &
> request.form("skillid")
>
> If I create joines it gives me multiple records for the same contact.
>
Again, I am not attempting this until you reveal the database type and
version. If I give a T-SQL answer, it will not work well in Jet.

Also ...
Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Searching Multiple Tables

Bob

The database is Access 2003 SP2.

Also I have pasted the actual query below that I have created in Access.

SELECT Contacts.ID, Contacts.FirstName, Contacts.LastName,
RolesMatrix.Roles.Id AS RoleId, SkillsMatrix.KeySkills.Id AS SkillID
FROM (Contacts LEFT JOIN SkillsMatrix ON Contacts.ID = SkillsMatrix.[Unique
Id]) LEFT JOIN RolesMatrix ON Contacts.ID = RolesMatrix.[Unique Id]
ORDER BY Contacts.ID

ID FirstName RoleId SkillID
1 VictoriaGray 12 6
1 VictoriaGray 13 4
1 VictoriaGray 1 4
2 KateGriffiths 3 28
2 KateGriffiths 3 37
2 KateGriffiths 3 25
2 KateGriffiths 3 4

When I loop through this recordset in my ASP page I get multiple records
where as I only need to see the Id/Names of the people who have a specific
skill or have a specific role experience.

Thanks
Jas


"Bob Barrows [MVP]" wrote in message
news:OkWZa$jEHHA.4132@TK2MSFTNGP04.phx.gbl...
> JP SIngh wrote:
>> Hi All,
>>
>> I am building a search page for our HR database.
>
> What database? Type and version please.
>
>> I am struglling to
>> create a query which will be able to search multiple related tables.
>>
>> The main database holds the contacts i.e
>>
>> ContactId,
>> ContactName
>>
>> Then I have two simple tables which hold the Skills and Roles we use
>>
>> Skills Table
>> SkillId
>> SkillName
>>
>> RolesTable
>> RoleId
>> RoleName
>>
>> I then have two further tables to allow skills per user to be stored
>> in the table
>> UserSkills
>>
>> ContactId
>> SkillId
>>
>> and also have UserRoles where I store ContactId, RoleId
>>
>> From my search page I want to search users who have preformed a
>> certain role in the past and/or have certain skill.
>
> "and" and "or" are mutually exclusive when speaking from a perspective
> of boolean logic. You need to better define your requirements. I suspect
> you actually mean "or", but in your attempted statement below, you are
> using "and" ....
>
>> What I am looking
>> for here is someone to help me build the top string in my page so I
>> can do something like this
>>
>> SQL = "Select ..... (this is the one I cannot work out"
>> SQL = SQL & " where roleid=" request.form("roles") and Skillid=" &
>> request.form("skillid")
>>
>> If I create joines it gives me multiple records for the same contact.
>>
> Again, I am not attempting this until you reveal the database type and
> version. If I give a T-SQL answer, it will not work well in Jet.
>
> Also ...
> Further points to consider:
> Your use of dynamic sql is leaving you vulnerable to hackers using sql
> injection:
> http://mvp.unixwiz.net/techtips/sql-injection.html
> http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Searching Multiple Tables

JP SIngh wrote:
> Bob
>
> The database is Access 2003 SP2.
>
> Also I have pasted the actual query below that I have created in
> Access.
>
> SELECT Contacts.ID, Contacts.FirstName, Contacts.LastName,
> RolesMatrix.Roles.Id AS RoleId, SkillsMatrix.KeySkills.Id AS SkillID
> FROM (Contacts LEFT JOIN SkillsMatrix ON Contacts.ID =
> SkillsMatrix.[Unique Id]) LEFT JOIN RolesMatrix ON Contacts.ID =
> RolesMatrix.[Unique Id]
> ORDER BY Contacts.ID
>
> ID FirstName RoleId SkillID
> 1 VictoriaGray 12 6
> 1 VictoriaGray 13 4
> 1 VictoriaGray 1 4
> 2 KateGriffiths 3 28
> 2 KateGriffiths 3 37
> 2 KateGriffiths 3 25
> 2 KateGriffiths 3 4
>
> When I loop through this recordset in my ASP page I get multiple
> records where as I only need to see the Id/Names of the people who
> have a specific skill or have a specific role experience.
>

What do you want? Just the list of names? If so, why are you including
the RoleID and SkillID in the resultset? If these are the correct names
that meet your data requirements, then simply remove the fields that
make the rows distinct and use the DISTINCT keyword:

SELECT DISTINCT
Contacts.ID, Contacts.FirstName, Contacts.LastName FROM ...

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Searching Multiple Tables

I do a search on these for example I want to find all names which have a
particular skill or a particular role.

I can't use a field name in where clause without it being in the list of
fields in the select, right?


"Bob Barrows [MVP]" wrote in message
news:uBoXLQkEHHA.4464@TK2MSFTNGP06.phx.gbl...
> JP SIngh wrote:
>> Bob
>>
>> The database is Access 2003 SP2.
>>
>> Also I have pasted the actual query below that I have created in
>> Access.
>>
>> SELECT Contacts.ID, Contacts.FirstName, Contacts.LastName,
>> RolesMatrix.Roles.Id AS RoleId, SkillsMatrix.KeySkills.Id AS SkillID
>> FROM (Contacts LEFT JOIN SkillsMatrix ON Contacts.ID =
>> SkillsMatrix.[Unique Id]) LEFT JOIN RolesMatrix ON Contacts.ID =
>> RolesMatrix.[Unique Id]
>> ORDER BY Contacts.ID
>>
>> ID FirstName RoleId SkillID
>> 1 VictoriaGray 12 6
>> 1 VictoriaGray 13 4
>> 1 VictoriaGray 1 4
>> 2 KateGriffiths 3 28
>> 2 KateGriffiths 3 37
>> 2 KateGriffiths 3 25
>> 2 KateGriffiths 3 4
>>
>> When I loop through this recordset in my ASP page I get multiple
>> records where as I only need to see the Id/Names of the people who
>> have a specific skill or have a specific role experience.
>>
>
> What do you want? Just the list of names? If so, why are you including
> the RoleID and SkillID in the resultset? If these are the correct names
> that meet your data requirements, then simply remove the fields that
> make the rows distinct and use the DISTINCT keyword:
>
> SELECT DISTINCT
> Contacts.ID, Contacts.FirstName, Contacts.LastName FROM ...
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

Re: Searching Multiple Tables

JP SIngh wrote:
> I do a search on these for example I want to find all names which
> have a particular skill or a particular role.
>
> I can't use a field name in where clause without it being in the list
> of fields in the select, right?
>
>
Try it. You'll see that there is nothing preventing it, at least with
this particular query.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.