This works, but... - 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

General SQL Server Forums

New to SQL Server Programming

This works, but...

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-04-08 : 12:25:32

I am going to tangle with our ERP system for the rest of my natural life, and in so doing, will need to create new SQL queries on an almost-daily basis. Based on the handful of queries that I have created against it so far, I recognize that I need to get better at authoring them.

Towards that end, I was wondering if anyone with a little firmer grasp on the subject could take a look at this query (which works & does exactly what I need it to do) and make suggestions as to what I might have done better/differently.

I've changed the names of everything to make it more comprehensible (I hope).

The query returns one row for each unique record in DetailTable, with data from the SummaryTable and a PartDescription from ThirdTable.

SELECT 		SummaryTable.RecordID, 
		SummaryTable.Status, 
		SummaryTable.CustomerName, 
		SummaryTable.CustomerNumber,
		SummaryTable.OrderNumber,
		SummaryTable.AssignedTo, 
		SummaryTable.ResolvedBy, 
		SummaryTable.ResolveDate, 
 		SummaryTable.PartNumber, 
		SummaryTable.PartRevision, 
		SummaryTable.OrderQuantity, 
		SummaryTable.IssueCategory,
		SummaryTable.IncidentDate,  
		SummaryTable.InquiryDate,
		SummaryTable.IssueClass,
		SummaryTable.Severity, 
		SummaryTable.IssueNumber,
		SummaryTable.AuthorizedBy, 
		SummaryTable.Facility, 
		DetailTable.AssignedTo, 
		DetailTable.ActionDate, 
		DetailTable.ActionBy, 
		DetailTable.JobNumber, 
		DetailTable.ActionTaken,
		DetailTable.NextAction, 
		DetailTable.IncidentNotes, 
		ThirdTable.PartDescription

FROM		theDatabase.dbo.DetailTable 
		LEFT JOIN
			theDatabase.dbo.SummaryTable ON DetailTable.RecordID=SummaryTable.RecordID
		LEFT JOIN 
			theDatabase.dbo.ThirdTable on SummaryTable.PartNumber = ThirdTable.PartNumber 
			AND SummaryTable.PartRevision = ThirdTable.PartRevision

WHERE  		(SummaryTable.IssueCategory='1' OR SummaryTable.IssueCategory='2' OR SummaryTable.IssueCategory='3' 
		OR SummaryTable.IssueCategory='D' OR SummaryTable.IssueCategory='E' OR SummaryTable.IssueCategory='L' 
		OR SummaryTable.IssueCategory='O' OR SummaryTable.IssueCategory='R' OR SummaryTable.IssueCategory='S' 
		OR SummaryTable.IssueCategory='V' OR SummaryTable.IssueCategory='X' OR SummaryTable.IssueCategory='Z') 
		AND SummaryTable.Facility='Default' AND SummaryTable.Status='OPEN'

ORDER BY 	SummaryTable.RecordID

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 12:38:36

Given that SummaryTable is in every expression in the where clause it means it cannot be null and so the first left join is actually an inner join - it will make the system more difficult to maintain if you code it like this.

If you want to include every row from DetailTable and there are some that don't have entries in the SummaryTable rows then the where clause should be part of the left join clause - but that would mean the query doesn't work and so can't be the case.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 12:44:46

[code]SELECT st.RecordID,
st.Status,
st.CustomerName,
st.CustomerNumber,
st.OrderNumber,
st.AssignedTo,
st.ResolvedBy,
st.ResolveDate,
st.PartNumber,
st.PartRevision,
st.OrderQuantity,
st.IssueCategory,
st.IncidentDate,
st.InquiryDate,
st.IssueClass,
st.Severity,
st.IssueNumber,
st.AuthorizedBy,
st.Facility,
dt.AssignedTo,
dt.ActionDate,
dt.ActionBy,
dt.JobNumber,
dt.ActionTaken,
dt.NextAction,
dt.IncidentNotes,
tt.PartDescription
FROM theDatabase.dbo.DetailTable AS dt
LEFT JOIN theDatabase.dbo.SummaryTable AS st ON st.RecordID = dt.RecordID
AND st.IssueCategory IN ('1', '2', '3', 'D', 'E', 'L', 'O', 'R', 'S', 'V', 'X', 'Z')
AND st.Facility = 'Default'
AND st.Status = 'OPEN'
LEFT JOIN theDatabase.dbo.ThirdTable AS tt ON tt.PartNumber = st.PartNumber
AND tt.PartRevision = st.PartRevision
ORDER BY dt.RecordID[/code]

E 12°55'05.25"
N 56°04'39.16"

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-04-08 : 14:02:10

nr - thanks; I swapped DetailTable and SummaryTable around in the first LEFT JOIN (joining DetailTable to SummaryTable) and it did not alter the results; it makes more sense considering the WHERE clause is almost completely dependent on SummaryTable.

Peso - thanks, also. I tried that approach, and the query ran, but for some reason is ignoring 'AND st.Status='OPEN' - if I grab TOP 100 with it, it returns records with a Status of 'CLOSED'. I'll tinker with it some more here & make sure I didn't overfinger something. But, I'd never attempted a join without a WHERE clause, that's pretty interesting (to me) that it works like that.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 14:22:09

As I pointed out moving the filter to the left join clause will include all rows from DetailTable.
I think you probably want

FROM theDatabase.dbo.DetailTable AS dt
JOIN theDatabase.dbo.SummaryTable AS st
ON st.RecordID = dt.RecordID
AND st.IssueCategory IN ('1', '2', '3', 'D', 'E', 'L', 'O', 'R', 'S', 'V', 'X', 'Z')
AND st.Facility = 'Default'
AND st.Status = 'OPEN'
LEFT JOIN theDatabase.dbo.ThirdTable AS tt
ON tt.PartNumber = st.PartNumber
AND tt.PartRevision = st.PartRevision
ORDER BY dt.RecordID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-04-08 : 14:42:47

quote:
Originally posted by nr

As I pointed out moving the filter to the left join clause will include all rows from DetailTable.
I think you probably want

FROM theDatabase.dbo.DetailTable AS dt
JOIN theDatabase.dbo.SummaryTable AS st
ON st.RecordID = dt.RecordID
AND st.IssueCategory IN ('1', '2', '3', 'D', 'E', 'L', 'O', 'R', 'S', 'V', 'X', 'Z')
AND st.Facility = 'Default'
AND st.Status = 'OPEN'
LEFT JOIN theDatabase.dbo.ThirdTable AS tt
ON tt.PartNumber = st.PartNumber
AND tt.PartRevision = st.PartRevision
ORDER BY dt.RecordID

That worked quite well! So does all this mean that I should try not to use WHERE if I can translate it to a condition of the JOIN, and when I feel an '... OR ...' coming on, use IN?

(BTW, I very much appreciate the time you have taken to look at this for me).

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 14:56:14

An "in" statement means less typing and that's always good - is more readable as well.

As to whether it should go in the where clause or the join it's a matter of preference.
I tend to put things in the where clause if they are filtering the rows and in the join if they are selecting from other tables. It's not always clear cut though.

In this case ST looks like the driving table so I would put that first.
I would put the filters in the where clause and just the joins to other tables in the joins
so


From theDatabase.dbo.SummaryTable AS st
    Join theDatabase.dbo.DetailTable AS dt
        ON st.RecordID = dt.RecordID
    LEFT JOIN theDatabase.dbo.ThirdTable AS tt
        ON tt.PartNumber = st.PartNumber
        AND tt.PartRevision = st.PartRevision
Where st.IssueCategory IN ('1', '2', '3', 'D', 'E', 'L', 'O', 'R', 'S', 'V', 'X', 'Z') 
AND st.Facility = 'Default'
AND st.Status = 'OPEN'
ORDER BY st.RecordID

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources