Plenty of records to choose, what type of JOIN?

I have 100k + records in table usr_table and I want to select all of them that do not have the same ID as the ID's indicated in table Usr_Type_Data

Select * From usr_table As t1
Join Company_Sales.dbo.Usr_Type_Data As t2 ON
t2.user_id = t1.user_id Where CustomerTypeId <> 7

I get 0 returned.

If I change it to:

Select * From usr_table As t1
Join Company_Sales.dbo.Usr_Type_Data As t2 ON
t2.user_id = t1.user_id Where CustomerTypeId = 7

I get all records from table Usr_Data_Type returned.

Do I need to specify a special Join type?

Or perhaps change the T-SQL around?

you can try a Left Outer Join to see if that brings back the results your looking for.

Select * From usr_table As t1
Left Outer Join Company_Sales.dbo.Usr_Type_Data As t2 ON
t2.user_id = t1.user_id Where CustomerTypeId <> 7

|||

Hi there,

Yeah I thought of that also, but it does not work.

Must be something that works?

I just thought of something, the Where clause in the above code is effectively working against both tables after they were joined correct?

Is there anywhat to put a where clause on just 1 of the tables?

|||

First of all I'd check to make sure you have data in your tables that meet your criteria.

You could try a full outer join as well. The type of join you use really is determined by how the data is stored.

|||

"I have 100k + records in table usr_table and I want to select all of them that do not have the same ID as the ID's indicated in table Usr_Type_Data"

One way is to use the NOT EXISTS clause.

Select *From usr_tableWhereNOT EXISTS (Select *From usr_type_data.UserID = usr_table.UserID)

Note that if Usr_Type_Data is also a large volume table - this could be slow. If you need to optimize for performance, then you may need a solution involving a couple of steps with a temporary table - but maybe not.