Re: Is this a JOIN situation?
Date: Mon, 14 Mar 2016 14:43:11 -0600
Message-ID: <2016031414431159527-_at_news.nmt.edu>
On 2016-03-14 19:48:07 +0000, Jerry Stuckle <jstucklex_at_attglobal.net> said:
> 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.
Hi Jerry!
The real code is mostly a bunch of loops that build the SQL statement and the where clause by reading values out of Tkinter fields with stuff in them put there by the user, so it would be meaningless. The SQLness works, I just can't get the query to return stuff in the order I need. However...
I didn't know about the ORDER BY working that way. Why isn't ASC or DESC in the index of DuBois' book? That's the kind of thing I'm after (ORDER BY Kingdom->Style ASC, Date DESC), but it's still not working.
In the example I get (each line here actually gets returned as a Python dictionary and they are all in a tuple)
(barcode=1,kingdom=a,phylum=a,class=a,make=a,model=a,style=a,date=2010) (barcode=1,kingdom=a,phylum=a,class=a,make=a,model=a,style=a,date=2011) (barcode=1,kingdom=a,phylum=a,class=a,make=a,model=a,style=a,date=2012)
(barcode=2,kingdom=b,phylum=b,class=b,make=b,model=b,style=b,date=2010) (barcode=2,kingdom=b,phylum=b,class=b,make=b,model=b,style=b,date=2011)
with desc at the end I get
(barcode=2,kingdom=b,phylum=b,class=b,make=b,model=b,style=b,date=2011) (barcode=2,kingdom=b,phylum=b,class=b,make=b,model=b,style=b,date=2010)
(barcode=1,kingdom=a,phylum=a,class=a,make=a,model=a,style=a,date=2012) (barcode=1,kingdom=a,phylum=a,class=a,make=a,model=a,style=a,date=2011) (barcode=1,kingdom=a,phylum=a,class=a,make=a,model=a,style=a,date=2010)
what I'm after is
(barcode=1,kingdom=a,phylum=a,class=a,make=a,model=a,style=a,date=2012) (barcode=1,kingdom=a,phylum=a,class=a,make=a,model=a,style=a,date=2011) (barcode=1,kingdom=a,phylum=a,class=a,make=a,model=a,style=a,date=2010)
(barcode=2,kingdom=b,phylum=b,class=b,make=b,model=b,style=b,date=2011) (barcode=2,kingdom=b,phylum=b,class=b,make=b,model=b,style=b,date=2010)
Wait...I think I stumbled on to it. I wanted things that are the same kind of thing to be lumped together (kingdom->style) in ascending order, but then I also wanted all of the entries for a given individual item (barcode) lumped together, but then all of those in descending date order. All I had to do was add the barcode to the end of the kingdom->style ORDER BY list. So in the SQL at the top it should be "...inz.style, inz.barcode asc, mdz.date desc". That has worked. Getting things this way just makes the printing routine easier. It just loops through the returned tuple of dictionaries and prints a header describing the item then lists the maint entries until it gets a new barcode, then it prints a new header and the entries for that barcode untill... Woohoo! Now I can keep working? :)
Thanks a bunch! You've made me dangerous.
Bob Received on Mon Mar 14 2016 - 21:43:11 CET