Type25
asked on
LEFT JOIN top 1
Quite an easy one i'm sure but can't get my head around it..
I have a jobs table and an items table (one to many)
I want to select all records from jobs and only record from items (the min date)
Jobs
-----
JobNo
other data..
Items
ItemID
JobNo
myDate
My results should only return one row from items based on the MIN(mydate).
The query should still return records from jobs if 1) No rows matched in items and 2) null in mydate column
thanks guys
I have a jobs table and an items table (one to many)
I want to select all records from jobs and only record from items (the min date)
Jobs
-----
JobNo
other data..
Items
ItemID
JobNo
myDate
My results should only return one row from items based on the MIN(mydate).
The query should still return records from jobs if 1) No rows matched in items and 2) null in mydate column
thanks guys
yep - and that should be a LEFT join (as you specified...)
select j.*,i.itemid,i.myDate
from jobs j
left join
(select itemid,jobno,min(myDate)
from items
group by itemid,jobno) i
select j.*,i.itemid,i.myDate
from jobs j
left join
(select itemid,jobno,min(myDate)
from items
group by itemid,jobno) i
(damn this too-quick posting lark):
on point 2, about NULLs in mydate:
or some such early date... and of course give it a column alias
on point 2, about NULLs in mydate:
or some such early date... and of course give it a column alias
select j.*,i.itemid,i.myDate
from jobs j
left join
(select itemid,jobno,min(ISNULL(myDate,'01/01/1950')) myDate
from items
group by itemid,jobno) i
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
inner query x takes care of null mydates, but gives it a date such that other records with valid mydate is preferred
inner query y takes care of filtering for exactly one item if more than one matches min mydate
final query selects all job and matches up with available item data
inner query y takes care of filtering for exactly one item if more than one matches min mydate
final query selects all job and matches up with available item data
ASKER
This is what i ended up with, pretty sure that's okay?
SELECT dbo.Jobs.JobNo, dbo.Jobs.ContactsSent, dbo.Jobs.VisitBookedFor, dbo.Jobs.FirstReport, dbo.LicencesHeld.LicNo, dbo.LicencesHeld.LicName,
dbo.LicencesHeld.LicUserName, i.RepDate
FROM dbo.Jobs INNER JOIN
dbo.LicencesHeld ON dbo.Jobs.LicID = dbo.LicencesHeld.LicID LEFT JOIN
(select top 1 itemid,jobno,min(ISNULL(ReplacementLetterSent,'01 Jan 1950')) RepDate from items y group by itemid,y.jobno) i
ON i.jobno = Jobs.jobno
WHERE (dbo.Jobs.BUID IN (2495,3529 ,3542,3537,3540) AND (dbo.LicencesHeld.LiveUser = 1) AND (dbo.Jobs.VisitBookedFor > CONVERT(DATETIME, @FromDate, 103 ) AND
dbo.Jobs.VisitBookedFor < CONVERT(DATETIME, @ToDate, 103)))
ORDER BY Jobs.JobNo
SELECT J.*
, ISNULL(I.ItemID, '<< No Item >>'
FROM Jobs J
LEFT JOIN (
SELECT ItemID
, JobNo
FROM Items
JOIN (
SELECT ItemID
, JobNo
, MIN(MyDate) AS MinDate
FROM Items
GROUP BY ItemID
) M
ON M.ItemID = I.ItemID
AND M.JobNo = I.JobNo
AND M.MinDate = I.MyDate
) I
ON I.JobNo = J.JobNo
ASKER
ok this doesn't work:
SELECT dbo.Jobs.JobNo, dbo.Jobs.ContactsSent, dbo.Jobs.VisitBookedFor, dbo.Jobs.FirstReport, dbo.LicencesHeld.LicNo, dbo.LicencesHeld.LicName,
dbo.LicencesHeld.LicUserNa me, i.itemid
FROM dbo.Jobs INNER JOIN
dbo.LicencesHeld ON dbo.Jobs.LicID = dbo.LicencesHeld.LicID LEFT JOIN
(select itemid,jobno,min(ISNULL(Re placementL etterSent, '01 Jan 1950')) RepDate from items y group by itemid,y.jobno) i
ON i.jobno = Jobs.jobno
WHERE (dbo.Jobs.BUID IN (2495,3529 ,3542,3537,3540) AND (dbo.LicencesHeld.LiveUser = 1) AND (dbo.Jobs.VisitBookedFor > CONVERT(DATETIME, @FromDate, 103 ) AND
dbo.Jobs.VisitBookedFor < CONVERT(DATETIME, @ToDate, 103)))
ORDER BY Jobs.JobNo
It's selecting more than one record per job, which looking at it you can see.
If i add in TOP 1 into the select join query then it doesn't return anything..
any ideas?
SELECT dbo.Jobs.JobNo, dbo.Jobs.ContactsSent, dbo.Jobs.VisitBookedFor, dbo.Jobs.FirstReport, dbo.LicencesHeld.LicNo, dbo.LicencesHeld.LicName,
dbo.LicencesHeld.LicUserNa
FROM dbo.Jobs INNER JOIN
dbo.LicencesHeld ON dbo.Jobs.LicID = dbo.LicencesHeld.LicID LEFT JOIN
(select itemid,jobno,min(ISNULL(Re
ON i.jobno = Jobs.jobno
WHERE (dbo.Jobs.BUID IN (2495,3529 ,3542,3537,3540) AND (dbo.LicencesHeld.LiveUser
dbo.Jobs.VisitBookedFor < CONVERT(DATETIME, @ToDate, 103)))
ORDER BY Jobs.JobNo
It's selecting more than one record per job, which looking at it you can see.
If i add in TOP 1 into the select join query then it doesn't return anything..
any ideas?
Hi,
just try:
select j.*,itm.itemid,itm.myDate
from jobs j left
join
(select Min(itemid) as itemid,jobno,min(myDate) as mydate
from items
group by jobno)as itm
ON j.jobno = itm.jobno
just try:
select j.*,itm.itemid,itm.myDate
from jobs j left
join
(select Min(itemid) as itemid,jobno,min(myDate) as mydate
from items
group by jobno)as itm
ON j.jobno = itm.jobno
SELECT J.*
FROM jobs J
LEFT JOIN (SELECT JobNo, Min(mydate) myDate
FROM items
GROUP BY JobNo) AS I
ON I.JobNo = J.JobNo
FROM jobs J
LEFT JOIN (SELECT JobNo, Min(mydate) myDate
FROM items
GROUP BY JobNo) AS I
ON I.JobNo = J.JobNo
Just try this(my first script was not correct):
select j.jobno,Jobs.ContactsSent,
from jobs j left
join
(select jobno,min(myDate) as mydate
from items
group by jobno)as itm
ON j.jobno = itm.jobno
left join items On itm.jobno = items.jobno
and itm.mydate = items.mydate
group by j.jobno,Jobs.ContactsSent,
i merged your table/column names with my first proposal
declare @FromDate datetime
declare @toDate datetime
SELECT dbo.Jobs.JobNo, dbo.Jobs.ContactsSent, dbo.Jobs.VisitBookedFor, dbo.Jobs.FirstReport, dbo.LicencesHeld.LicNo, dbo.LicencesHeld.LicName,
dbo.LicencesHeld.LicUserName, i.itemid
FROM dbo.Jobs INNER JOIN
dbo.LicencesHeld ON dbo.Jobs.LicID = dbo.LicencesHeld.LicID LEFT JOIN
(select x.jobno, minReplacementLetterSent, min(itemid) as itemid
from
(select jobno, min(isnull(ReplacementLetterSent, '9900-01-01')) as minReplacementLetterSent from item group by jobno) x
inner join item on x.jobno = item.jobno and x.minReplacementLetterSent = isnull(item.ReplacementLetterSent, '9900-01-01')
group by x.jobno, minReplacementLetterSent) y on y.jobno = Jobs.jobno
WHERE (dbo.Jobs.BUID IN (2495,3529 ,3542,3537,3540) AND (dbo.LicencesHeld.LiveUser = 1) AND (dbo.Jobs.VisitBookedFor > CONVERT(DATETIME, @FromDate, 103 ) AND
dbo.Jobs.VisitBookedFor < CONVERT(DATETIME, @ToDate, 103)))
ORDER BY Jobs.JobNo
code testing visible. please drop the two declare @.. datetimes from the top
well ignore it completely. just use the below
SELECT dbo.Jobs.JobNo, dbo.Jobs.ContactsSent, dbo.Jobs.VisitBookedFor, dbo.Jobs.FirstReport, dbo.LicencesHeld.LicNo, dbo.LicencesHeld.LicName,
dbo.LicencesHeld.LicUserName, i.itemid
FROM dbo.Jobs INNER JOIN
dbo.LicencesHeld ON dbo.Jobs.LicID = dbo.LicencesHeld.LicID LEFT JOIN
(select x.jobno, minReplacementLetterSent, min(itemid) as itemid
from
(select jobno, min(isnull(ReplacementLetterSent, '9900-01-01')) as minReplacementLetterSent from item group by jobno) x
inner join item on x.jobno = item.jobno and x.minReplacementLetterSent = isnull(item.ReplacementLetterSent, '9900-01-01')
group by x.jobno, minReplacementLetterSent) i on i.jobno = Jobs.jobno
WHERE (dbo.Jobs.BUID IN (2495,3529 ,3542,3537,3540) AND (dbo.LicencesHeld.LiveUser = 1) AND (dbo.Jobs.VisitBookedFor > CONVERT(DATETIME, @FromDate, 103 ) AND
dbo.Jobs.VisitBookedFor < CONVERT(DATETIME, @ToDate, 103)))
ORDER BY Jobs.JobNo
from jobs j
join
(select itemid,jobno,min(myDate)
from items
group by itemid,jobno) i
ON i.jobno = j.jobno