Databases
StackOverflow2013
Postsdbo
POSQL query to show difference from the same table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL query to show difference from the same table
    primarykey
    data
    text
    <p>My application has a table that contains snapshot inventory data from each year. For example, there's a vehicle inventory table with the typical columns vehicle_id, vehicle_plate_num, vehicle_year, vehicle_make, etc, but also the year designating that the vehicle is owned.</p> <p>Querying the entire table might result in something like this:</p> <pre><code>Id Plate Num Year Make Model Color Year Owned --------------------------------------------------------- 1 AAA555 2008 Toyota Camry blue 2009 2 BBB666 2007 Honda Accord black 2009 3 CCC777 1995 Nissan Altima white 2009 4 AAA555 2008 Toyota Camry blue 2010 5 BBB666 2007 Honda Accord black 2010 6 DDD888 2010 Ford Explorer white 2010 </code></pre> <p>(Good or bad, this table already exists and it is not an option to redesign the table and that's a topic for another question). What you see here is year after year, the majority of the vehicles are still in the inventory, but there's always the situation where old ones are getting rid of, and new vehicles are acquired. In the example above, the 1995 Nissan Altima was in the 2009 inventory but no longer in the 2010 inventory. The 2010 inventory has a new 2010 Ford Explorer. </p> <p>How can I build an efficient query that takes any two years and show only the difference. For example, if I pass in 2009, 2010, the query should returns </p> <pre><code>3 CCC777 1995 Nissan Altima white 2009 </code></pre> <p>If I pass in 2010, 2009, the query should return </p> <pre><code>6 DDD888 2010 Ford Explorer white 2010 </code></pre> <p>Edit: I should have added the comment following the answer from Kyle B., but the text area for comment is not very user-friendly:</p> <p>I didn't think it would be this tough, but seems to be.</p> <p>Anyway, wouldn't you need a sub-select from the above like this:</p> <pre><code>select q.* from ( select f.* from inventory f left join inventory s on (f.plate_num = s.plate_num and f.year_owned = :first-year and s.year_owned = :second-year) where s.plate_num is null ) q where q.year_owned = :second_year </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UScathat
    1. UScathat
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL query to show difference from the same table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSQL query to show difference from the same table
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL query to show difference from the same table
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload