You want a LEFT JOIN not a RIGHT JOIN (these are of course just lazy spellings
for LEFT OUTER JOIN and RIGHT OUTER JOIN respectively), assuming that you want
all selected rows from the table of the LEFT and only the matching values (else
NULL) for the table(s) on the right, which is how you describe what you want to
achieve. Simply change the word RIGHT to LEFT.
On the other hand, X RIGHT JOIN Y can be spelled as Y LEFT JOIN X (which may
be spelled with the word OUTER preceding the word JOIN) if for some reason you
really do want a right outer join (which of course means to include all rows
from the table on the RIGHT and either NULL or the matching values from the
table(s) on the LEFT.
To simulate a OUTER JOIN (which is an alternate spelling of FULL OUTER JOIN)
which includes all the rows from both sides with non-matching lookups set to
null is a wee bit (but not a lot) more complicated when all you can do is a
LEFT JOIN.
select EventID,
ContactInfo,
TicketID,
Priority,
PriorityText,
CreateDate
from Events
left join ColorScheme
on ColorScheme.PriorityLevel = Events.EventID
order by {SomeSoftwareDefinedOrder}
---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Stephen Chrzanowski
>Sent: Tuesday, 5 December, 2017 14:45
>To: General Discussion of SQLite Database
>Subject: [sqlite] Emulate right-join
>
>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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
