Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 18:26pm
ythisbug2.1K
Edited 8-Feb-13 3:37am
v2
Comments
PIEBALDconsult at 5-Feb-13 23:34pm
   
I'de use a left outer join.
ythisbug at 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 at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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[^]
 
 
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...
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Method 1: Get a resultset with common 'Furniture' and 'Machinery' and use that in a subquery to exclude those results.
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.
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!
  Permalink  
v2
Comments
Ankur\m/ at 7-Feb-13 0:03am
   
Platinum down-voter - reason for down voting the answer?
ythisbug at 8-Feb-13 8:48am
   
thank you. but machinery is value nt column field..
Ankur\m/ at 10-Feb-13 23:40pm
   
Oh I see.

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



Advertise | Privacy | Mobile
Web04 | 2.8.1411022.1 | Last Updated 8 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100