sql server programming: Difficult query -- sql server programming

Re: Difficult query

Ramnadh
6/9/2004 11:55:01 PM

HI,
Thanx for ur reply

There is another thing i doesn't remind to tell you, i am giving it with example data

Table A :
EmpId SystemId
1 11
1 4
1 10
1 5
1 2
2 11
2 4
3 4
3 11

Table B :
SystemId
11
4

Table C :
EmpId SystemId ImpSystemId IsCompleted
1 11 1 1
1 4 1 0
1 10 0 0
2 4 1 1
2 11 1 0
3 11 1 1

The result Set should be :

EmpId SystemId
1 4
2 11
3 4

Means the systems in the Table B should contain compulsarily in the Table A and the EmpId & SystemId having ImpSystemId = 1 and Iscompleted = 1 should ignore.

[quoted text, click to view]

Re: Difficult query

oj
6/10/2004 12:59:35 AM

That would do nicely. Tks. ;-)

SELECT *
FROM A JOIN B ON A.SystemId=B.SystemId
LEFT JOIN C ON A.EmpId=C.EmpId AND A.SystemId=C.SystemId
AND (C.ImpSystemId & C.IsCompleted) = 1
WHERE C.EmpId IS NULL

[quoted text, click to view]

Re: Difficult query

Ramnadh
6/10/2004 2:48:01 AM

Sorry, It is not working.

It is still getting the rows in which the

ImpSystemId = 1 and IsCompleted = 1

[quoted text, click to view]

Re: Difficult query

Ramnadh
6/10/2004 4:28:01 AM

it's almost worked fine...i also tried the same query but

The ImpSystemId can be 0,1,2 etc...it is not the bit value.

so we have to compare just by only ImpSystemId = 1 or ImpSystemId != 1

[quoted text, click to view]

Re: Difficult query

David Portas
6/10/2004 7:03:42 AM

You could try this:

SELECT empid, systemid
FROM TableA AS A
WHERE EXISTS
(SELECT *
FROM TableB
WHERE systemid = A.systemid)
AND NOT EXISTS
(SELECT *
FROM TableC
WHERE systemid = A.systemid
AND empid = A.systemid
AND impsystemid = 1
AND iscompleted = 1)

Of course there's probably an easier, faster method using JOINs but since
you didn't post any DDL we don't know what the primary keys, foreign keys
and constraints are so we can only guess...

--
David Portas
SQL Server MVP
--

Re: Difficult query

David Portas
6/10/2004 7:08:37 AM

Perhaps you meant:

SELECT *
FROM A JOIN B ON A.SystemId=B.SystemId
LEFT JOIN C ON A.EmpId=C.EmpId AND A.SystemId=C.SystemId
AND C.ImpSystemId = 1 AND C.IsCompleted = 1
WHERE C.EmpId IS NULL

:)

--
David Portas
SQL Server MVP
--

Re: Difficult query

oj
6/10/2004 10:03:13 AM

Try:

SELECT *
FROM A JOIN B ON A.SystemId=B.SystemId
LEFT JOIN C ON A.EmpId=C.EmpId AND A.SystemId=C.SystemId
AND C.ImpSystemId = 1 AND C.IsCompleted = 1
WHERE C.EmpId IS NULL

or

SELECT *
FROM A JOIN B ON A.SystemId=B.SystemId
LEFT JOIN C ON A.EmpId=C.EmpId AND A.SystemId=C.SystemId
AND (C.ImpSystemId & C.IsCompleted) = 1
WHERE C.EmpId IS NULL

[quoted text, click to view]
Table A and the EmpId & SystemId having ImpSystemId = 1 and Iscompleted = 1
should ignore.
[quoted text, click to view]

Re: Difficult query

Ramnadh
6/10/2004 11:33:02 AM

Thanx for helping me.

The query doesn't get the correct results. As you are left joining with the table C it will get the all the rows if the particular values exists or not exists in the table C.

And another thing is that

The ImpSystemId can be 0,1,2 etc...it is not the bit value.

so we have to compare just by only ImpSystemId = 1 or ImpSystemId != 1

it is specified in the previous replies.

[quoted text, click to view]

Re: Difficult query

oj
6/10/2004 11:44:09 AM

Have you tried the queries. They work correctly with your posted sample
data.
Perhaps, this is what you're after.

SELECT *
FROM A JOIN B ON A.SystemId=B.SystemId
JOIN C ON A.EmpId=C.EmpId AND A.SystemId=C.SystemId
AND (C.ImpSystemId !=1 or C.IsCompleted !=1)

[quoted text, click to view]
the table C it will get the all the rows if the particular values exists or
not exists in the table C.
[quoted text, click to view]

Re: Difficult query

Ramnadh
6/10/2004 12:15:01 PM

HI,
Thanx for your reply.

Now it is getting fine. Thanx for your coordination.

Regards,
Ramnadh.

[quoted text, click to view]

Re: Difficult query

Partha Mandayam
6/10/2004 12:41:32 PM

SELECT a.* FROM TableA a INNER JOIN TableB b ON a.SystemID = b.SystemID
INNER JOIN TableC on b.SystemID = c.SystemID AND (c.ImpSystemID <> 1 OR
c.IsCompleted <> 1)

Regards

Partha Mandayam
Software Consultant
Home page: http://partha.tripod.com

*** Sent via Devdex http://www.devdex.com ***

Re: Difficult query

Peter Yeoh
6/10/2004 3:37:57 PM

SELECT a.* FROM TableA a INNER JOIN TableB b ON a.SystemID = b.SystemID
INNER JOIN TableC on b.SystemID = c.SystemID AND c.ImpSystemID <> 1 AND
c.IsCompleted <> 1

Peter Yeoh
http://www.yohz.com
Need smaller backups? Try MiniSQLBackup

[quoted text, click to view]
A and the EmpId & SystemId having ImpSystemId = 1 and Iscompleted = 1 should
ignore.
[quoted text, click to view]

Re: Difficult query

Peter Yeoh
6/10/2004 6:33:52 PM

My bad, should've been

SELECT a.* FROM TableA a INNER JOIN TableB b ON a.SystemID = b.SystemID
INNER JOIN TableC on b.SystemID = c.SystemID AND (c.ImpSystemID = 0 OR
c.IsCompleted = 0)

Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.