Re: Sorting of data from two tables
От | R. Smith |
---|---|
Тема | Re: Sorting of data from two tables |
Дата | |
Msg-id | CADuTMYBwTaecTyZG_a0zbviPpF65PQ0zm7uyaG5oxocxVoFfDg@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Sorting of data from two tables (David Johnston <polobo@yahoo.com>) |
Ответы |
Re: Sorting of data from two tables
(David Johnston <polobo@yahoo.com>)
|
Список | pgsql-sql |
On Sat, Sep 17, 2011 at 2:56 PM, David Johnston <polobo@yahoo.com> wrote: > On Sep 17, 2011, at 9:32, "R. Smith" <ship.quotes@gmail.com> wrote: > > > What I want to do is do a query joining table A with B and sorting > firstly on a field in Table A then on several fields in Table B. > > > SELECT a.gdn_gdn, a.gdn_custref, a.gdn_date, a.gdn_address_name, > a.gdn_method, b.gdn_stockref, b.gdn_row, b.gdn_bay, b.gdn_shelf > FROM a > LEFT JOIN b > ON a.gdn_gdn = b.gdn_gdn > ORDER BY a.gdn_method,b.gdn_row, b.gdn_bay, b.gdn_shelf > > > It would help if you gave an example comparing the above query results with > your desired results. It would also help if you actually provided a > detailed description of you goal instead of the generic one quoted above. > Given your stated need the query does exactly what you requested. > David J. Ok, here is some more specific information. The data stored is inventory pick data. We have a table which stores all the header information, addresses etc (Table A) and then each order line (Table B) which stores item to be picked and location. Now what we want is the data sorted by the method to be sent out which is stored in Table A and then the location of the item being picked which is stored in Table B. Now where there is more than one order line we just sort on the first order line. If we use the above query to sort the data, we end up with the order data all mixed up with one multiple orders spread amongst other orders, as the query does not know that some of the orders in Table A have multiple order lines in Table B. So we may pick order 1, then order 2, then order 3, and all of sudden come back to order 1, as there is a second order line to pick which is in a different location to the first order line in order 1, and order 2 and order 3. So simple solution is to drop it in to a cross tab query, then sort it as many times as you like as all the order data is in one row now rather then multiple ones. However the moment you run the crosstab query it fails, as the number columns are a variable which you cannot have. Hence my asking how you do this? Sample data output: Standard Query Order 1, Despatch Method A, Orderline1, Item Ref, Location A Order 2, Despatch Method A, Orderline1,Item Ref, Location A Order 3, Despatch Method A, Orderline1,Item Ref, Location A Order 1, Despatch Method A, Orderline2, Item Ref, Location B Order 4, Despatch Method A, Orderline1,Item Ref, Location B Order 5, Despatch Method B, Orderline1,Item Ref, Location A Order 6, Despatch Method B, Orderline1,Item Ref, Location B Order 7, Despatch Method B, Orderline1,Item Ref, Location B Order 5, Despatch Method B, Orderline2,Item Ref, Location B Crosstab Query Order 1, Despatch Method A, Orderline1, Item Ref, Location A, Orderline2, Item Ref, Location B, Order 2, Despatch Method A, Orderline1,Item Ref, Location A Order 3, Despatch Method A, Orderline1,Item Ref, Location A Order 4, Despatch Method A, Orderline1,Item Ref, Location B Order 5, Despatch Method B, Orderline1,Item Ref, Location A, Orderline2,Item Ref, Location B Order 6, Despatch Method B, Orderline1,Item Ref, Location B Order 7, Despatch Method B, Orderline1,Item Ref, Location B Richard
В списке pgsql-sql по дате отправления:
Предыдущее
От: pasman pasmańskiДата:
Сообщение: Re: Better way to check more than 1 value NOT IN (...)