[Nana Nyarko Abronomah]

I get duplicate columns when I run this query. I actually have two tables, TblAsset and TblDispose. They both have columns, AssetId with type int. TblDipose references TblAsset in a one to one relationship. I want the query to return records that are in TblAsset and are not in TblDispose.
The code I have come up with so far is this and it gives me duplicates in the resultset

SELECT TblAsset.AssetId FROM TblAsset, TblDispose WHERE
 TblDispose.AssetId != TblAsset.AssetId

[Martyr2]

That should not be giving you a duplicate columns. You prefixed the column you specifically wanted with the table name which is what you need to do to prevent both columns (one from TblAsset and one from TblDispose) from showing up.

Could you possible mean you are getting duplicate rows? If you are sure you are getting duplicate columns, perhaps there is something else at play here.

Is there any more info you can provide here?

[Nana Nyarko Abronomah]

The columns in TblAsset are (AssetId[pk],Name, Price, CategoryId, SupplierId) and that of TblDispose are (AssetId[pk], DisposeTypeId, DiposerId). so I simply can't understand why I get duplicates in the records returned.

[Mahdi Eftekhari]

Nana Nyarko Abronomah, on 21 February 2012 - 05:16 PM, said:

The columns in TblAsset are (AssetId[pk],Name, Price, CategoryId, SupplierId) and that of TblDispose are (AssetId[pk], DisposeTypeId, DiposerId). so I simply can't understand why I get duplicates in the records returned.

I created a test database with these tables and ran your select on them and it returns only one column. Could you please properly post the create table statements of your tables, also could you please run your select statement and take a snapshot of your result and post it here.

Seems AssetId in your TblDispose needs to be FK rather than PK.
Any particular reason you are cross joining (this has nothing to do with duplicate column, I was just curious as I have never used one - sorry).

Regards
Mahdi Eftekhari

[e_i_pi]

That query should be fine, but I imagine this query would have less overhead even with the expensive NOT IN clause:

SELECT
	TblAsset.AssetId
FROM TblAsset
WHERE TblAsset.AssetId NOT IN (
	SELECT DISTINCT
		TblDispose.AssetID
	FROM TblDispose
)

Though there are a couple of ways you can go about this.
Are you certain that AssetId is the PK in both tables? That's the only reason I can see why duplicates would emerge.