INNER JOIN Question

INNER JOIN Question

• Redjo

  From what I understand you want to do something like

  SELECT id, t1.stuff, t2.stuff FROM table1 t1 INNER JOIN table2 t2 on t1.id <> t2.somerelationalid

  This may help http://msdn2.microsoft.com/en-us/library/ms190014(SQL.90).aspx

  
  

• Leroy Pierce

  Using an OUTER JOIN will be the best way to do it. The first way will give you the records that DO NOT exists in Table1. The second way will give you the records that DO exist in Table1 and Table2.

  SELECT A.Col1,
  B.Col1
  FROM dbo.Table1 A
  LEFT JOIN dbo.Table2 B ON A.YourIdentifier = B.YourIdentifier
  WHERE B.YourIdentifier IS NULL
  

  SELECT A.Col1,
  B.Col1
  FROM dbo.Table1 A
  LEFT JOIN dbo.Table2 B ON A.YourIdentifier = B.YourIdentifier
  WHERE B.YourIdentifier IS NOT NULL

  
  

• DNet

  I agree with JEN. I doubt an inner join with <> is what you want, as it returns everything except where the rows match so in effect it does a cross join, and then remove the examples where the tow rows match (one from each table). You probably want to use a NOT EXISTS, i.e. get the rows that are in one table and not the other, or vice versa. To do this, do use a sub query style syntax, eg to do a not exists on two tables, where id is the PK:

  select * from tableA a where NOT EXISTS (select 1 from tableB b where b.id = a.id)

  This essentially doing the join, caching the resultset formed from the union of the two sets (from the JOIN), then performing a select all from tableA, and removing the rows in the resultset cached earlier. This may not be how the DB engine actually does it (or may be), but that is the simplest way of thinking about it.

  
  

• robert1352

  You can addiionally use either the OUTER syntax OR the NON EXISTS syntax, look in the BOl for more information and samples, or come back if you are not clear with the samples.

  HTH, jens Suessmeyer

  ---

  http://www.sqlserver2005.de

  ---

  
  

• mutzel

  Ah, I think I finally see what you are going for. I think FULL OUTER JOIN does the trick:

  drop table test1
  drop table test2
  go
  create table test1
  (
  value int primary key
  )
  create table test2
  (
  value int primary key
  )
  insert into test1
  select 1
  union all
  select 2
  union all
  select 3
  union all
  select 4

  insert into test2
  select 3
  union all
  select 4
  union all
  select 5
  union all
  select 6
  union all
  select 7

  GO

  select *
  from test1
  full outer join test2
  on test1.value = test2.value
  where test1.value is null
  or test2.value is null

  returns:

  value value
  ----------- -----------
  1 NULL
  2 NULL
  NULL 5
  NULL 6
  NULL 7

  (5 row(s) affected)

  
  
  

• PhilipT

  Can you post a sample table structure and some data and the desired results

  What happens when you are looking for rows that don't match is that while it matches one row, it doesn't match others so everything gets returned.

  
  
  
• INNER JOIN Question