Click here to Skip to main content
12,625,981 members (24,095 online)
Rate this:
 
Please Sign up or sign in to vote.
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
Updated 8-Feb-13 3:37am
v2
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
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/ 7-Feb-13 0:03am
   
Platinum down-voter - reason for down voting the answer?
ythisbug 8-Feb-13 8:48am
   
thank you. but machinery is value nt column field..
Ankur\m/ 10-Feb-13 23:40pm
   
Oh I see.
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  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.161205.3 | Last Updated 8 Feb 2013
Copyright © CodeProject, 1999-2016
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