better sp to get unmatched records?

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

hi
am having two tables tblProperty and tblSales
tblProperty contains this values in perticular field
Machinery
Furniture
Flat
Land

i created page in sales and am using property popup in sales so property value saving to tblsles as tblProperty tables primary key value.
tblSales contains
machinery
Furniture

now i want to get only Flat and Land which is not exist in tblSales

can any one help me how to do this?

Thank you.

Posted 5-Feb-13 17:26pm

ythisbug

Updated 8-Feb-13 2:37am

v2

Add a Solution

Comments

PIEBALDconsult 5-Feb-13 23:34pm

I'de use a left outer join.

ythisbug 5-Feb-13 23:41pm

that ok but how to get unmatched records..inleft outer join will get all records rite..i want non repeated records in second table. i.e tblSales.

ythisbug 8-Feb-13 8:34am

ALTER PROCEDURE [dbo].[usp_RptVacantPropertyByUnits]
@PropertyID int=null,
@DateFrom datetime=null,
@DateTo datetime=null
AS BEGIN
if @PropertyID is null
Begin
SELECT tblContractDetails.*,tblContractHeader.*,tblAssetRegister.*
FROM
tblContractDetails
INNER JOIN
tblContractHeader ON tblContractDetails.cdeDID = tblContractHeader.chDID AND
tblContractDetails.cdeDocType = tblContractHeader.chDocType

left outer join tblAssetRegister on tblContractHeader.chPropertyID=tblAssetRegister.asID
WHERE (@DateFrom IS NULL OR
tblContractHeader.chDocumentDateTime >= @DateFrom) AND (@DateTo IS NULL OR
tblContractHeader.chDocumentDateTime <= @DateTo)

AND tblContractHeader.chIsDelete=0 and tblAssetRegister.asDocType='PRE1' and tblAssetRegister.asID is null
ORDER BY tblContractHeader.chDocumentDateTime

End else if @PropertyID is not null
Begin
SELECT tblContractDetails.*,tblContractHeader.*,tblAssetRegister.*
FROM
tblContractHeader
INNER JOIN
tblContractDetails ON tblContractHeader.chDID = tblContractDetails.cdeDID AND tblContractHeader.chDocType = tblContractDetails.cdeDocType
left outer join tblAssetRegister on tblContractHeader.chPropertyID=tblAssetRegister.asID

WHERE

tblContractHeader.chIsDelete=0 and tblAssetRegister.asID is null and tblContractHeader.chPropertyID in(select asID from tblAssetRegister where asID=@PropertyID and tblAssetRegister.asDocType='PRE1')
ORDER BY tblContractHeader.chDocumentDateTime

End
END

this is my query but am not getting records.. can any one suggest me wats the issue..
thank you

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Naveen.Sanagasetti · Answer 1 · 2013-02-05T18:12:00

I think this might be helpful to you...

http://stackoverflow.com/questions/723195/should-i-use-or-for-not-equal-in-tsql[^]
http://msdn.microsoft.com/en-us/library/ms190286.aspx[^]

SQL

SELECT COL_NAME FROM TABEL1 A, TABLE2 B
WHERE A.ID <> B.ID

i'm not sure this is correct , i think this is satisfied your question...

Ankur^\m/ · Answer 2 · 2013-02-05T18:43:00

Method 1: Get a resultset with common 'Furniture' and 'Machinery' and use that in a subquery to exclude those results.

SQL

SELECT Flat, Land FROM tblProperty
WHERE Furniture NOT IN 
(SELECT Furniture FROM tblProperty p
INNER JOIN tblSales s ON p.Furniture = s.Furniture AND p.Machinery = s.Machinery)

Method 2: Simply using NOT IN clause in the query.

SQL

SELECT Flat, Land FROM tblProperty
WHERE Machinery NOT IN (SELECT Machinery FROM tblSales)
AND Furniture NOT IN (SELECT Furniture FROM tblSales)

I think Method 2 will be slower if your tables contain huge records. That's because tblSales table is queried two times here. So the overall data involved in second query is high.
I would suggest you still check the query plan for both the queries and go with the best plan.

Hope this helps!

better sp to get unmatched records?

2 solutions

Solution 2

Solution 3

Add your solution here

Preview 0