Re: Searching Multiple Tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

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.


.

Relevant Pages

  • Re: Something wrong with my INSERT INTO
    ... error but the data is not getting recorded in the database. ... You use of dynamic sql is leaving you vulnerable to hackers using sql ... Personally, I prefer using stored procedures, or saved parameter queries as ... Please reply to the newsgroup. ...
    (microsoft.public.scripting.vbscript)
  • Re: SQL Injection and DB user permissions
    ... since it contains quite a bit of 'dynamic sql' and I KNOW ... that employing a database user in the connection string with ... The only potential problem ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • can you help me find a Database App Developer MDX SSAS DotNet-Permanent-Miami, Fl?
    ... Minimum 2 years of experience in a multi-tier environment, including but not limited to integration of middleware, network management, information security, data architecture, database management. ... Outstanding analytical and quantitative skills. ... Proven ability, as evidenced by past work experience, to work well in both a team and as an individual ... Define, design, and implement, multi-tier object-oriented distributed software applications. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Shall I use database in this application?And how?
    ... storing it for use in your own program, ... So a lot depends on your skills. ... if it is, a database might help. ...
    (microsoft.public.windowsce.embedded.vc)
  • GetOleDbSchemaTable question
    ... I am trying to retrieve the list of tables in local oracle database, ... When I use the following set of commands ... objconnection = New OleDbConnection ... I get exactly the tables in the SKILLS schema. ...
    (microsoft.public.dotnet.framework.adonet)