List record from one table and all matching in another

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

List record from one table and all matching in another

Flakheart
I have two tables with data and I know I can use JOINS to list all data with duplications in a monolithic table but I need something along the following lines for user display purposes.

Groceries table:

Itemname, Category, Brandname, Manufacturer, Packaging, Units, Weight, Note, Picture, Barcode, Deleted, Record

History table: (ItemDataId corresponds to the Record number in Groceries table)

´╗┐ItemDataId, Boughton, Boughtfrom, Quantity, Aisle, Price, Discount, Total, Note, Record

If I "SELECT * FROM Groceries" to list all records, how do I arrange a query to list each record in the groceries table with all matching history items under each record.

I:e (Just including column names for reference)

ItemName: 'Dishwashing Liquid', BrandName: 'HoneySuckle', Manufacturer: 'Reardon'
   BoughtOn: '12/02/2000', BoughtFrom: 'Aldi',           Quantity: '3', Aisle: '15', Price: '$1.50'
   BoughtOn: '18/06/2001', BoughtFrom: 'Coles',        Quantity: '1', Aisle: '1', Price: '$2.50'
   BoughtOn: '12/02/2000', BoughtFrom: 'Safeway',    Quantity: '5', Aisle: '8', Price: '$6.50'
ItemName: 'Sticky Tape', BrandName: 'StickRite', Manufacturer: 'Rogers'
   BoughtOn: '22/05/2011', BoughtFrom: 'Big-W',        Quantity: '12', Aisle: '9', Price: '$.30'
   BoughtOn: '22/05/2011', BoughtFrom: 'Big-W',        Quantity: '12', Aisle: '9', Price: '$.30'
Reply | Threaded
Open this post in threaded view
|

Re: List record from one table and all matching in another

Igor Tandetnik-2
On 9/20/2013 8:52 AM, Flakheart wrote:
> If I "SELECT * FROM Groceries" to list all records, how do I arrange a query
> to list each record in the groceries table with all matching history items
> under each record.

You do "select * from Groceries g join History h on (g.Record =
h.´╗┐ItemDataId)" to get the data out, then format it to taste in your
application code.
--
Igor Tandetnik

_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: List record from one table and all matching in another

Simon Slavin-3
In reply to this post by Flakheart

On 20 Sep 2013, at 1:52pm, Flakheart <[hidden email]> wrote:

> I have two tables with data and I know I can use JOINS to list all data with
> duplications in a monolithic table but I need something along the following
> lines for user display purposes.
>
> Groceries table:
>
> Itemname, Category, Brandname, Manufacturer, Packaging, Units, Weight, Note,
> Picture, Barcode, Deleted, Record
>
> History table: (ItemDataId corresponds to the Record number in Groceries
> table)
>
> ´╗┐ItemDataId, Boughton, Boughtfrom, Quantity, Aisle, Price, Discount, Total,
> Note, Record
>
> If I "SELECT * FROM Groceries" to list all records, how do I arrange a query
> to list each record in the groceries table with all matching history items
> under each record.

You do it with two queries, not one.  In all seriousness, layout is the job of your programming language.  The database system just hands it data. Don't try to use SQL to do your layout for you, you'll end up with a huge SELECT statement that is difficult to understand and breaks any time you change anything.

In your situation I'd probably do something like (simplified)

SELECT * FROM History ORDER BY ItemDataId, Boughton DESC

This would give you your entire purchase history sorted so that all the entries for one Grocery are listed together, and within that the most recent purchase is at the top.

Then I'd have my program go down the list, printing each line, but before I print a line I compare the ItemDataId in this row with the ItemDataId of the last row.  If the ItemDataId has changed, then you know you've moved to a new grocery, so you need to print a header for the new one.  So you do

SELECT * FROM Groceries WHERE rowid = [the new itemDataId]

and that's the data to show in the header.

There are many improvements to above (including not fetching * unless you need *) but that should get you started.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: List record from one table and all matching in another

James K. Lowden
On Sat, 21 Sep 2013 15:06:14 +0100
Simon Slavin <[hidden email]> wrote:

> > If I "SELECT * FROM Groceries" to list all records, how do I
> > arrange a query to list each record in the groceries table with all
> > matching history items under each record.
>
> You do it with two queries, not one.  In all seriousness, layout is
> the job of your programming language.  The database system just hands
> it data. Don't try to use SQL to do your layout for you, you'll end
> up with a huge SELECT statement that is difficult to understand and
> breaks any time you change anything.

Yes, SQL is not a layout/display language.  But that's not very good
support for the advice to use two queries.  

Joining two tables is hardly going to create one "huge" query.  More
important, in the general case two queries cannot be guaranteed to be
consistent: it is possible for a grocery item to have been deleted
while processing the history table.  

And then there's performance. Issuing per-row queries while processing
a results must be the dominant antipattern of our time.  Instead of a
single query and one trip to the DBMS, the application endures N
trips, with the attendant delay.  

The right answer is to join the tables in a single query and -- as you
suggest --use application logic detect when a "new" item needs a new
header.  

--jkl
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: List record from one table and all matching in another

Simon Slavin-3

On 22 Sep 2013, at 4:06pm, James K. Lowden <[hidden email]> wrote:

> The right answer is to join the tables in a single query and -- as you
> suggest --use application logic detect when a "new" item needs a new
> header.

Agreed.  So to summarise for the OP, the best solution is a mixture of the two that Igor and myself posted.  Do the SELECT that I posted to get the right order, with the JOIN that Igor posted to get the title section from when you move from one grocery to the next.

And that's why you want answers to your questions be posted to the list, folks, not directly to you.  Because sometimes one reader will be able to improve on another reader's answer.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users