Page 2 - Clueless - Need SQL query/syntax help

Dev Shed Forums Sponsor:

#16

May 19th, 2004, 06:35 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,654

Time spent in forums: 1 Month 3 Weeks 6 Days 12 h 49 m 28 sec
Reputation Power: 981

no idea why it's happening

perhaps priscilla is in there more than once with a different name or email

here's my test:

Code:

create table jivaldi
( id smallint not null identity
, entered datetime
, name  varchar(33)
, email varchar(33)
, have_visited  char(3)
)
insert into jivaldi (entered, name, email, have_visited)
values ('04-Mar-03','Woo, Priscilla','Prisawoo1@aol.com','No')
insert into jivaldi (entered, name, email, have_visited)
values ('30-May-03','Woo, Priscilla','Prisawoo1@aol.com','No')
insert into jivaldi (entered, name, email, have_visited)
values ('03-Jun-03','Woo, Priscilla','Prisawoo1@aol.com','No')
insert into jivaldi (entered, name, email, have_visited)
values ('15-Jun-03','Woo, Priscilla','Prisawoo1@aol.com','No')
insert into jivaldi (entered, name, email, have_visited)
values ('17-Jun-03','Woo, Priscilla','Prisawoo1@aol.com','No')
insert into jivaldi (entered, name, email, have_visited)
values ('09-Sep-03','Woo, Priscilla','Prisawoo1@aol.com','No')
insert into jivaldi (entered, name, email, have_visited)
values ('10-Oct-03','Woo, Priscilla','Prisawoo1@aol.com','No')


select * from jivaldi

1  2003-03-04 00:00:00.000  Woo, Priscilla  Prisawoo1@aol.com  No 
2  2003-05-30 00:00:00.000  Woo, Priscilla  Prisawoo1@aol.com  No 
3  2003-06-03 00:00:00.000  Woo, Priscilla  Prisawoo1@aol.com  No 
4  2003-06-15 00:00:00.000  Woo, Priscilla  Prisawoo1@aol.com  No 
5  2003-06-17 00:00:00.000  Woo, Priscilla  Prisawoo1@aol.com  No 
6  2003-09-09 00:00:00.000  Woo, Priscilla  Prisawoo1@aol.com  No 
7  2003-10-10 00:00:00.000  Woo, Priscilla  Prisawoo1@aol.com  Yes

select name
     , email
     , max(have_visited) as max
  from jivaldi
group
    by name
     , email

Woo, Priscilla  Prisawoo1@aol.com  Yes

see? it works!!

__________________
r937.com | rudy.ca

#17

May 19th, 2004, 08:12 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

Oh, more light! So each record has it's own ID??...so the same lastname and firstname can have many ID's?? Give this a try....inner query finds distinct Lastname and Firstname that only have answered the same answer for Have_Visited....joins back and then filters where the have_visited = 'no'. I saw earlier that you said you "migrated everything from Access over to SQL"...I assume you meant over to SQL Server, try this...

Code:

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

Last edited by Username=NULL : May 19th, 2004 at 08:24 PM.

#18

May 19th, 2004, 08:18 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

Quote:

Originally Posted by jivaldi

Rudy, thanks for the reply, but this query didn't exclude those that had entered 'yes' at any given time.

Any idea why this might be happening?

From what I can tell, and I may be off, Rudy's query is only pulling in the max(have_visited) value, this isn't enough to eliminate distinct names that have entered 'yes' @ some point in time...though, in the midst of all the requirements confusion, I think he's either misunderstanding or overlooking this aspect maybe?

Last edited by Username=NULL : May 19th, 2004 at 08:23 PM.

#19

May 19th, 2004, 10:23 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,654

Time spent in forums: 1 Month 3 Weeks 6 Days 12 h 49 m 28 sec
Reputation Power: 981

me? misunderstand something in this thread?

hard to fathom why

look, disregard the business of the dupe names for a moment, and just consider GROUP BY something (doesn't matter what you group by)

MAX(have_entered) will be 'No' only if there is no 'Yes' in the group

if there's even one 'Yes' in the group, no go

the sql i gave is perfect

it's the data that's bad

#20

May 19th, 2004, 10:27 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,654

Time spent in forums: 1 Month 3 Weeks 6 Days 12 h 49 m 28 sec
Reputation Power: 981

p.s. in your subquery, null, your use of DISTINCT is redundant

when using GROUP BY, those groups are distinct, by definition

:Cool:

#21

May 19th, 2004, 10:47 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

Edited...I see what you're saying now...I saw that your query was returning a 'yes' from your test data, that's how the max(have_visited) is gonna work though, and of course previously you filtered on having 'no' ...confused on what you meant by "it's working". My fault.

Last edited by Username=NULL : May 19th, 2004 at 11:14 PM.

#22

May 19th, 2004, 11:24 PM

brightlight

Contributing User

Join Date: Apr 2004

Posts: 371

Time spent in forums: 19 h 32 m 27 sec
Reputation Power: 5

Hi Jivaldi,

I use the previously mentioned table as an example and the following are the data.

Quote:

1 04-Mar-03 Woo, Priscilla Prisawoo1@aol.com No
2 30-May-03 Woo, Priscilla Prisawoo1@aol.com No
3 03-Jun-03 Woo, Priscilla Prisawoo1@aol.com No
4 15-Jun-03 Woo, Priscilla Prisawoo1@aol.com No
5 17-Jun-03 Woo, Priscilla Prisawoo1@aol.com No
6 09-Sep-03 Woo, Priscilla Prisawoo1@aol.com No
7 10-Oct-03 Woo, Priscilla Prisawoo1@aol.com No
8 10-Dec-03 jane jane@hello.com No
9 11-Jan-04 jane jane@hello.com No
10 11-May-04 Woo, Priscilla Prisawoo1@aol.com Yes
11 10-Apr-03 john john@jupe.com No
12 13-May-04 jane jane@hello.com No

My SQL stmt is as follows (I'm not sure whether this will work in Access. Can give a try)

Quote:

SELECT x.*
FROM jivaldi x
WHERE (entered =
(SELECT MAX(entered)
FROM jivaldi y
WHERE y.name = x.name)) AND (have_visited = 'No')

The result will be as follows

Quote:

11 10-Apr-03 john john@jupe.com No
12 13-May-04 jane jane@hello.com No

I think this is the result you want to have.

__________________
Regards,

Brightlight

#23

May 20th, 2004, 12:33 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

BrightLight, change the "yes" to "no" for this record...

Code:

10 11-May-04 Woo, Priscilla Prisawoo1@aol.com Yes

..and change the "no" to "yes" for this record...

Code:

1 04-Mar-03 Woo, Priscilla Prisawoo1@aol.com No

...and see what you get. I'm curious as to how your query is working.

#24

May 20th, 2004, 01:06 AM

brightlight

Contributing User

Join Date: Apr 2004

Posts: 371

Time spent in forums: 19 h 32 m 27 sec
Reputation Power: 5

Hi Null,

When I changed as u said, I get the following result

Quote:

10 11-May-04 Woo, Priscilla Prisawoo1@aol.com No
11 10-Apr-03 john john@jupe.com No
12 13-May-04 jane jane@hello.com No

From the first post of Jivaldi I think once have_visited becomes "Yes", it can never change to "No". So there may not be a chance for the scenario as you have mentioned (i.e Yes changing to No). Assuming the fact and when data entered are correct then my SQL can work correctly

Last edited by brightlight : May 20th, 2004 at 01:12 AM.

#25

May 20th, 2004, 02:05 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

Right, your query has good reason, but the "yes's can not become no's" logic is dependent on the user entry, it's not a 100% gaurantee. Say the user marks "yes" for a given company. They come back to the site 6 months from now and forgot they visited the company, this time they mark no...you're query wouldn't catch that scenario. Also, let's say a small name in the company changes that globally only affects nothing more than the name @ the db level, ID and related records aren't effected. The user visits the site again and doesn't recognize the company name (all though the company is still exactly the same, except for the name) and marks "no"....this scenario would also squeak by. How likely is it this will happen??...who knows, but it's a small diff in time and the code to ensure cases like this are caught.

Btw, rudy, I wasn't attacking you or your intelligence, sorry if it came across that way...for obvious reasons though, I think we both know that would be pretty silly. Regardless of skill, experience, etc...it's possible for anyone to overlook something, which definitely holds true in this case w/a bit of irony involved as I originally thought you were the one overlooking something.

Last edited by Username=NULL : May 20th, 2004 at 02:20 AM.

#26

May 20th, 2004, 02:17 AM

brightlight

Contributing User

Join Date: Apr 2004

Posts: 371

Time spent in forums: 19 h 32 m 27 sec
Reputation Power: 5

Hi Null,

Do you agree with my SQL stmt or not?

Quote:

SELECT x.*
FROM jivaldi x
WHERE (entered =
(SELECT MAX(entered)
FROM jivaldi y
WHERE y.name = x.name)) AND (have_visited = 'No')

#27

May 20th, 2004, 02:22 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

yes, and no

(I'm far from an authority though). I must've been editing while you were typing, read my reply above. Nothing personal in the difference of attack on this one, just stating what I would do. I think looking @ the entire group of answers for a given name is a more solid approach, relying on latest submission based on "yes's can not become no's" type logic leaves cracks for records to get by. Just me...oh, and yes...I may be thinking on extremes, but you never know.

Last edited by Username=NULL : May 20th, 2004 at 03:51 AM.

#28

May 20th, 2004, 12:50 PM

jivman

Registered User

Join Date: May 2004

Posts: 10

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

All is well in the world again.

Hello,

First off, I want to thank all of you for putting your time into this. (Null, Rudy & Brightlight) I've put a lot of effort into this one, but learned a lot in the process. Thank you for your patience with someone who is much lower on the knowledge scale (me).

Brightlight - it looks like you nailed it. Null, you are correct that this methodology leaves a crack for those individuals who may have accidentally answered 'Yes' at some point - and then gone back to 'No' for there last entry...but...for what I'm looking for right now this works well.

thanks again

jim ivaldi

#29

May 20th, 2004, 02:07 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

cool, glad we could all lend in. Good luck.