i need to get only the first row from one of the joins in a multiple join
query.

i am join several tables to the main table.
all but one of the joins are simply replacing key values with text.
the last join may find several rows is is causing a row to be displayed for
eahc value,
i only need to get the first row.

syntax please.

tia,
mcnewsxp

here is sample of join that requires doctoring:

LEFT JOIN LENNON.V2_T1.dbo.Foods as I
ON B.ID = I.ID
LEFT JOIN LENNON.V2_T1.dbo.LookupFoodTypes as H
ON I.FoodCategoryID = H.FoodCategoryID

B is the main table.

The first row - what is that?  You'll need to add an ORDER BY clause to
ensure the proper ordering of the records for your "first row".  You can use
the TOP 1 clause in the SELECT statment or SET ROWCOUNT.

HTH

Jerry
Show quoteHide quote

first row from the of the joined table, not the main table.
i need it to be in the as entered order.

Same approach.

Show quoteHide quote

set rowcount 1 and top1 give me only the first row from the main table.
i need all rows from the main table, but only the first row from the joined
table.

Then join to nested query using the TOP 1 record from the joined table.
Show quoteHide quote

can you give example?
here is what i have:

truncate table tblInsertedInto
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
INSERT INTO tblInsertedInto (State, obMonth, obYear, Count, Vehicle,
Location, Comments, KEYID)
   SELECT D.StateName, month(B.FirstCount), year(B.FirstCount),
B.EstimatedTotal,
H.FoodCategoryName, F.WhereEatenLabel, B.Remarks, B.KEYID
   FROM LENNON.AFORS.dbo.InternalCloseout as A
   JOIN LENNON.V2_T1.dbo.GeneralEFORSMain as B
    ON A.KEYID = B.KEYID

    LEFT JOIN LENNON.V2_T1.dbo.GeneralState as E
ON B.KEYID = E.KEYID
LEFT JOIN LENNON.V2_T1.dbo.LookupState as D
ON E.StateID = D.StateID

    LEFT JOIN LENNON.V2_T1.dbo.GeneralWhereEaten as G
ON B.KEYID = G.KEYID
LEFT JOIN LENNON.V2_T1.dbo.LookupWhereEaten as F
ON G.WhereEatenID = F.WhereEatenID

    --This is the join that where i need only the first row
LEFT JOIN LENNON.V2_T1.dbo.GeneralImplicatedFood as I
ON B.KEYID = I.KEYID
LEFT JOIN LENNON.V2_T1.dbo.LookupFoodCategories as H
ON I.FoodCategoryID = H.FoodCategoryID

LEFT JOIN tblInsertedInto
    ON B.KEYID = tblInsertedInto.KEYID
WHERE tblInsertedInto.KEYID IS NULL AND E.Reporting = 1 ;

Try something like this:

USE PUBS
GO

SELECT TITLE
FROM   TITLES T JOIN TITLEAUTHOR TA
ON     T.TITLE_ID =TA.TITLE_ID
JOIN   (SELECT TOP 1 AU_ID FROM AUTHORS ORDER BY AU_ID) A
ON     TA.AU_ID = A.AU_ID

HTH

Jerry
Show quoteHide quote

tried your example and got in correct syntax on "ON"
LEFT JOIN LENNON.EFORS_V2_T1.dbo.GeneralImplicatedFood as I
ON B.EFORSCDCID = I.EFORSCDCID
LEFT JOIN (SELECT TOP 1 H.FoodCategoryName
FROM LENNON.EFORS_V2_T1.dbo.LookupFoodCategories AS H)
ON I.FoodCategoryID = H.FoodCategoryID

Try this:

LEFT JOIN LENNON.EFORS_V2_T1.dbo.GeneralImplicatedFood as I
ON B.EFORSCDCID = I.EFORSCDCID
LEFT JOIN (SELECT TOP 1 H.FoodCategoryName
FROM LENNON.EFORS_V2_T1.dbo.LookupFoodCategories) AS H  --moved paren here
ON I.FoodCategoryID = H.FoodCategoryID

HTH

Jerry
Show quoteHide quote

yeah, i tried that a got
"The column prefix 'H' does not match with a table name or alias name used
in the query."

Well one issue you're selecting FoodCategoryName and then joining on
FoodCategoryID.  Also, try removing the H. after TOP 1.  In addition add an
ORDER BY clause after .LookupFoodCategories so SQL Server will know what
record you want returned i.e., it wont be a random record.

Try this:

(SELECT TOP 1 FoodCategoryID
HTH

Jerry

Show quoteHide quote

well that get's my query running again, but i am still getting the same
number of total rows.
maybe i can tweak it into shape.
i'll let you knwo if i get working.
thanks for your help.

Unknown Columns!
Simple query?
order by
Can someone explain why this is not an error?
TOP Operator - Variable?