0

Could someone kindly assist in troubleshooting a query problem: I have 2 tables with a common field CheckID. When I run Select statements as below I get the correct results 2 rows but when I use an inner join, it only returns one row. I am trying to transition from MS Access to SQL Server, so I am missing something here? Any assistance highly appreciated. The tables currently contain these two rows

Select CheckID from table1 where CheckID = 723 or CheckID = 322 'returns 2 rows
Select CheckID from table2 where CheckID = 723 or CheckID = 322 'returns 2 rows

Select CheckID from table1 inner join table2 
on table1.CheckID = table2.CheckID ' returns only 1 row
Avagut
  • 924
  • 3
  • 18
  • 34

1 Answers1

-1

You are selecting only CheckID from table1.

Try this for your purposes:

Select CheckID.table1, CheckID.table2 from table1 inner join table2 
where table1.CheckID = 123 or table2.CheckID = 345;
Damian Mąsior
  • 164
  • 1
  • 8
  • Hi, this is giving me 2 columns, I was hoping to only see the CheckID in table 1 that are matched, still not showing – Avagut Jan 24 '15 at 12:06
  • SQL Joins - as name says - joins tables. So if you join table1 and table2 then select their id's colmun - you gonna have 2 columns. – Damian Mąsior Jan 24 '15 at 12:10
  • @DamianMąsior This doesn't help answer the question and your 2nd SELECT is missing the ON clause. – Rhys Jones Jan 24 '15 at 12:31
  • @RhysJones there is no require to use ON clause http://stackoverflow.com/questions/16470942/can-i-use-mysql-join-without-on-condition . – Damian Mąsior Jan 24 '15 at 12:48
  • @DamianMąsior - that link is for MySQL. The ON clause is not optional in SQL Server. – Rhys Jones Jan 24 '15 at 13:28
  • Thank you @DamianMąsior, I think I had a problem in my tables, I recreated them and the query worked. Many thanks for the assistance and the information! – Avagut Jan 24 '15 at 14:50