A complex 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)

A complex Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-04-25 : 14:08:22

White Knight writes "Senario I have to determine which Jobs are editable and which not
Those which are *not* editable have at least one entry in the page_transaction table, and By deduction those that are editable have NO enties in the page_transaction. Here be the rub really what I need is a count of the number of entries in the statement
'AND d.page_id in(select pglog_id from page_transaction
where pglog_id in (select page_id from Pagelog where job_id = 4862))' and if it is greater than 0 then set Not editable.

So far Not editable is correct, but I cannot get ediatble to work because the joins mean that some records in the Job_spec_details could have a record and some could not so it returns a false records.

Wow is that clear I'm not sure cos I been working on it for a while. ask for further info if you cannot understand and i will try to clarify.

SELECT Distinct 'N' as Editable, j.Action_item, j.applies_to, j.Action_required, j.pg_range, d.Job_id,d.specid
FROM job_spec j left join job_spec_detail d on j.id = d.specid
WHERE j.id in(SELECT id FROM Job_Spec WHERE job_id =4862)
AND d.page_id in(select pglog_id from page_transaction
where pglog_id in (select page_id from Pagelog where job_id = 4862))

Union
SELECT Distinct 'Y'as Editable, j.Action_item, j.applies_to, j.Action_required, j.pg_range, d.Job_id,d.specid
FROM job_spec j left join job_spec_detail d on j.id = d.specid
WHERE j.id in(SELECT id FROM Job_Spec WHERE job_id =4862)
AND d.page_id NOT in(select pglog_id from page_transaction
where pglog_id in(select page_id from Pagelog where job_id = 4862))
ORDER By j.Action_item"

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-28 : 12:01:37

quote:

Wow is that clear I'm not sure cos I been working on it for a while. ask for further info if you cannot understand and i will try to clarify.

Ah, No.

Can you try something like: I want all of the Red Apples that don't have worms?

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources