On Tue, Dec 5, 2017 at 3:44 PM, Stephen Chrzanowski <pontia...@gmail.com>

> 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?

​Why not just use a RIGHT JOIN? PostgreSQL supports it. On page



One of







For the INNER and OUTER join types, a join condition must be specified,
namely exactly one of NATURAL, ON *join_condition*, or USING (*join_column* [,
...]). See below for the meaning. For CROSS JOIN, none of these clauses can

A JOIN clause combines two FROM items, which for convenience we will refer
to as “tables”, though in reality they can be any type of FROM item. Use
parentheses if necessary to determine the order of nesting. In the absence
of parentheses, JOINs nest left-to-right. In any case JOIN binds more
tightly than the commas separating FROM-list items.

CROSS JOIN and INNER JOIN produce a simple Cartesian product, the same
result as you get from listing the two tables at the top level of FROM, but
restricted by the join condition (if any). CROSS JOIN is equivalent to INNER
JOIN ON (TRUE), that is, no rows are removed by qualification. These join
types are just a notational convenience, since they do nothing you couldn't
do with plain FROM and WHERE.

LEFT OUTER JOIN returns all rows in the qualified Cartesian product (i.e.,
all combined rows that pass its join condition), plus one copy of each row
in the left-hand table for which there was no right-hand row that passed
the join condition. This left-hand row is extended to the full width of the
joined table by inserting null values for the right-hand columns. Note that
only the JOIN clause's own condition is considered while deciding which
rows have matches. Outer conditions are applied afterwards.

Conversely, RIGHT OUTER JOIN returns all the joined rows, plus one row for
each unmatched right-hand row (extended with nulls on the left). This is
just a notational convenience, since you could convert it to a LEFT OUTER
JOIN by switching the left and right tables.

FULL OUTER JOIN returns all the joined rows, plus one row for each
unmatched left-hand row (extended with nulls on the right), plus one row
for each unmatched right-hand row (extended with nulls on the left).
ON *join_condition*


> 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

I have a theory that it's impossible to prove anything, but I can't prove

Maranatha! <><
John McKown
sqlite-users mailing list

Reply via email to