help with a 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 2000 Forums

Transact-SQL (2000)

help with a query

Author

Topic

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-09-08 : 10:23:43

Hi,

I have two queries that I am trying to combine yet I am failing so far. Thus any help will be appreciated.

The first query returns labor time for a certain category of jobs (starting with I) and withing a certain time frame:

select prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate, Sum(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)) as mins 
from ladetail ,prempl where ladetail.fjobno like 'I%' 
and (ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'SAW' or ladetail.fpro_id = 'DRILL' or 
ladetail.fpro_id = 'MACH' or ladetail.fpro_id = 'SH/R/PB')
and ladetail.fempno=prempl.fempno and ladetail.fdate >= '2006-09-01' and ladetail.fdate <= '2006-09-07'
group by prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate
order by prempl.fname+prempl.ffname

here is the example of what it returns:
X X I0154-0000 2006-09-05 00:00:00.000 38
X X I0154-0000 2006-09-06 00:00:00.000 -8
X X I0154-0000 2006-09-07 00:00:00.000 20
X X I4827-0000 2006-09-06 00:00:00.000 338
X X I5023-0000 2006-09-07 00:00:00.000 553

In addition to this I also need to get the work times and names for the jobs that are returned by this query:

select jomast.fjobno
from somast,jomast
where somast.fsono = jomast.fsono and somast.fcusrchr3 = 'SHIPOUT'

I thought I could this as a subquery but that failed since the subquery returns more than one row.

how can I combine this into one query? Thanx

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-09-08 : 10:39:31

What happens if you do;

SELECT DISTINCT
     jomast.fjobno
FROM 
    somast,jomast
WHERE 
    somast.fsono = jomast.fsono and somast.fcusrchr3 = 'SHIPOUT'

as your sub? try it out! =)

when life hands you lemons, ask for tequila and salt

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-09-08 : 10:49:35

cannot do that because the subquery is not scalar. at least not they way I tried to do it

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 11:26:06

You need to put something into the subquery that refers to a column in the main query, so it would be something like

select jomast.fjobno
from somast,jomast
where somast.fsono = jomast.fsono and somast.fcusrchr3 = 'SHIPOUT'
AND somast.col = ladetail.col

That way the subquery specifically returns the value for the row associated with the row in the main query.

Note that almost always in a case like this you can simply join the table that you're using in the subquery into the main query so that you don't need a subquery at all - and its better to use a join if you can.

I'd also recommend using the INNER/OUTER JOIN keyword syntax rather than the syntax you're using (list of tables separated by commas) because it makes the query much easier to understand and is more standard too.

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-09-08 : 11:34:22

I have to admit that I don't fully understand your idea. From my point of view the main column is the fjobno column because I want data related to jobs within certain period of time. I honestly cannot see how to do what you suggest.
Note that I am obviously no expert at SQL so please bare with me.
Is there any way that you could rewrite the queries into one (the way you would do it) so that way I can test it and hopefully learn some new things as well

Thanx

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 11:59:58

OK, so then the jobno, is the column that you use between the two and your subquery becomes (I have no idea what your real column names for the "the work times and names for the jobs" are, so I just put jomast.jobname, somast.jobtime as examples)

(SELECT jomast.jobname, somast.jobtime
FROM somast, jomast
WHERE somast.fsono = jomast.fsono AND somast.fcusrchr3 = 'SHIPOUT'
AND jomast.jobno = ladetail.jobno)

In terms of using a single query with the JOIN syntax

SELECT <columns>
FROM ladetail
INNER JOIN prempl ON ladetail.fempno=prempl.fempno
INNER JOIN jomast ON jomast.jobno = ladetail.jobno
INNER JOIN somast ON somast.fsono = jomast.fsono
WHERE ladetail.fjobno like 'I%'
AND (ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'SAW' or ladetail.fpro_id = 'DRILL' or
ladetail.fpro_id = 'MACH' or ladetail.fpro_id = 'SH/R/PB')
AND ladetail.fdate >= '2006-09-01' and ladetail.fdate <= '2006-09-07' AND somast.fcusrchr3 = 'SHIPOUT'
GROUP BY <columns>
ORDER BY prempl.fname + prempl.ffname

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-09-08 : 12:18:25

here is the query as you design it yet it returns 0 rows:

SELECT prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate, Sum(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)) as mins
FROM ladetail
INNER JOIN prempl ON ladetail.fempno=prempl.fempno
INNER JOIN jomast ON jomast.fjobno = ladetail.fjobno
INNER JOIN somast ON somast.fsono = jomast.fsono
WHERE ladetail.fjobno like 'I%'
AND (ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'SAW' or ladetail.fpro_id = 'DRILL' or
ladetail.fpro_id = 'MACH' or ladetail.fpro_id = 'SH/R/PB')
AND ladetail.fdate >= '2006-09-01' and ladetail.fdate <= '2006-09-07' AND somast.fcusrchr3 = 'SHIPOUT'
GROUP BY prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate
ORDER BY prempl.fname + prempl.ffname

The reason for that is because a ladetail.fjobno which starts with I will NEVER be a SHIPOUT.
So the point is I want ALL the I-jobs in any given timeperiod AND ALL the jobs within the same period that were SHIPOUTs. I do not want I-jobs that were SHIPOUT as well for any given period of time.
So it is very easy to filter the I-jobs like I did in the first query, because they have the I in common, but as far the shipout jobs the only thing they have in common is the SHIPOUT label. I hope all this makes sense.
I'm not even sure if this can be done with a single query, so I am thinking about writing a little app with a nice little for loop, but it just seems like i should be able to pull this off with a query.
I don't know. just thinking out loud.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 14:45:52

OK, in that case you need something different, you don't want a subquery at all, that's why I misunderstood.

You can do that like this

SELECT prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate, Sum(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)) as mins
FROM ladetail
INNER JOIN prempl ON ladetail.fempno=prempl.fempno
INNER JOIN jomast ON jomast.fjobno = ladetail.fjobno
INNER JOIN somast ON somast.fsono = jomast.fsono
WHERE (ladetail.fjobno like 'I%' OR (somast.fcusrchr3 = 'SHIPOUT' AND ladetail.fjobno like 'I%'))
AND (ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'SAW' or ladetail.fpro_id = 'DRILL' or
ladetail.fpro_id = 'MACH' or ladetail.fpro_id = 'SH/R/PB')
AND ladetail.fdate >= '2006-09-01' and ladetail.fdate <= '2006-09-07'
GROUP BY prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate
ORDER BY prempl.fname + prempl.ffname

Will there be a jomast and a somast for every ladetail? If not, then you may need to change one or both of those INNER JOINs to LEFT OUTER JOINs.

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-09-08 : 16:02:08

ok that query does not return any rows either but now it will be easier to explain what is missing:
WHERE (ladetail.fjobno like 'I%' OR (somast.fcusrchr3 = 'SHIPOUT' AND ladetail.fjobno like 'I%'))
in the row above after the OR I would need a way to say OR ladetail.fjobno = (AND HERE WOULD BE every row returned from my second original query, which is this:

select jomast.fjobno
from somast,jomast
where somast.fsono = jomast.fsono and somast.fcusrchr3 = 'SHIPOUT'

does that make more sense?

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-09-08 : 16:06:58

ok after modifiying it a bit I think this works

SELECT prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate, Sum(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)) as mins
FROM ladetail
INNER JOIN prempl ON ladetail.fempno=prempl.fempno
INNER JOIN jomast ON jomast.fjobno = ladetail.fjobno
INNER JOIN somast ON somast.fsono = jomast.fsono
WHERE (ladetail.fjobno like 'I%' OR (somast.fcusrchr3 = 'SHIPOUT' AND ladetail.fjobno like 'I%'))
AND (ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'SAW' or ladetail.fpro_id = 'DRILL' or
ladetail.fpro_id = 'MACH' or ladetail.fpro_id = 'SH/R/PB')
AND ladetail.fdate >= '2006-09-01' and ladetail.fdate <= '2006-09-07'
GROUP BY prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate
ORDER BY prempl.fname + prempl.ffname

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 16:20:13

I don't see any differences, but I left out a crucial NOT, it should be

SELECT prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate, Sum(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)) as mins
FROM ladetail
INNER JOIN prempl ON ladetail.fempno=prempl.fempno
INNER JOIN jomast ON jomast.fjobno = ladetail.fjobno
INNER JOIN somast ON somast.fsono = jomast.fsono
WHERE (ladetail.fjobno like 'I%' OR (somast.fcusrchr3 = 'SHIPOUT' AND ladetail.fjobno NOT like 'I%'))
AND (ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'SAW' or ladetail.fpro_id = 'DRILL' or
ladetail.fpro_id = 'MACH' or ladetail.fpro_id = 'SH/R/PB')
AND ladetail.fdate >= '2006-09-01' and ladetail.fdate <= '2006-09-07'
GROUP BY prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate
ORDER BY prempl.fname + prempl.ffname

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-09-08 : 16:21:52

And I'm guessing you had to do this

SELECT prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate, Sum(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)) as mins
FROM ladetail
INNER JOIN prempl ON ladetail.fempno=prempl.fempno
LEFT OUTER JOIN jomast ON jomast.fjobno = ladetail.fjobno
LEFT OUTER JOIN somast ON somast.fsono = jomast.fsono
WHERE (ladetail.fjobno like 'I%' OR (somast.fcusrchr3 = 'SHIPOUT' AND ladetail.fjobno NOT like 'I%'))
AND (ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'SAW' or ladetail.fpro_id = 'DRILL' or
ladetail.fpro_id = 'MACH' or ladetail.fpro_id = 'SH/R/PB')
AND ladetail.fdate >= '2006-09-01' and ladetail.fdate <= '2006-09-07'
GROUP BY prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate
ORDER BY prempl.fname + prempl.ffname

ronin2307
Posting Yak Master

126 Posts

Posted - 2006-09-08 : 16:26:50

sorry :-) bad copy and paste
here it is

SELECT prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate, Sum(datediff(minute,ladetail.fsdatetime,ladetail.fedatetime)) as mins
FROM ladetail
INNER JOIN prempl ON ladetail.fempno=prempl.fempno
inner JOIN jomast ON jomast.fjobno = ladetail.fjobno
left outer join somast ON somast.fsono = jomast.fsono
WHERE (ladetail.fjobno like 'I%' OR somast.fcusrchr3 = 'SHIPOUT')
AND (ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'RED' or ladetail.fpro_id = 'SAW' or ladetail.fpro_id = 'DRILL' or
ladetail.fpro_id = 'MACH' or ladetail.fpro_id = 'SH/R/PB')
AND ladetail.fdate >= '2006-09-01' and ladetail.fdate <= '2006-09-07'
GROUP BY prempl.fname,prempl.ffname,ladetail.fjobno,ladetail.fdate
ORDER BY prempl.fname + prempl.ffname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources