Results 1 to 5 of 5

Thread: [RESOLVED] Inner Join? as opposed to returning a record set and interating through it?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Dec 2008
    Location
    Hobart, Tasmania
    Posts
    104

    Resolved [RESOLVED] Inner Join? as opposed to returning a record set and interating through it?

    Hi people.

    I'm using MS SQL server 2008 with visual basic 6...

    I'm trying to construct a query that does not require that I return a record set and then run another query for each line in the recordset, which I know is a slow way to do it.

    I've tried various ways to implement an inner join... but most I have used in the past have had static where clauses, and this I need to be dynamic, ie, take in the data from a record set row and use that in the where clause for the inner join.

    I hope that makes some sense...

    For example... I have a database that has:
    ItemDescription
    ItemCategory
    ItemLocation
    SaleDate
    SalePrice

    example data:
    Milk: Dairy:Shop1:12/01/2010:3.00
    Milk: Dairy:Shop1:08/04/2009:3.25
    bread: Bakery:Shop1:08/02/2009:3.00
    Milk: Dairy:Shop2:01/06/2009:2.95
    bread: Bakery:Shop2:08/02/2009:4.50

    I need to return a list of all records that match a given ItemCategory AND ItemLocation, AND that is also the most recent matching record for each ItemDescription...

    eg... the first query, assuming ItemCategory and ItemLocation are set to Dairy and Shop1 Respectively, should return
    Milk: Dairy:Shop1:12/01/2010:3.00, as it is the most recent dairy item sold in shop 1....

    I SORT of have that... as in:
    Code:
    select ItemDescription, SalePrice, max(SaleDate) as MDate
    from Stock.dbo.Sales
    where ItemDescription = 'Milk'
    and ItemCategory = '510'
    and ItemLocation = "Dairy"
    group by ItemDescription, SalePrice
    order by MDate DESC
    However, I have to put in the ItemDescription for the where query... whereas I'd like it to list all items that match the ItemCategory and ItemLocation... and then return, for each matching ItemDescription, only the most recent record...

    To further complicate it, I then need to return all records that match that returned ItemDescription's in the first query, and that also have an earlier SaleDate than the first query, ignoring ItemCategory or ItemLocation...

    I am honestly no expert at this... as may be patently obvious... but I was wondering whether I should put the first part... up to the complicated bit, into a temp table, and then do another query where I loop through that temp table, filling out the clauses from it...although I'm not really familiar with using and addressing temptables... I normally just return recordsets and iterate through them in vb..

    eg: psuedo-code

    Code:
    select * from Stock.dbo.Sales
    where itemdescription = temptable.itemdescription.rownumber
    and saledate < temptable.saledate.rownumber
    This should then return, for the originally returned record above...
    Milk: Dairy:Shop1:08/04/2009:3.25
    Milk: Dairy:Shop2:01/06/2009:2.95

    ie, the older data for that ItemDescription, irrespective of its ItemCategory or ItemLocation.

    But I'm sure there is a faster way to do it...However, FWIW, later, in vb, I need access to both sets of data...

    To explain it better (hopefully), I need the query to work out the most recently sold items that match the category and location, return that as a recordset, table, whatever.. and then, find all previous records for those matching itemDescriptions, ignoring ItemCategory or ItemLocation, and put these into another recordset or table.

    I hope this makes some sense, and apologies if it doesn't, it is after 1 am here at the moment... If you need me to clarify anything, please let me know.
    Thanks for any help you can provide, and have a nice weekend.
    Last edited by joshAU; Apr 13th, 2012 at 10:58 AM. Reason: addition of example data, clarity.. I hope

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Inner Join? as opposed to returning a record set and interating through it?

    You're so close to achieving it already that you're going to kick yourself when you see the answer

    However, I have to put in the ItemDescription for the where query... whereas I'd like it to list all items that match the ItemCategory and ItemLocation... and then return, for each matching ItemDescription, only the most recent record...
    No problem there, just take out the where clause that refers to ItemDescription:-
    Code:
    select ItemDescription, SalePrice, max(SaleDate) as MDate
    from Stock.dbo.Sales
    where ItemCategory = '510'
    and ItemLocation = "Dairy"
    group by ItemDescription, SalePrice
    order by MDate DESC
    To further complicate it, I then need to return all records that match that returned ItemDescription's in the first query, and that also have an earlier SaleDate than the first query, ignoring ItemCategory or ItemLocation...
    I think we need a little bit more info to answer that. Do you want to see all the sales records that fulfil the criteria (i.e. if there are three prior sales records for item X you would see each of the three records displayed) or do you just want to know which products have at least one prior Sales Record (i.e. if product X had three prior sales record you just want to see product X)? Or is there some other scenario?
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Dec 2008
    Location
    Hobart, Tasmania
    Posts
    104

    Re: Inner Join? as opposed to returning a record set and interating through it?

    OMG!
    Ha ha ha..

    You're so close to achieving it already that you're going to kick yourself when you see the answer
    OK, I'm kicking myself right now...

    That was... mmm... well, I'll blame the hour of posting...

    Thank you, FunkyDexter, for pointing that out to me, much appreciated...despite being somewhat embarrassing....

    The second... err.... the complicated part...
    well, yes, I did want to get, for each matching product X returned in the first recordset, a record for each and every record of product X that predates the Saledate..
    ie. if two records were returned in the first query /part of query...

    ie
    ItemDescription: SaleDate
    Product X : 01/01/2012
    Product Y : 01/10/2011

    Then I'd get another recordset at the end, that would have multiple records for each product... ie:

    ItemDescription: SaleDate :ItemCategory : ItemLocation : SalePrice
    Product X : 01/09/2011 : etc...
    Product X : 22/05/2011 : etc ...
    Product X : 22/05/2006 : etc ...
    Product Y : 30/09/2011: etc...
    Product Y : 25/09/2009 : etc...

    So I guess somehow combining, the first recordset... by, I'm guessing here, putting it into a temptable, and then running a query as I mentioned in the first post...

    ie:
    Code:
    select * from Stock.dbo.Sales
    where itemdescription = temptable.itemdescription.rownumber
    and saledate < temptable.saledate.rownumber
    However, as I said... I'm not familiar with looping through a table using a query.
    Normally, from here, I'd probably take the first recordset into vb6, then loop through that, build a new sql query statement there for each product returned in that initial query... and run a new query for each...

    I'm guessing though that it would be a lot slower than combining it directly within SQL... hence why I'd like to combine it into one statement.

    Hope this makes some sense...
    Thanks again FunkyDexter.
    If you have any further ideas, I'd appreciate it.

    I think I may have to stand back and have a better look at what I'm trying to do and the best way to get there...

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: Inner Join? as opposed to returning a record set and interating through it?

    I'll blame the hour of posting
    Meh, we've all done it.

    putting it into a temptable
    Step away from the temp table. Put the temp table down and calmly back away.

    Temp tables are OK but it's almost always better to do things with a single query if you can. Looping, on the other hand isn't OK at all. It works but it's sloooooow and it's always worth remembering that, in a database, you're not just slowing down the current user but also every other user that's waiting for the cpu. Your desire to do it in a single query is bang on the money and you should give yourself a pat on the back for having exactly the right goal in mind... even if you don't have all the tools to achieve it yet.

    A good way to move from temp tables to single queries is to take the view that a temp table is nothing but a sub query. And you can join to sub queries just the same way as you can join to a temp table. The query in your last post looks right to me (although I'm not sure you need the rownumber bit) so you've already got both the queries you need. Now you just need to join them (I've chucked a few aliases in just to make it clearer what's happening):-
    Code:
    select LatestSales.ItemDescription, LatestSales.SalePrice, max(LatestSales.SaleDate) as MDate, PriorSales.SaleDate, PriorSales.SalePrice etc
    from Stock.dbo.Sales as LatestSales
    join (select * from Stock.dbo.Sales as InnerSales
          where LatestSales.itemdescription = InnerSales.itemdescription
          and InnerSales.saledate < LatestSales.saledate) as PriorSales
        on LatestSales.itemdescription = PriorSales.itemdescription
    where LatestSales.ItemCategory = '510'
    and LatestSales.ItemLocation = "Dairy"
    group by LatestSales.ItemDescription, LatestSales.SalePrice, PriorSales.SaleDate, PriorSales.SalePrice
    order by LatestSales.MDate DESC
    OK, that's now a functional query. It's still a bit ugly though, and we've checked this: LatestSales.itemdescription = InnerSales.itemdescription
    twice. Once in the join condition and once in the inner where clause. In fact, you might spot at this point that you don't need a sub query at all, you could just join to a second copy of the sales table using the apropriate criteria:-
    Code:
    select LatestSales.ItemDescription, LatestSales.SalePrice, max(LatestSales.SaleDate) as MDate, PriorSales.SaleDate, PriorSales.SalePrice etc
    from Stock.dbo.Sales as LatestSales
    join Stock.dbo.Sales as PriorSales
        on LatestSales.itemdescription = PriorSales.itemdescription
        and PriorSales.saledate < LatestSales.saledate
    where LatestSales.ItemCategory = '510'
    and LatestSales.ItemLocation = "Dairy"
    group by LatestSales.ItemDescription, LatestSales.SalePrice, PriorSales.SaleDate, PriorSales.SalePrice 
    order by LatestSales.MDate DESC
    That should be all you need. It's a single query so you're giving the DBMS the best chance of coming up with an efficient execution plan and the aliases make it pretty clear as to what your doing with each version of the table so it's quite readable.

    Beware that I typed all this straight into the post and haven't syntax checked it in a code window but it should serve to demonstrate the principle quite well.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Dec 2008
    Location
    Hobart, Tasmania
    Posts
    104

    Re: Inner Join? as opposed to returning a record set and interating through it?

    Step away from the temp table. Put the temp table down and calmly back away.
    Ha ha ha.. You do make me laugh!

    As you know, I did hope that it would be possible within a single query, but, without the knowledge, I was going to just resort to what I knew... clubbing it to death with a big stick repeatedly.

    Thank you SO much for your help and suggested queries, and for explaining it in such detail.

    Your use of LatestSales. and PriorSales. is illuminating... and finally I have made some sense of it... plus breaking down and simplifying it further in your second suggested query makes the learning curve easier.
    Thank you again so much!

    Oh... and you were right re the rownnumber bits... just a throwback to looping through each recordset in vb...

    Hmm... I see potentials here.... wow... so many ways of speeding up other parts of this program...

    Thank you so much once again... I'll study it in more detail tomorrow and try to implement it... but it looks exactly like it'll do the job.

    Thanks, once again, FunkyDexter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width