Re: Is this a JOIN situation?

On 3/14/2016 2:59 PM, Robert Greschke wrote:
> (Wasn't there a comp.lang.sql at one time?)
>
> I've been writing Python programs that construct SQL commands to feed to
> MySQL/MariaDB for years, but now I'd like to see if I can do this with
> one command, instead of one, a little Python, and a bunch of queries.
>
> table inventory
> barcode
> kingdom
> phylum
> class
> make
> model
> style
>
> table maint
> barcode
> date
> entry
>
> There's one entry per barcode in the inventory table, but as many as it
> takes entries per barcode in maint as it takes to document all of the
> times an item has been worked on.
>
> select inz.barcode, inz.kingdom, inz.phylum, inz.class, inz.make,
> inz.model, inz.style, mdz.date, mdz.entry inventory as inz, maint as mdz
> where inz.barcode=mdz.barcode order by inz.kingdom, inz.phylum,
> inz.class, inz.make, inz.model, inz.style, mdz.date;
>
> That works fine until I want the returned maint entries sorted by
> kingdom->style, but in reverse date order (desc). Just adding desc to
> the end gets kingdom->style also sorted in reverse order (like
> anti-alphabetical).
>
> There's more to the selecting, like just the inz.phylum items that equal
> "SENSORS" and stuff like that that I don't have here, but this is the
> idea. The search can also be done where I just want maint entries made
> on or between certain dates, but I can make that all work fine when I
> just want the order of everything to be ascending.
>
> I can do it in Python by getting the initial distinct list of inventory
> barcodes and then running a loop to get the maint entries for those
> barcodes in whichever order I want (this is all for eventually printing
> out the maint entries), but can it be done in just SQL? I've messed with
> the GROUP BY clause, but that didn't seem to work. I think I've hammered
> out two JOINs in my life, but I'm not even sure I understand what I did
> there. :)
>
> Thanks!
>
> Bob
>

I'm not sure exactly what you're looking for. For questions like this, it's much better to show some sample data and the output you would like.

One thing to remember - when you ORDER BY multiple columns, some can be in ASC order and some in DESC order, i.e.

  ORDER BY a, b ASC, c, d DESC

Where a, b, c and d are column names. Not all columns need to be in the same order.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================