August 31, 2007 at 1:44 pm
Greetings,
I am trying to design a query to retrieve information from multiple tables. One of those tables has multiple records that match the key and I only want to find the last record. I have looked on the web and found some examples where they suggest doing a select statement in the inner join portion of the query. However, when I do that I get no records returned. Can you assist me in figuring this out?
Here is what returns no records:
select distinct rdalky, abalph, aladd1, aladd2, aladd3, alcty1, aladds, substring(aladdz,1, 5), rd$pocka, rd$dschg, rdlitm
from testdta.f56rdssp with (NOLOCK)
inner join testdta.f0101sp with (NOLOCK) on testdta.f56rdssp.rdalky = testdta.f0101sp.abalky
inner join
(select TOP 1 alan8, aladd1, aladd2, aladd3, alcty1, aladds, aladdz
from testdta.f0116sp with (NOLOCK)
order by alupmj desc) C
on testdta.f0101sp.aban8 = C.alan8
where rd$alky = 'ABCDEF' and rdalky = '123ABC456'
Here is what returns multiple records:
select distinct rdalky, abalph, aladd1, aladd2, aladd3, alcty1, aladds, substring(aladdz,1, 5), rd$pocka, rd$dschg, rdlitm
from testdta.f56rdssp with (NOLOCK)
inner join testdta.f0101sp with (NOLOCK) on testdta.f56rdssp.rdalky = testdta.f0101sp.abalky
inner join testdta.f0116sp with (NOLOCK) on testdta.f0101sp.aban8 = testdta.f0116sp.alan8
where rd$alky = 'ABCDEF' and rdalky = '123ABC456'
TIA!
Jeremey
August 31, 2007 at 2:20 pm
The top 1 will specifically give you just one record for the entire set which may not match anything. However I cannot decern enough infomation from yuor titles to help. Can you explain to us what field is a the date you want to use and how you know a particular set of records are the same relationship considering that date?
September 2, 2007 at 11:51 am
Please post more readable code - use table name/alias with each column in the select.
Table structure of involved tables would also be helpful.
At the moment, I can only make a wild shot... select from f0116sp (derived table C) should only be a "filter" to pinpoint the correct row in this table, and with another join to this table you retrieve columns you want to use. But a lot depends on what do you want to show and what data you have.
Example:
CREATE TABLE #customer(custid int identity, custname varchar(30))
CREATE TABLE #order (orderid int identity, customer int, orderdate datetime, shippingdate datetime, product varchar(20), amount int)
INSERT INTO #customer(custname) values ('ONE')
INSERT INTO #customer(custname) values ('TWO')
INSERT INTO #order(customer, orderdate, shippingdate, product, amount) VALUES (1, '20070504', '20070530', 'Book', 15)
INSERT INTO #order(customer, orderdate, shippingdate, product, amount) VALUES (2, '20070506', '20070509', 'Video', 8)
INSERT INTO #order(customer, orderdate, shippingdate, product, amount) VALUES (1, '20070507', '20070509', 'DVD', 22)
INSERT INTO #order(customer, orderdate, shippingdate, product, amount) VALUES (1, '20070511', '20070520', 'Video', 30)
INSERT INTO #order(customer, orderdate, shippingdate, product, amount) VALUES (2, '20070512', '20070516', 'DVD', 10)
/*last orders by IDENTITY column (last inserted)*/
SELECT c.custid, c.custname, o.orderdate, o.shippingdate, o.product, o.amount
FROM #customer c
JOIN (select customer, max(orderid) as lastorder
from #order
group by customer) as Q ON Q.customer = c.custid
JOIN #order o ON o.orderid = Q.lastorder
/*last orders by shipping date - last delivered - only works if shippingdate is unique for each customer (otherwise returns all orders from the last date)*/
SELECT c.custid, c.custname, o.orderdate, o.shippingdate, o.product, o.amount
FROM #customer c
JOIN (select customer, max(shippingdate) as lastship
from #order
group by customer) as Q ON Q.customer = c.custid
JOIN #order o ON o.customer = Q.customer AND o.shippingdate = Q.lastship
/*last orders by shipping date - last delivered - works with multiple deliveries on the same date, but correlated subqueries tend to have bad performance*/
SELECT c.custid, c.custname, o.orderdate, o.shippingdate, o.product, o.amount
FROM #customer c
JOIN #order o ON o.customer = c.custid AND o.orderid=(select top 1 orderid from #order where customer=c.custid order by shippingdate DESC, orderid DESC)
/*CLEANUP*/
DROP TABLE #customer
DROP TABLE #order
Sorry, got some work now, I might return later or tomorrow if I have time. Good luck!
September 4, 2007 at 10:31 am
Thanks for the replies I will try to be more clear on what I am attempting.
The date field that I want to get the last record on is ALUPMJ.
the names of the fields are:
select distinct f0101sp.longAddressNumber, f0101sp.AlphaName, f0116sp.Address1, f0116sp.Address2, f0116sp.Address3, f0116sp.City, f0116sp.State, f0116sp.Zip, f56rdssp.PocketLease, f56rdssp.DistributionCharge, f56rdssp2ndItemNumber
From testdta.F56RDSSP with (NOLOCK)
inner jion testdta.f0101sp with (NOLOCK) on testdta.f56rdssp.rdalky = testdta.f0101sp.abalky
inner join
(select top 1 f0116sp.alan8, f0116sp.aladd1, f0116sp.aladd2, f0116sp.aladd3, f0116sp.alcty1, f0116sp.aladds, f0116sp.aladdz
from testdta.f0116sp with (NOLOCK)
order by alupmj desc) C
on testdta.f0101sp.aban8 = C.alan8
where f56rdssp.rd$alky = 'ABCDEF' and f56rdssp.rdalky = '123ABC456'
F0116sp contains multiple address records for each individual record in F0101sp. We are wanting the last record in F0116sp based off of ALUPMJ (Date updated) that matches to the addressNumber (AN8) from F0101SP.
September 5, 2007 at 1:44 am
I'm sorry, but your select list contains references that can't be used in this place... Table f0116sp is not available unless you join it - the derived table is aliased "C" and would have to be referenced to as C in the select, but that doesn't help either, since columns with these names (e.g. address1) don't exist in derived table C.
Maybe instead of f0116sp.Address1 there should be C.aladd1, and so on? Could you please try to correct it so that we can be sure what's what?
BTW, did you check the examples in my previous post? I'm almost sure these describe what you need and could be adapted for your environment.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy