sql Help - 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 2000 Forums

SQL Server Development (2000)

sql Help

Author

Topic

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-01-31 : 16:15:41

I have a master table where I want to show all the records. I've joined to another Table [TOPS_GROUPING_DETAIL] Which has detail records to some but not all master records. The return table only is returning master records that have detail records associated to it.
Here is the query as is:

SELECT TOPS_GROUPING_MASTER.GroupingID, TOPS_GROUPING_DETAIL.GroupKeyID, TOPS_BOM_MASTER.MaterialNo,
TOPS_BOM_MASTER.MaterialDescription, TOPS_GROUPING_DETAIL.Constrained, TOPS_GROUPING_DETAIL.DefaultSelect
FROM TOPS_GROUPING_MASTER INNER JOIN
TOPS_BOM_MASTER ON TOPS_GROUPING_MASTER.MaterialID = TOPS_BOM_MASTER.MaterialNo INNER JOIN
TOPS_GROUPING_DETAIL ON TOPS_GROUPING_MASTER.GroupingID = TOPS_GROUPING_DETAIL.GroupingID
WHERE (TOPS_GROUPING_MASTER.GroupKeyID = '103')

MCP, MCSD

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 16:17:57

Try changing INNER JOIN TOPS_GROUPING_DETAIL to LEFT OUTER JOIN TOPS_GROUPING_DETAIL

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 16:18:50

[code]SELECT gm.GroupingID,
gd.GroupKeyID,
bm.MaterialNo,
bm.MaterialDescription,
gd.Constrained,
gd.DefaultSelect
FROM TOPS_GROUPING_MASTER AS gm
LEFT JOIN TOPS_BOM_MASTER AS bm ON bm.MaterialNo = gm.MaterialID
LEFT JOIN TOPS_GROUPING_DETAIL AS gd ON gd.GroupingID = gm.GroupingID
WHERE gm.GroupKeyID = '103'[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 16:31:00

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-01-31 : 16:32:15

Nah, not a Snipe. You've got LEFT OUTER JOIN on TOPS_BOM_MASTER - so you & I read that part of the question differently. No doubt points will be awarded for correct mind reading once again!

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-31 : 16:41:54

Mind-reading?
I blame copy & paste...

Peter Larsson
Helsingborg, Sweden

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-01-31 : 16:58:23

That worked!
How do you guys get points?

MCP, MCSD

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-01-31 : 17:36:20

I had to add 1 more Key in there an now I'm back to a single record again: Any Ideas?

sql = "SELECT gm.GroupingID, bm.MaterialNo, bm.MaterialDescription, gd.Constrained, gd.DefaultSelect " & _
"FROM TOPS_GROUPING_MASTER AS gm LEFT OUTER JOIN " & _
"TOPS_BOM_MASTER AS bm ON bm.MaterialNo = gm.MaterialID LEFT OUTER JOIN " & _
"TOPS_GROUPING_DETAIL AS gd ON gd.GroupingID = gm.GroupingID " & _
"WHERE(gm.GroupKeyID = '103' and LevelKey = '1.2.1.1')"

MCP, MCSD

Kristen
Test

22859 Posts

Posted - 2007-02-01 : 00:50:28

If LevelKey is NOT in the TOPS_GROUPING_MASTER table then you need to put it in the JOIN for the table it belongs to, not there WHERE clause

Kristen

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-01 : 08:08:31

It belongs to TOPS_GROUPING_DETAIL

MCP, MCSD

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-01 : 08:20:13

This worked:

sql = "SELECT gm.GroupingID, bm.MaterialNo, bm.MaterialDescription, gd.Constrained, gd.DefaultSelect " & _
"FROM TOPS_GROUPING_MASTER AS gm LEFT OUTER JOIN " & _
"TOPS_BOM_MASTER AS bm ON bm.MaterialNo = gm.MaterialID LEFT OUTER JOIN " & _
"TOPS_GROUPING_DETAIL AS gd ON gd.GroupingID = gm.GroupingID and gd.LevelKey = '" & grpNd.LevelKey & "' " & _
"WHERE(gm.GroupKeyID = '" & grpNd.GroupKey & "')"

thx for the advice.

MCP, MCSD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 08:36:20

Thanks for the feedback.

But you SHOULD REALLY NOT execute this kind of queries from front-end applications!
Make a stored procedure that accepts LevelKey and GroupKey as parameters.

CREATE PROCEDURE dbo.uspSomeCatchyNameHere
(
	@LevelKey VARCHAR(20),
	@GroupKey VARCHAR(20)
)
AS

SET NOCOUNT ON

SELECT		gm.GroupingID,
		bm.MaterialNo,
		bm.MaterialDescription,
		gd.Constrained,
		gd.DefaultSelect
FROM		TOPS_GROUPING_MASTER AS gm
LEFT JOIN	TOPS_BOM_MASTER AS bm ON bm.MaterialNo = gm.MaterialID
LEFT JOIN	TOPS_GROUPING_DETAIL AS gd ON gd.GroupingID = gm.GroupingID AND gd.LevelKey = @LevelKey
WHERE		gm.GroupKeyID = @GroupKey

And use this technique for calling the stored procedure
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70783

Peter Larsson
Helsingborg, Sweden

REEPER
Yak Posting Veteran

53 Posts

Posted - 2007-02-01 : 14:05:30

Is there a performance gain? I'm already 'parameterizing' the query.

MCP, MCSD

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 14:17:12

Oh yes, but you are sending the query as plain text to sql server.
Doing this, the SQL Server needs to parse syntax and create an execution plan for the query every time you run it.
When already stored in the database as a stored procedure, this is already done (after first run) and the query will return your results much faster!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-01 : 14:17:13

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources