I'm working on a pretty simplified event tracking system (So I stay out of
trouble of not updating time spent throughout the day), and each item that
I need to track has an associated priority with it, which ranges for any
positive integer value. This priority can be user defined as a positive
integer. (FWIW, zero is bottom of the pack, while 1 is top priority -- Zero
priority items are displayed at the bottom of the list, and I consider them
"Undefined")
Priorities 0 to 25 can have a color and title associated with it, and this
information is stored in its own table. Not all of the 26 priorities have
to be defined. The PriorityLevel is related to Priority (See below) but
has no direct requirement to exist.
So the question I have is how I can build the query to emulate a right-join?
The problem encountered is that if I have an event with a priority 99, and
with the priority text not being in the table, my query does not return
items with a priority 99.
The two tables goes something along the lines of:
Events
- EventID
- ContactInfo
- TicketID
- Resolved
- Priority (Integer)
- CreateDate
ColorScheme
- PriortyLevel (Integer)
- FGColor
- BGColor
- PriorityText
Notes:
There is no direct PK/FK relation between Priority and PriorityLevel.
The Priority is defaulted to zero when items are created. No trigger
exists to enforce a positive value, but my code protects that data info.
If the the Priority of an Event item is defined with a value that does not
exist in ColorScheme, color wise, it defaults to whatever PriorityLevel 0
is defined as.
The existing ColorScheme table is loaded into memory to toy with on a new
UI form, then on submit, the real table is dumped and repopulated.
If the user changes the Priority in the Events table, and there is no
PriorityLevel in ColorScheme that matches, the color scheme is
defaulted/assumed to be 0 in the application, only for coloring purposes,
but, retains the priority level.
On the submission of the color schemes, priority zero is force-created,
meaning that if the user deletes priority 0, the software will add a
default values to the ColorScheme table.
The query I'm attempting to run is something along the lines of: (Untested)
Select EventID,ContactInfo,TicketID,Priority,CreateDate from Events order
by {SomeSoftwareDefinedOrder}
What I want to do is essentially:
select EventID,ContactInfo,TicketID,Priority,PriorityText,CreateDate from
Events *RIGHT JOIN* ColorScheme on ColorScheme.PriorityLevel =
Events.EventID order by {SomeSoftwareDefinedOrder}
What this did in MSSQL2000 days, if I remember correctly, give me all
results in the Events table even if the relevant info isn't in ColorScheme
table. The PriorityLevel and PriorityText would be returned as NULL.
Does anyone have any working theories on how I can get ALL results in the
Events table regardless if the Events.Priority isn't in
ColorScheme.PriorityLevel?
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
