query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query

Author

Topic

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-19 : 15:03:27

hello all,

I am having a heck of a time getting a query to work.

Here's the situation:

I have 3 tables

Styles
-----------
ID
StyleID
Name
Notes

Operations
-----------
ID
Number
Cost

StyleOperationJoin
------------------
StyleID
OperationID

What I am trying to do is get All of the operations associated with ONE style.

So if I enter a style parameter eg. T7000, I should get n operations.

HERE is my original code:

ALTER PROCEDURE dbo.GetOperationsByStyle
(
@styleID nvarchar(50)
)
AS
SELECT * FROM Operations
INNER JOIN StyleOperationJoin ON StyleOperationJoin.StyleID=@styleID
WHERE StyleOperationJoin.OperationID=Operations.ID
RETURN

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-19 : 16:17:34

My base table has three rows that looks like this:

StyleID | OperationID
--------------------------
T9000 | op1
T9000 | op2
T9000 | op3
T9001 | op1

So, based on entering T9000, I want to get the following results

StyleID | OperationID | OperationName
------------------------------------------
T9000 | op1 | Operation #1
T9000 | op2 | Operation #2
T9000 | op3 | Operation #3
Again, thanks for any help!

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-19 : 17:20:13

I got some suggestions, but neither of them returned any rows:

suggestion 1:

select S.StyleID, O.ID, O.Number, O.[Name],O.Cost
from Styles S, Operations O, StyleOperationJoin J
where J.StyleID = S.StyleID and J.OperationID = O.ID
and S.StyleID =@styleID

suggestion 2:

SELECT o.ID, o.Name,o.Number, o.Cost
FROM Operations o
INNER JOIN StyleOperationJoin j
ON o.ID = j.OperationID
INNER JOIN Styles s
ON s.ID = j.StyleID
WHERE s.StyleID = @styleID

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-19 : 17:22:37

Do you have data in all three tables? And does that data have data that will link...aka find an id that matches for the other table(s)?

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-19 : 17:31:20

Yes, I have data in all three tables.

The Styles table has 1 row.

The StyleOperationJoin Table has three rows.

The Operations Table has 17 rows.

The StyleOperationJoin table has a StyleID column and a OperationID column.

The ID column in the Style table should be linking with the StyleID column while the ID column in the Operations table should be linking with the OperationID column in the StyleOperationJoin table.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-19 : 17:38:43

Try taking the "WHERE s.StyleID = @styleID" out and see if you get any rows back. If you still don't then you have to backtrack. Select * from each table seperately and make sure you have joining data. If you still have trouble, post your exact data for each table.

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-19 : 17:48:26

Fixed it! There was a mismatch in my parameter @styleID and the actual styleID in the table!

Thanks for your help!

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-19 : 17:50:17

NP. I knew it had to be a data issue (or the @variable wasn't set correctly).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources