URGENT! SQL Query Question...Pls Help!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

URGENT! SQL Query Question...Pls Help!

Author

Topic

kwilliams

194 Posts

Posted - 2003-08-18 : 11:04:28

I'm trying to pull jobs from a database that the applicant has not already applied for. This is the SQL Query that I created that checks to make sure that the user is registered, and then checks which jobs they've applied for:

SELECT *
FROM dbo.Employment_App, dbo.Jobs
WHERE App_ID = 'varApp_ID' AND Password = 'varPassword' AND Employment_App.Seqno = MMColParam AND GetDate() BETWEEN Start_Date AND Deadline_Date AND Job_ID NOT IN (SELECT Job_ID FROM Employ_Jobs)
ORDER BY Job_Title ASC

The problems lies in the "Job_ID NOT IN (SELECT Job_ID FROM Employ_Jobs)" area. If another user has applied for that job, it won't display as being available for this user. But when I change the "NOT IN" to "IN", it will show all jobs are that are available, including jobs the user's already applied for, and jobs that other users have applied for.

How do I tie in which jobs are available for that specific applicant, that they haven't already applied for? I thought that's what my query did with using the APP_ID & Password check, but I guess not. All suggestions will be a big help! Thanks in advance.

KWilliams

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-08-18 : 11:12:43

If it's really urgent you can speed up getting a decent answer by providing a proper table structure. Some sample data (preferably as insert statements) make it even faster to help you.

Damian

kwilliams

194 Posts

Posted - 2003-08-18 : 11:29:11

Merkin,

I've included the INSERT statement at the end of this message, but the problems doesn't lie with the insert statement. That's working just fine. After the user selects one or more jobs, they get redirected to another page that actually does the insert using a loop.

My problems lies in the SELECT statement on the first page. The table for this page (dbo.Employ_Jobs) has the following columns: Seqno (auto#), Job_ID, App_ID, and Creation_Date. Each job that the user enters creates a new record. If another applicant's applied for a job, and I try to view & apply for that same job, it's not being displayed with the SELECT statement. As I stated previously, I don't want jobs to show up that the user's already applied for in the past, so that's why I added the "Job_ID NOT IN (SELECT Job_ID FROM Employ_Jobs)" wording.

I've also tried moving the Job_ID wording to 2nd SELECT statement away from the 1st that checks the users login info, like this:
SELECT *
FROM dbo.Jobs
WHERE GetDate() BETWEEN Start_Date AND Deadline_Date AND Job_ID NOT IN (SELECT Job_ID FROM Employ_Jobs) AND 'varAppID2' IN (SELECT App_ID FROM Employment_App)
ORDER BY Job_Title ASC
varAppID2 % Session("MM_Username")

...but it does the same thing. For some reason, it's not seeing the wording about checking for only this applicant (App_ID), and is checking for all applicants, even though I put the check into the SELECT statement. Any other suggestions?? Thanks.

KWilliams

INSERT STATEMENT:
<%
var MM_Insert=String(Request.Form("job"))
if (MM_Insert != "undefined")
{
var Insert_array = MM_Insert.split(",");
var loop = 0
while (loop < Insert_array.length)
{
var New_Insert=Insert_array[loop]
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = MM_strConn_STRING;
Command1.CommandText = "INSERT INTO dbo.Employ_Jobs (App_ID, Creation_Date, Job_ID) VALUES('"+ Command1__varApp_ID.replace(/'/g, "''") + "', '"+ Command1__varCreation_Date.replace(/'/g, "''") + "', '"+ New_Insert.replace(" ","") + "')";
Command1.CommandType = 1;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Execute();
//Response.Write(Command1.CommandText)Use to test if needed
Command1.Close;
loop++
}
}
else {%>

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-18 : 11:30:50

One thing I don't see is any JOIN condition between dbo.Employment_App and dbo.Jobs unless its "Employment_App.Seqno = MMColParam" please try to either alias tables or use the full naming of objects to help

assuming that Job_ID exists in dbo.Jobs then you could use

AND NOT EXISTS (SELECT * from Employ_Jobs where dbo.Jobs.Job_ID = Employ_Jobs.Job_ID) instead of AND Job_ID NOT IN...

kwilliams

194 Posts

Posted - 2003-08-18 : 11:34:30

Hi Merkin,

Thanks for the quick responses. I've tried adding "Join" into the DB table names in the past, but it results in several duplicate results for some reason. I was told from a SQL query book that if you entered a couple of DB tables with a comma, it would work as a join. And it has so far for me.

As for the query change suggestion, I will give it a try, and let you know what happens. Thanks.

KWilliams

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-18 : 11:41:14

The SQL book is correct it does work as a join, a Cartesian join, meaning every row in the one table is joined to every row on the other which is rarely desirable and can be costly for performance. And talk about duplicates in results...you have to have very extensive and very busy WHERE clauses to filter them out.

Take advantage of table structure and relations, use the join keys and build very powerful and quick queries.

kwilliams

194 Posts

Posted - 2003-08-18 : 11:44:29

...but whenever I've tried to put "Inner Join" or Join" into a query, it's given me a huge list of duplicate results.
SELECT *
FROM dbo.TableName1 INNER JOIN dbo.TableName2
WHERE Column1 = 'First_Name'

...This kind of setup results in me getting one record maybe 10 times.

KWilliams

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-18 : 12:03:43

quote:
SELECT *
FROM dbo.TableName1 INNER JOIN dbo.TableName2
WHERE Column1 = 'First_Name'

is no different than dbo.TableName1, dbo.TableName2 it is still a Cartesian join. The key is how dbo.TableName1 and dbo.TableName2 "relate" to eachother

SELECT *
FROM dbo.TableName1 INNER JOIN dbo.TableName2
	ON dbo.TableName1.RelatedField1 = dbo.TableName2.RelatedField1 -- key part of statement
WHERE Column1 = 'First_Name'

...where RelatedField1 is a key that exists in both tables that relate them to one another.

In your example, I would guess that dbo.Employment_App has a field like Job_ID which also exists in dbo.Jobs if so the code would be:


SELECT	*
FROM	dbo.Employment_App JOIN dbo.Jobs 
		ON dbo.Employment_App.Job_ID = dbo.Jobs.Job_ID
WHERE	App_ID = 'varApp_ID'
AND	Password = 'varPassword'
AND	Employment_App.Seqno = MMColParam
AND	GetDate() BETWEEN Start_Date AND Deadline_Date
AND NOT EXISTS 
	(
	SELECT	*
	FROM	Employ_Jobs 
	WHERE	dbo.Jobs.Job_ID = Employ_Jobs.Job_ID
	)
ORDER BY Job_Title ASC

kwilliams

194 Posts

Posted - 2003-08-18 : 12:25:48

Merkin,

Actually there are 3 DB tables: dbo.Employment_App, dbo.Employ_Jobs, and dbo.Jobs. This is the way it's set up with relationships:
1) dbo.Employment_App
Key: App_ID

2) dbo.Employ_Jobs (ties dbo.Employment_App & dbo.Jobs together)
Keys: App_ID & Job_ID

3) dbo.Jobs
Key: Job_ID

I'm working with the SELECT statement you provided with the join to see if I can get it to work with my setup. But if you can send me a similar SELECT statement that uses my setup, it would be greatly appreciated. I've really appreciated all of your help.

KWilliams

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-18 : 12:52:42

In order to better help you, we need to see the table structures and also some sample data. For the table structures, please provide CREATE TABLE statements. For sample data, please provide INSERT INTO statements to get data into those 3 tables so that we can test.

Also, why aren't you using stored procedures (just looking at the INSERT statement)?

Tara

kwilliams

194 Posts

Posted - 2003-08-18 : 13:03:35

Hi Tara,

This page only inserts into one table, dbo.Employ_Jobs. It just uses keys from the other 2 tables to relate the info (Job_ID & App_ID). But I'm confused about what you're asking for. I'm not having any problems at all with the Insert itself, only the displaying of what's in the dbo.Employ_Jobs table when using a SELECT statement for that specific user. That has nothing to do with the Insert statement. But here's the INSERT statement for dbo.Employ_Jobs (it uses a loop to allow for multiple entries):
<%
var MM_Insert=String(Request.Form("job"))
if (MM_Insert != "undefined")
{
var Insert_array = MM_Insert.split(",");
var loop = 0
while (loop < Insert_array.length)
{
var New_Insert=Insert_array[loop]
var Command1 = Server.CreateObject("ADODB.Command");
Command1.ActiveConnection = MM_strConn_STRING;
Command1.CommandText = "INSERT INTO dbo.Employ_Jobs (App_ID, Creation_Date, Job_ID) VALUES('"+ Command1__varApp_ID.replace(/'/g, "''") + "', '"+ Command1__varCreation_Date.replace(/'/g, "''") + "', '"+ New_Insert.replace(" ","") + "')";
Command1.CommandType = 1;
Command1.CommandTimeout = 0;
Command1.Prepared = true;
Command1.Execute();
//Response.Write(Command1.CommandText)Use to test if needed
Command1.Close;
loop++
}
}
else {%>

The dbo.Employ_Jobs table is structured this way (it's pretty simple):
Column Name Data Type Length
Seqno int 4 (auto#)
Job_ID char 6
App_ID char 20
Creation_Date char 30

As you can see from the Insert statement above, every time a user enters a job, it enters a new record for that transaction. I hope this helps.

KWilliams

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-08-18 : 13:07:51

You still haven't provided what we need. We don't need the insert statement from your code. We need the CREATE TABLE statements for your table and sample data which will need to be in the form of INSERT INTO statements.

Tara

kwilliams

194 Posts

Posted - 2003-08-18 : 14:05:45

drymchaser,

I tried your suggestion, but it gave me the exact same results as "NOT IN" did. Any other ideas? Thanks.

KWilliams

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-18 : 14:21:26

Here goes nuthin:

SELECT	*

FROM	dbo.Jobs LEFT JOIN
	(
	SELECT *
	FROM dbo.Employment_App JOIN dbo.Employ_Jobs 
		ON dbo.Employment_App.App_ID = dbo.Jobs.App_ID
	WHERE	App_ID = 'varApp_ID'
	AND	Password = 'varPassword'
	AND	Employment_App.Seqno = MMColParam
	AND	GetDate() BETWEEN Start_Date AND Deadline_Date
	) AppliedFor

ON	dbo.Jobs.Job_ID = AppliedFor.Job_ID

WHERE	AppliedFor.Job_ID is NULL

ORDER BY Job_Title ASC

kwilliams

194 Posts

Posted - 2003-08-18 : 15:28:20

drymchaser,

WOW! You must have spent some time on that one. I tried it, but it just kept giving me error messages. I'm thinking that the query should be a bit more simple. I just think that I'm not adding in the check for the App_ID in properly to the query for dbo.Employ_Jobs. I'd like to try to check it against the Session("MM_Username"), which is just the session var for the App_ID. But when I add it into the query, it doesn't work. Maybe you can have a look:

SELECT *
FROM dbo.Jobs JOIN dbo.Employ_Jobs ON Jobs.Job_ID = Employ_Jobs.Job_ID
WHERE Employ_Jobs.App_ID = 'varAppID' AND GetDate() BETWEEN Start_Date AND Deadline_Date AND NOT Jobs.Job_ID = Employ_Jobs.Job_ID
ORDER BY Job_Title ASC

varAppID % Session("MM_Username")

Thanks for your suggestions drymchaser.

KWilliams

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-18 : 15:43:46

What if you put actual values in the Where clause

WHERE App_ID = <actual value> etc...

does the query run and are the results as expected if not copy and paste the SQL error message

kwilliams

194 Posts

Posted - 2003-08-18 : 15:45:38

Well, I've mae some good progress. The SELECT statement below pulls up both sample jobs as it should, but it pulls up 5 copies of each record. I've tried changing the "AND NOT Jobs.Job_ID.." part to "AND NOT EXISTS(SELECT..." like you had mentioned earlier, and changing the beginning to an INNER & LEFT JOIN, but no records come up when I do this. Can you see from my statement below why it results in multiple copies of the 2 records?

Ex:
02-001
02-001
02-001
02-001
02-002
02-002
02-002
02-002

...when it should be:
02-001
02-002

Here's the SELECT statement that pulls up the correct records, but with duplicates:

SELECT *
FROM dbo.Jobs, dbo.Employ_Jobs
WHERE App_ID IN (SELECT App_ID FROM dbo.Employment_App) AND GetDate() BETWEEN Start_Date AND Deadline_Date AND NOT Jobs.Job_ID = Employ_Jobs.Job_ID
ORDER BY Job_Title ASC

KWilliams

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-18 : 15:50:13

SELECT DISTINCT * -- You may have to list out column names, but only the ones needed
FROM dbo.Jobs, dbo.Employ_Jobs
WHERE App_ID IN (SELECT App_ID FROM dbo.Employment_App) AND GetDate() BETWEEN Start_Date AND Deadline_Date AND NOT Jobs.Job_ID = Employ_Jobs.Job_ID
ORDER BY Job_Title ASC

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-18 : 15:52:52

BTW you may want to discuss with the DBA about getting a data diagram which has tables/fields/datatype info with relations mapped it could be very useful

kwilliams

194 Posts

Posted - 2003-08-18 : 16:03:11

drymchaser,

I agree about the data diaram...and I'll definitely do that. About specifying the column names, I totally forgot that you have to do that when using this type of join. I've gottem it to work athat way in the past. So I did it...see:
SELECT App_ID, Start_Date, Deadline_Date, Jobs.Job_ID, Employ_Jobs.Job_ID, Job_Title
FROM dbo.Jobs, dbo.Employ_Jobs
WHERE App_ID IN (SELECT App_ID FROM dbo.Employment_App) AND GetDate() BETWEEN Start_Date AND Deadline_Date AND NOT Jobs.Job_ID = Employ_Jobs.Job_ID
ORDER BY Job_Title ASC

But it still shows duplicate Jobs for some reason. I'm not sure why, but Ill keep messing with it. If you see something that's obvious, let me know. Thanks again for all of your help.

KWilliams

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-18 : 16:18:10

did you try DISTINCT

SELECT DISTINCT App_ID, Start_Date, Deadline_Date, Jobs.Job_ID, Employ_Jobs.Job_ID, Job_Title

run this and paste results then we may be able dissect where the duplication is.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources