June 21, 2011 at 1:54 pm
SELECT Customers.SalesRep
, Jobs.JNr
, Mov.Type
, Mov.Date
, Ch.Type
FROM Customers
INNER
JOIN Jobs
on Jobs.Acc = Customers.Acc
AND Jobs.JNr NOT IN ('54','49')
INNER
JOIN JobsMov
on JobsMov.JNr = Jobs.JNr
INNER
JOIN Mov
on Mov.MNr = JobsMov.MNr
AND Mov.Type <> 'imp'
LEFT OUTER
JOIN ( SELECT Substring(Cost,4,7) AS JNr
FROM Ch
WHERE Type = 'Cust' ) AS not_these
ON not_these.JNr = Jobs.JNr
LEFT OUTER
JOIN Ch
ON Substring(Ch.Cost,4,7) = Jobs.JNr
WHERE Customers.SalesRep = 'Ara'
AND not_these.JNr IS NULL
The records are:
JNr Type
24 Cust
24 A
24 A
25 A
25 A
25 A
25 A
26 Cust
27 A
27 A
Can anybody explain me step by step what this query does? Thanks
June 21, 2011 at 2:01 pm
that is a rather braod request. do you have a specific question?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
June 21, 2011 at 2:14 pm
Are you asking for what the different clauses of the query do? As in "What does SELECT do? And what does FROM do?"
If so, then your best bet is Google "t-sql select" and dig into the documentation.
If you know how Select statements work, and need to know the specific purpose of that specific query, that'll be business-specific, and you'll need to talk to someone where you work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 21, 2011 at 2:34 pm
Hi looking at your query would i be correct in saying that the results that are returned are not what you expected?
its always easier to work out what your query is doing if you have some representative data. However looking at your query it appears to me that your query is negating the first left outer join and this has become an INNER join because you have filered on collumn from the table your left outer joining in the where section
try altering your query to
SELECT Customers.SalesRep
, Jobs.JNr
, Mov.Type
, Mov.Date
, Ch.Type
FROM Customers
INNER JOIN Jobs on Jobs.Acc = Customers.Acc
AND Jobs.JNr NOT IN ('54','49')
INNER JOIN JobsMov on JobsMov.JNr = Jobs.JNr
INNER JOIN Mov on Mov.MNr = JobsMov.MNr
AND Mov.Type <> 'imp'
LEFT OUTER JOIN ( SELECT Substring(Cost,4,7) AS JNr
FROM Ch
WHERE Type = 'Cust' ) AS not_these ON not_these.JNr = Jobs.JNr
AND not_these.JNr IS NULL
LEFT OUTER JOIN Ch ON Substring(Ch.Cost,4,7) = Jobs.JNr
WHERE Customers.SalesRep = 'Ara'
***The first step is always the hardest *******
June 21, 2011 at 2:34 pm
Sorry
More I'm interested what retrieves the first LEFT OUTER in the table not_these;
LEFT OUTER
JOIN ( SELECT Substring(Cost,4,7) AS JNr
FROM Ch
WHERE Type = 'Cust' ) AS not_these
ON not_these.JNr = Jobs.JNr
and then what does the second LEFT OUTER
LEFT OUTER
JOIN Ch
ON Substring(Ch.Cost,4,7) = Jobs.JNr
WHERE Customers.SalesRep = 'Ara'
AND not_these.JNr IS NULL
left outer is when you have 2 tables customers orders and you link them with left outer join by let's say c_id and you obtain customers that have orders+customers that don't have orders.
I am very beginner in stored procedures.
June 22, 2011 at 8:37 am
Ah! Now that's a question I can answer.
The left outer join finds rows that have a match in the second query, AND keeps rows in the first query that don't have a match.
For example:
SELECT *
FROM (SELECT 1 AS Col1
UNION ALL
SELECT 2) AS Table1
LEFT OUTER JOIN (SELECT 1 AS Col2) AS Table2
ON Col1 = Col2 ;
You'll see that Col1 has 1 and 2 in it, but Col2 only has 1 in it, but the query returns all the rows from Table1 because it's a Left Outer Join instead of an Inner Join. Change it to Inner, and you'll only get the row where there's a match.
The Where clause, by limiting it to rows where there's a NULL in the Outer table, means "Just give me the rows that don't have a match". Run the example above, and you'll quickly see how that works.
It's a pretty standard trick. If you do much T-SQL, you'll end up using it now and again. Definitely worth learning.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 22, 2011 at 9:13 am
so definitely saying WHERE Type = 'Cust' means the query retrives records when WHERE Type <> 'Cust'?
June 22, 2011 at 9:27 am
The records are:
JNr Type
24 Cust
24 A
24 A
25 A
25 A
25 A
25 A
26 Cust
27 A
27 A
so will be
JNr Type
25 A
25 A
25 A
25 A
27 A
27 A
I am at home and I can't test it.
June 24, 2011 at 10:07 am
codrutza_m (6/22/2011)
so definitely saying WHERE Type = 'Cust' means the query retrives records when WHERE Type <> 'Cust'?
It will ignore any that have a match in that sub-query, so essentially yes.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 1, 2011 at 6:44 am
Thank you very much
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply