May 6, 2003 at 11:20 am
I am trying to return a distinct value (unit) and the 1st/top 1 occurance of an id. I cannot figure out where to place the id portion fo the query or if I should be using group by. I should be returning 46k rows but, I am ending up with 126k rows.
--------------
SELECT WorkHistory. Unit,-- Workhistory.travelerid
(Select top 1(traveler.travelerid)
from Traveler
where traveler.travelerid = workhistory.travelerid
order by traveler.travelerid) as Traveler --
FROM WorkHistory
WHERE (Workhistory.Unit NOT IN
(SELECT UnitType.Description
FROM unittype)) AND
(workhistory.Unit NOT IN
(SELECT UnitType.fullDescription
FROM unittype))
group by workhistory.unit, workhistory.travelerid
------------
thanks for any sugestions
May 6, 2003 at 12:25 pm
Unless I am mistaken, something in your SQL just doesn't make sense. This part:
(Select top 1(traveler.travelerid)
from Traveler
where traveler.travelerid = workhistory.travelerid
order by traveler.travelerid) as Traveler --
doesn't make any sense to me. What are you trying to accomplish here? Do you mean to do:
SELECT WorkHistory. Unit, MIN(Workhistory.travelerid) as Traveler
FROM WorkHistory
WHERE (Workhistory.Unit NOT IN
(SELECT UnitType.Description
FROM unittype)) AND
(workhistory.Unit NOT IN
(SELECT UnitType.fullDescription
FROM unittype))
group by workhistory.unit
Also, I am confused as to why your WHERE clause is comparing Unit to the Description fields in Unittype, instead of a code field. This seems a little odd; pls explain...
Thanks,
Jay
May 6, 2003 at 12:33 pm
Jay,
I am trying to compare a varchar field for values that do not exist in either a desc field or full desc field. It is intended to see how much data clean up will be necessary.Thank you for the snippet, I believe I am making simple things too complex!
cheers
May 6, 2003 at 2:50 pm
I think you are looking for something a little more like this.
SELECT
WorkHistory.Unit,
MIN(workhistory.travelerid) as Traveler
FROM
WorkHistory
LEFT JOIN
unittype
ON
Workhistory.Unit = UnitType.[Description] OR
Workhistory.Unit = UnitType.fullDescription
WHERE
(UnitType.NotNullPKColumn IS NULL)
GROUP BY
WorkHistory.Unit
If in UnitType table you don't have a Column that would not be NULL then instead do something like this
WHERE
COALESCE(UnitType.[Description],UnitType.fullDescription,NULL) IS NULL
However the MIN(Workhistory.travelerid) will only give you the smallest value like this not the first occurrance so you may instead want to do
SELECT
WorkHistory.Unit,
(
SELECT TOP 1
traveler.travelerid
FROM
Traveler
WHERE
traveler.travelerid =workhistory.travelerid
ORDER BY
traveler.travelerid --Would be better to order by a datetime or identity column to ensure order output
) as Traveler
FROM
WorkHistory
LEFT JOIN
unittype
ON
Workhistory.Unit = UnitType.[Description] OR
Workhistory.Unit = UnitType.fullDescription
WHERE
(UnitType.NotNullPKColumn IS NULL)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply