Clueless - Need SQL query/syntax help

Dev Shed Forums Sponsor:

Dell PowerEdge Servers

May 17th, 2004, 08:38 PM

jivman

Registered User

Join Date: May 2004

Posts: 10

Time spent in forums: 4 m 7 sec
Reputation Power: 0

Clueless - Need SQL query/syntax help

I ran into a scenario where I need some SQL direction. Here it is..

I have a database of users, that enter/re-enter their information periodically in order to be in a monthly drawing. Since we collect the new entry each and every time, I need to figure out a way to query these user records and export results based on their most recent entry.

To get right down to it, I need to find out all the 'no' answers for whether they have visited this company - and it has to be the most recent answer. If the user has only one entry in the database, then I only have to look at that one record. If the user has multiple entries, then I need to find their most recent entry and use information from this record. As logic would have it in this case, no's can become yes's over time (as to whether they've visited the organization or not), but yes's can't become no's.

As such, the user is able to indicate whether they've visited the organization by checking a 'yes/no' check box (let's call this field 'have_visited' to make it easy) via an online form. In some cases, the user checked 'No' some time ago (indicating they haven't visited the organization) - and later on they checked 'Yes' - that they have visited the organization.

So, I think the pertinent records here are 'ID' (primary key), 'FirstName', 'LastName', 'email', 'entered' (date in which they entered their info), and 'have_visited'.

Right now, all I have is:

...................................
Select * FROM guests
WHERE have_visited = 'no'
...................................

This obviously doesn't help me much as it only lists no's and doesn't take into account they they may have a more recent entry with a yes. How do I go about getting all the 'nos', but eliminating those that have entered 'yes' at any time? Also, since they enter their info multiple times in some cases, I want to only return one record for each email address.

I appreciate any help you can give.

May 17th, 2004, 10:20 PM

r937

SQL Consultant

Dev Shed God 24th Plane (16500 - 16999 posts)

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,744

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 33 m 16 sec
Reputation Power: 870

you will have to be a little more precise

i saw "yes" and "no" and "most recent" and "but yes's can't become no's"

so which row do you want?

if it's just the most recent, regardless of whether it's yes or no, then that's easy

__________________
r937.com | rudy.ca

May 17th, 2004, 11:02 PM

jivman

Registered User

Join Date: May 2004

Posts: 10

Time spent in forums: 4 m 7 sec
Reputation Power: 0

Need most recent

I would like to get the most recent entry, but I don't need it if it is a 'yes' answer - as I'm only looking to output a list of users that have selected 'No'. This is keeping in mind that there are users that have multiple entries. So, if, at any time, a user has selected 'Yes', then I want to Exclude them from the final output.

This is why I mentioned that a user that has selected 'no' at one point in time may have later selected 'yes' - which would then exclude them from the final output. For a user that has filled out the form, say 5 times, and selected 'No' each time - I want to make sure that only their last entry is output.

Let me know if this help clears it up. Thanks for the reply.

May 17th, 2004, 11:20 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,744

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 33 m 16 sec
Reputation Power: 870

you basically you want the latest?

May 18th, 2004, 11:53 AM

jivman

Registered User

Join Date: May 2004

Posts: 10

Time spent in forums: 4 m 7 sec
Reputation Power: 0

latest question..

i may be missing something here. if so, i apologize in advance, but...

i want the latest answer ONLY if the user answered 'no' for 'have_visited'. i don't want to return a record that has a 'yes' answer - even if it is their latest answer. however, if i could get to the point where i have the latest answer for all individuals, i would then want to query it for 'no' answers and this would give me what i'm looking for.

the reason being is that i need to export this result set as a contact list...and i only want to contact those that said 'no' to having visited the organization.

thanks again for your attention.

May 19th, 2004, 01:02 AM

Username=NULL

Contributing User

Join Date: Apr 2004

Location: TX

Posts: 249

Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5

assuming this is the relevent table architecture....

Code:

Guests
-----
ID [PK]
FirstName
LastName
Email
Entered //date entered
Have_Visited //'yes' or 'no'

...and you want the most recent (latest) call of all guests where the have_visited = 'no'...

Code:

select	ID, lastname, firstname, Email, Entered
from	Guests G
join	(select	ID, max(Entered) as DateEntered
	 from	Guests
         group  by ID) as LastEntry
on	G.ID = LastEntry.ID and G.Entered = LastEntry.DateEntered
where	have_visited = 'no'

...the inner query gets the latest answer date for each ID in the Guests table...joins back and then filters where that latest call is 'no'...give that a shot and let me know.

Last edited by Username=NULL : May 19th, 2004 at 01:07 AM.

May 19th, 2004, 12:28 PM

jivman

Registered User

Join Date: May 2004

Posts: 10

Time spent in forums: 4 m 7 sec
Reputation Power: 0

I received the following error:

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC Microsoft Access Driver] Syntax error in FROM clause.

The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (20:1) to (20:115).

Note the exact code that I used below. The only thing I changed from your code was the table being used (winnner) and I added 'have_played_bridges' to the Select statement.

Code:

<cfquery name="noplay" datasource="#application.dsn#" username="#application.login#" password="#application.pass#">
select	ID, lastname, firstname, Email, Entered, have_played_bridges
from	winner G
join	(select	ID, max(Entered) as DateEntered
	 from	winner
         group  by ID) as LastEntry
on	G.ID = LastEntry.ID and G.Entered = LastEntry.DateEntered
where	have_played_bridges = 'no'
</cfquery>

Any ideas. Thanks for responding...

May 19th, 2004, 12:45 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,744

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 33 m 16 sec
Reputation Power: 870

the keyword INNER is not optional in Access

but that's not my buddy null's fault, he gave you SQL Server syntax, most likely assuming, you know, that this is what you wanted, since you posted in the SQL Server forum...

May 19th, 2004, 01:00 PM

jivman

Registered User

Join Date: May 2004

Posts: 10

Time spent in forums: 4 m 7 sec
Reputation Power: 0

joining

thanks for the input rudy.

nevertheless, i tried the same code using an inner join (which works in access), but it throws the following error:

The specified field 'ID' could refer to more than one table listed in teh FROM clause of your SQL statement.

#10

May 19th, 2004, 02:59 PM

jivman

Registered User

Join Date: May 2004

Posts: 10

Time spent in forums: 4 m 7 sec
Reputation Power: 0

sql try

Ok, I migrated everything from access over to sql and ran the following query:

Code:

SELECT     G.ID, G.LastName, G.FirstName, G.Email, G.Entered, G.Have_Played_Bridges
FROM         winner G INNER JOIN
                          (SELECT     ID, MAX(Entered) AS DateEntered
                            FROM          winner
                            GROUP BY ID) LastEntry ON G.ID = LastEntry.ID AND G.Entered = LastEntry.DateEntered
WHERE     (G.Have_Played_Bridges = 'no')

It returns all instances where a person has entered no, lists them multiple times and doesn't exclude them if they have entered 'yes' at any point. It seems that we're on the right track, but it isn't quite there yet.

#11

May 19th, 2004, 04:48 PM

Username=NULL

Contributing User

Join Date: Apr 2004

Location: TX

Posts: 249

Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5

ahhh, ok. First off, yes, I did give you an error, sorry about that...the ambiguous "ID"...looks like you fig'd that out though. Second, I should be alot better about not limiting my responses to SQL Server syntax.

...but now I'm confused, tell me which of these is what you want...

1) You want the guests that ONLY have answered 'NO' on every submission

2) You want guests that answered 'NO' ONLY on their last submission.

About the previous query returning multiple rows for each guest...is the data exactly the same in each row for each Guest ID?? (mainly I think all the 'Entered' fields should have the same date)...if so try a DISTINCT in your Select, see if that helps.

Last edited by Username=NULL : May 19th, 2004 at 04:56 PM.

#12

May 19th, 2004, 05:00 PM

Username=NULL

Contributing User

Join Date: Apr 2004

Location: TX

Posts: 249

Time spent in forums: 1 Day 7 h 6 m 42 sec
Reputation Power: 5

If it's #1 give this a shot...

Code:

select	DISTINCT
	ID,
	Firstname,
	lastname,
	email,
	Entered
from	Guests G
inner	join	
	(
	 select	ID as A_ID,
	 	count(have_visited) as NumDiffAnswers,
		max(Entered) as DateEntered
	 from	Guests
	 group	by ID
	 having	count(have_visited) = 1
	) as A
on 	G.ID = A.A_ID and G.Entered = A.DateEntered
where	Have_Visited = 'no'

Last edited by Username=NULL : May 19th, 2004 at 05:04 PM.

#13

May 19th, 2004, 05:37 PM

jivman

Registered User

Join Date: May 2004

Posts: 10

Time spent in forums: 4 m 7 sec
Reputation Power: 0

query woes

Mr. Null

Thanks again for the input. You asked to indicate which of the following I was looking for.....

1) You want the guests that ONLY have answered 'NO' on every submission

2) You want guests that answered 'NO' ONLY on their last submission.

.... and what I am looking for is this.

3) I want guests that answered 'NO' and have Never answered 'Yes'. (answering yes at any time will exclude them from the result set)

I gave your query below a shot and it returned a list of those that answered no, but it doesn't remove those that have answered 'yes' at any point in time. It also repeats users and shows all the records where they entered 'no' to have_visited.

As an example, a user may have the following answers. In this case, I would Not want this user in the result set as they answered yes on 22 Apr 04 - despite the fact that they said 'no' every time before.

entered................name.................have visited

10-feb-02............john smith............no
12-apr-03............john smith............no
04-july-03............john smith............no
25-sep-03............john smith............no
05-jan-04............john smith............no
22-apr-04............john smith............yes

And for this user, I would want only her last record (22-apr-04) returned.

entered................name.................have visited

10-feb-02............cindy who............no
12-apr-03............cindy who............no
04-july-03............cindy who............no
25-sep-03............cindy who............no
05-jan-04............cindy who............no
22-apr-04............cindy who............no

And for this user, I'd like this row in the result set.

entered................name.................have visited

12-Jan-03............Bilbo Baggins............no

And for this user, I do not want this in the result set.

entered................name.................have visited

12-Jan-03............Bilbo Baggins............no

You also asked...:
About the previous query returning multiple rows for each guest...is the data exactly the same in each row for each Guest ID?? (mainly I think all the 'Entered' fields should have the same date)...if so try a DISTINCT in your Select, see if that helps.

Unfortunately, with the way the application was set up, the guest gets a new ID each time they enter their information. (I know, bad programming). Also, sometimes their information changes over time (ie. email address entered in '02 may have changed in '04). As such, the guest's ID is, for the most part, their email address. But since information changes occasionally (ie. Firstname - Jim...later input as Jimmy) the Distinct statement doesn't work very well.

Nevertheless, as long as I can output a list of users that have NEVER said yes, even if it lists mulitples entries in the result set, I could work with this.

Does this help. I don't think I've done a very good job explaining this up to this point.

Jim

#14

May 19th, 2004, 05:57 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 16,744

Time spent in forums: 1 Month 3 Weeks 2 Days 21 h 33 m 16 sec
Reputation Power: 870

Quote:

Originall