//AMIS Technology Blog » outer join

Posts tagged outer join

image.png

Oracle SQL – spotting combinations that occur and those that do not – demonstrating Analytical Functions, Outer Join and SubQuery Factoring

0
2 months ago
by Lucas Jellema in ,

The challenge in this article is a simple one. I have a table with records (duh!) and each record describes the occurrence of a certain payload. In this example the payload will be a color. Every record also has a sequence number to indicate well, when it appeared relative to the other observations. A subset of the data would look like this:

The challenges I will discuss in this article are simple:

  • which sequences of three color observations occur in the data set
  • how often do these sequences of three occur (give me the top 3 sequences)
  • given the colors that have been observed, which combinations of three can be created
  • which possible combinations (or color sequences of three colors) have not been observed at all

Using Oracle SQL features such as Analytical Functions, Outer Join and Subquery Factoring – these questions become very easy to answer.

(more…)

SIG Event

Weird ADF 11g requirement addressed with left outer join and modern SQL join syntax

0
1 year ago
by Lucas Jellema in , , ,

The functional requirement was a little unusual. The page should either show all master-records or – depending on the value of a parameter – it should show exactly one master-record joined with exactly one detail-record. The use case was valid – that was exactly the functionality that was required.

In terms of EMP and DEPT -I like to always simplify things to express them in EMP and DEPT terms – , we should either see all Departments (and no Employee data) or we should see the data for a specific Employee joined with the data for its corresponding Department. And of course we just one to create a single page, and make it as simple as possible to create that page.

A simplistic page that supports this functionality could like this:

when a specific Employee is requested and

when all Departments should be shown, because no single Employee is asked for.

(more…)

Vacatures bij AMIS services

Anti-Search patterns – SQL to look for what is NOT there – Part Two

2
2 years ago
by Lucas Jellema in , ,

It is a strange thing really: querying the database to find missing data. Queries usually report on what is there. Not the queries in this article (and its prequel): they report on what is not there. We explore a number of approaches to uncovering missing information – because the fact that something is not found in itself is a fact we may want to know about. And it gives me a nice motive to demonstrate a number of fun SQL features.

We are still very much engaged in the EMP and DEPT tables in the SCOTT schema that back in the early ’80s was pivotal in demonstrating the capabilities of the first Oracle RDBMS releases. It still does an excellent job for most of my demos today. Must be me…

This article delves into urgent matters such as ‘which letters do not occur in the names of the employees’, ‘in which months of the year was no one hired’, ‘who in each department does not have a colleague with less experience (or a lower salary)’, ‘which employees do not have a colleague in the same job (either in their own department or in the entire company)’, and more.

(more…)

Go to Top