Query Problem ( INNER JOIN)
-
Hey, Guys Let me just lay out the problem: I have 2 tables table Function FID FName Table Materials MID MName FunctionID1 (foriegn key for FID) FunctionID2 (foriegn key for FID) Now I want to select all columns from Materials table, instead of FunctionID1 and FunctionID2, I want to have their related text (FName).
SELECT M.MID, M.MName, F.FName, F.Fname FROM Materials AS M INNER JOIN Function AS F
ON FunctionID1=F.FID OR FunctionID2=F.FID
WHERE FunctionID1 = X(input) and FunctionID2 = X(input)I want this query to search for records in which FunctionID 1 is The input or FunctionID2 is equal to related input!!!! buy when I do this, It fetches two rows for a row and in each one FunctionID1 and FunctionID2 are the same ! How should I fix this???
-
Hey, Guys Let me just lay out the problem: I have 2 tables table Function FID FName Table Materials MID MName FunctionID1 (foriegn key for FID) FunctionID2 (foriegn key for FID) Now I want to select all columns from Materials table, instead of FunctionID1 and FunctionID2, I want to have their related text (FName).
SELECT M.MID, M.MName, F.FName, F.Fname FROM Materials AS M INNER JOIN Function AS F
ON FunctionID1=F.FID OR FunctionID2=F.FID
WHERE FunctionID1 = X(input) and FunctionID2 = X(input)I want this query to search for records in which FunctionID 1 is The input or FunctionID2 is equal to related input!!!! buy when I do this, It fetches two rows for a row and in each one FunctionID1 and FunctionID2 are the same ! How should I fix this???
If the content of the rows is the same DISTINCT might be useful. Using OR in a join statement is always going to cause you problems. You may also want to use sub selects, do your initial filtering in the from (select) Select * from (Select * from Function where Id1 = @ID1) X
Never underestimate the power of human stupidity RAH
-
Hey, Guys Let me just lay out the problem: I have 2 tables table Function FID FName Table Materials MID MName FunctionID1 (foriegn key for FID) FunctionID2 (foriegn key for FID) Now I want to select all columns from Materials table, instead of FunctionID1 and FunctionID2, I want to have their related text (FName).
SELECT M.MID, M.MName, F.FName, F.Fname FROM Materials AS M INNER JOIN Function AS F
ON FunctionID1=F.FID OR FunctionID2=F.FID
WHERE FunctionID1 = X(input) and FunctionID2 = X(input)I want this query to search for records in which FunctionID 1 is The input or FunctionID2 is equal to related input!!!! buy when I do this, It fetches two rows for a row and in each one FunctionID1 and FunctionID2 are the same ! How should I fix this???
How about using two joins like this:
SELECT M.MID, M.MName, F1.FName, F2.Fname
FROM Materials AS M
INNER JOIN Function AS F1 ON M.FunctionID1=F1.FID
INNER JOIN Function AS F2 ON M.FunctionID2=F2.FID
WHERE FunctionID1 = X(input) and FunctionID2 = X(input)Regards, Syed Mehroz Alam
My Blog My Articles Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
-
Hey, Guys Let me just lay out the problem: I have 2 tables table Function FID FName Table Materials MID MName FunctionID1 (foriegn key for FID) FunctionID2 (foriegn key for FID) Now I want to select all columns from Materials table, instead of FunctionID1 and FunctionID2, I want to have their related text (FName).
SELECT M.MID, M.MName, F.FName, F.Fname FROM Materials AS M INNER JOIN Function AS F
ON FunctionID1=F.FID OR FunctionID2=F.FID
WHERE FunctionID1 = X(input) and FunctionID2 = X(input)I want this query to search for records in which FunctionID 1 is The input or FunctionID2 is equal to related input!!!! buy when I do this, It fetches two rows for a row and in each one FunctionID1 and FunctionID2 are the same ! How should I fix this???
Actually, I think you'd want to use two
LEFT OUTER JOINS, as you'll want to return a match onFunctionID1orFunctionID2(unless I've read this all incorrectly):select M.MID, M.MName, F1.FName [F1Name], F2.FName [F2Name]
from Materials M
left outer join Functions F1 on F1.FID = M.Function1ID
left outer join Functions F2 on F2.FID = M.Function2ID
where F1.FID is not null or F2.FID is not nullThe
WHEREclause will elimitate records where neither theFunction1IDnorFunction2IDmatch the input value. And the doubleLEFT OUTER JOINs will cause records where either theFunction1IDorFunction2IDmatch the supplied input value to be pulled.Hope in one hand and poop in the other; see which fills up first. Hope and change were good slogans, now show us more than words.