help with select query

  • Thread starter Thread starter mary cox
  • Start date Start date
M

mary cox

Table: characters
charID int
name string
playerID int

Table: sightings
charID int
time timestamp


I listed the parts of my db that seem necessary to construct the queries
I'm trying to figure out. There are actually 3 of them:

1. generate a comparison of two specific characters, showing the number
of times that they have been sighted at the same time. I think I have this
one, but please let me know if this is a poor way to do it:

select count(*) from sightings s1, sightings s2
where (s1.charID = (select charID from characters where name = 'bob')) and
(s2.charID = (select charID from characters where name = 'jane'))
and (s1.time = s2.time)

2. generate a list of all characters that have NEVER been seen at the same
time as a specific character. The list should show the character names and
the total number of times each has ever been sighted, ordered by that count
of sightings (descending).

example:

bob is the specified character.
jane has never been seen at the same time as bob.
jane has been seen a total of 18 times.
jack has never been seen at the same time as bob.
jack has been seen a total of 7 times.
sue has been seen at least once at the same time as bob.
sue has been seen a total of 5 times.

output:
jane 18
jack 7

3. the same as number 2 except it's a list of all characters that have
never
been sighted at the same time as a specified character AND none of the other
characters with the same playerID have been seen at the same time as the
specified character either.

example:
same as number 2 except sue and jack have the same playerID

output:
jane 18


I'm running these queries on an access db so far, but may end up porting
to msde or mysql. So I'm hoping there's a generic way to write these
that'll
work on other engines.

Thanks in advance,
John Cox
 
John

Just a suggestion... using "time" as a field name will give both you and
Access headaches, as it is a "reserved word".

Try "TimeSighted" or some other such name.

Ditto for use of the term "name" (?CharacterName).

Good luck

Jeff Boyce
<Access MVP>
 
Thanks for the info. I will certainly make that correction.

Anybody else know if it's even possible to write queries I want?

Thanks again,
John Cox
 
I listed the parts of my db that seem necessary to construct the queries
I'm trying to figure out. There are actually 3 of them:

1. generate a comparison of two specific characters, showing the number
of times that they have been sighted at the same time. I think I have this
one, but please let me know if this is a poor way to do it:
Click to expand...
select count(*) from sightings s1, sightings s2
where (s1.charID = (select charID from characters where name = 'bob')) and
(s2.charID = (select charID from characters where name = 'jane'))
and (s1.time = s2.time)
Click to expand...

This will work if the timestamps are EXACTLY the same, to the
microsecond; if that's what you want to do you can actually pick up a
bit of efficiency by using a self-join query and joining in the
Characters table, rather than a Cartesian join. Be sure there is an
index on Time (and as noted elsethread, do consider changing the
fieldname):

select count(*)
from (sightings s1 INNER JOIN characters c1 ON c1.CharID = s1.CharID)
INNER JOIN
(sightings s2 INNER JOIN characters c2 ON c2.CharID = s2.CharID)
ON s1.[time] = s2.[time]
where c1.name = 'bob' c2.name = 'jane'

You may need to fiddle with the parentheses, this is untested air
code!
2. generate a list of all characters that have NEVER been seen at the same
time as a specific character. The list should show the character names and
the total number of times each has ever been sighted, ordered by that count
of sightings (descending).

example:

bob is the specified character.
jane has never been seen at the same time as bob.
jane has been seen a total of 18 times.
jack has never been seen at the same time as bob.
jack has been seen a total of 7 times.
sue has been seen at least once at the same time as bob.
sue has been seen a total of 5 times.

output:
jane 18
jack 7
Click to expand...

A NOT EXISTS query will work here:

SELECT c.name, Count(*)
FROM (sightings INNER JOIN characters c
ON c.CharID = sightings.CharID)
WHERE NOT EXISTS
(SELECT [time] FROM [sightings] s2 INNER JOIN characters c2
WHERE c2.[name] = "bob"
AND s2.[time] = s.[time]);

3. the same as number 2 except it's a list of all characters that have
never
been sighted at the same time as a specified character AND none of the other
characters with the same playerID have been seen at the same time as the
specified character either.
Click to expand...

Left as an exercise... you may need to base a query on a query !
 
A NOT EXISTS query will work here:
SELECT c.name, Count(*)
FROM (sightings INNER JOIN characters c
ON c.CharID = sightings.CharID)
WHERE NOT EXISTS
(SELECT [time] FROM [sightings] s2 INNER JOIN characters c2
WHERE c2.[name] = "bob"
AND s2.[time] = s.[time]);
Click to expand...

Thanks for the help. The first query worked great after I put an 'AND' in
between the two conditions on the last line. I'm having problems getting
this one to work though. I tried changing that last "s.[time]" to
"s1.[time]" and chaging the second line to "FROM (sightings s1 ..." but I
still get a syntax error. Is there something else I'm missing? I'm still
struggling to understand what this query does, so it's no wonder I'm having
problems debugging syntax. :)

Thanks again,
John Cox
 
I'm having problems getting
this one to work though.
Click to expand...

Well, given that I posted it with several errors that's not
surprising... <g>

Try

SELECT c.name, Count(*)
FROM sightings AS s INNER JOIN characters AS c
ON c.CharID = sightings.CharID
WHERE NOT EXISTS
(SELECT [time] FROM [sightings] AS s2 INNER JOIN characters AS c2
ON c2.CharID = s2.CharID
WHERE c2.[name] = "bob"
AND s2.[time] = s.[time])
GROUP BY c.name;

Basically, it's just a TOTALS query grouping by name and counting all
sightings for that name; the NOT EXISTS criterion excludes those
records where "bob" was sighted at the same time as the Sightings
record in the query.
 
Thanks again for all the help... sorry to be a pain but I still can't get
this query to work. :)

Access says "Syntax error in JOIN operation."

Thanks,
John Cox
 
Access says "Syntax error in JOIN operation."

Thanks,
John Cox
SELECT c.name, Count(*)
FROM sightings AS s INNER JOIN characters AS c
ON c.CharID = sightings.CharID
WHERE NOT EXISTS
(SELECT [time] FROM [sightings] AS s2 INNER JOIN characters AS c2
ON c2.CharID = s2.CharID
WHERE c2.[name] = "bob"
AND s2.[time] = s.[time])
GROUP BY c.name;
Click to expand...
Click to expand...

The first ON should probably be ON c.CharID = s.CharID

You might want to try creating the query and the subquery separately,
in the grid: just a simple inner join in each case, with a criterion
of "Bob". You can then copy and paste the SQL from the grid-created
query.
 
Ok.. I got the query to work without errors. The problem is it's not the
data I was after. :)

The query is generating a list of every character, and the total number of
times each character has been seen when the specified character was not
seen.

What I was trying to get was a list of only those characters that have NEVER
been seen at the same time as the specified character. For each of those
characters, list the total count of all the times I have ever seen them.

Target character = bob
sue has been seen at least once at the same time as bob.. so her count is
irrelevant.
jane has never been seen at the same time as bob. jane has been seen a
total of 10x.
jack has never been seen at the same time as bob. jack has been seen a
total of 30x.

output:
jack 30
jane 10

Thanks again :)

John Cox
 
Back
Top