| View previous topic :: View next topic |
| Author |
Message |
Xildjian
Joined: 08 Jan 2005
Posts: 100
|
 Posted: Wed Nov 05, 2008 2:27 Post subject: |
 |
|
Original sequence (up to 20):
| Code: | | 1, 2, 4, 5, 7, 8, 9, 10, 11, 12, 13, 14, 16, 17, 18, 19, 20 |
I ran this query to understand how the tables are joined. This doesn't seem to be as you would expect.
| Code: | select t1.CharacterID,t2.CharacterID
from table_character t1 left outer join table_character t2
on t1.CharacterID = t2.CharacterID -1 |
Produces this resultant table (up to 20):
| Code: |
T1 T2
1 2
2 NULL
4 5
5 NULL
7 8
8 9
9 10
10 11
11 12
12 13
13 14
14 NULL
16 17
17 18
18 19
19 20 |
This query:
| Code: | select t1.CharacterID - 1 as 'missing'
from table_character t1 left outer join table_character t2
on t1.CharacterID = t2.CharacterID -1
where t1.CharacterID > 1 and t2.CharacterID is null |
produces this result:
This Query:
| Code: | select t1.CharacterID + 1 as 'missing'
from table_character t1 left outer join table_character t2
on t1.CharacterID = t2.CharacterID -1
where t1.CharacterID > 1 and t2.CharacterID is null |
Produces this result:
Not trying to argue just trying to understand the discrepancy between what you say I should be seeing and what I'm actually seeing because the CharacterID + 1 is what's producing expected output. I very much appreciate your help.
_________________
Member Shadow of Iniquity development team |
|
| Back to top |
|
 |
Gryphyn
Joined: 20 Jan 2005
Posts: 431
|
| Posted: Wed Nov 05, 2008 7:15 Post subject: |
|
|
Whoops... yep, you're right 
try it with
| Code: | | on t1.CharacterID -1 = t2.CharacterID |
when t1 = 2, t2 = (2-1) 1
when t1 = 4, t2 = (4-1) 3 (null) ==> select t1 - 1 (3)
as I said the sign was important (and what was throwing me)
t1 = t2-1 ==> t1+1 = t2 (which is why your select t1 + 1 works)
So yes, keep your version. possibly replace the on with
| Code: | | on t1.CharacterID +1 = t2.CharacterID |
just to make it consistent.
Clears it up for me as well.
Cheers
Gryphyn |
|
| Back to top |
|
|
Xildjian
Joined: 08 Jan 2005
Posts: 100
|
| Posted: Sun Nov 09, 2008 17:52 Post subject: |
|
|
Just a follow up:
How would I extend this to do the same thing off a sub query. Instead of an over all table ID, but an ID that was character or player based, and wanted the same functionality.
I thought it would be setup something like this:
| Code: | select t1.LocationID + 1 as 'missing'
from table_location t1 left outer join table_location t2
on t1.LocationID = t2.LocationID -1 IN (select LocationID from table_location where CharacterID=1)
where t1.LocationID > 1 and t2.LocationID is null |
But this isn't working. The assumption is that (select LocationID from table_location where CharacterID=1) would be a different column I just used one that I already had for testing.
Or maybe a better question is how does everyone setup / manage a persistent object table (SCO/RCO)?
Thanks!
_________________
Member Shadow of Iniquity development team |
|
| Back to top |
|
|
Xildjian
Joined: 08 Jan 2005
Posts: 100
|
| Posted: Mon Nov 10, 2008 1:58 Post subject: |
|
|
Looks like I figured it out this query appears to be doing what I want:
| Code: | select t1.LocationID + 1 as 'missing'
from (select LocationID from table_location where CharacterID=1) as t1 left outer join
(select LocationID from table_location where CharacterID=1) as t2
on t1.LocationID = t2.LocationID -1
where t1.LocationID > 1 and t2.LocationID is null |
_________________
Member Shadow of Iniquity development team |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
