How can I select none duplicate rows with inner join?

Question

My MS SQL Server stored procedure is:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_close]
@DATE NVARCHAR(8)
AS
BEGIN
SELECT appointment_datas.appointment_date
     ,appointment_datas.appointment_no
     ,costumer_datas.costumer_name
     ,appointment_datas.appointment_type
     ,personel_datas.personel_ADI
FROM [LONI].[dbo].appointment_datas
     INNER JOIN [LONI].[dbo].costumer_datas ON appointment_datas.appointment_costumer = costumer_datas.costumer_id
     INNER JOIN [LONI].[dbo].personel_datas ON appointment_datas.appointment_personel = personel_datas.personel_id
     INNER JOIN [GUONU].[dbo].dayend ON appointment_datas.appointment_no <> dayend.appointment_no COLLATE Turkish_CI_AS
WHERE CONVERT(nvarchar(8),appointment_datas.appointment_date,112) = @DATE
END

With this code, I select duplicate rows from the same records. Actually I want to select fields from [LONI].[dbo].appointment_datas but if appointment_no is not in [GUONU].[dbo].dayend

Answer 1

SELECT DISTINCT removes duplicates in your output.

But your SQL looks wrong. Are you sure you mean to write:

TABLE1.FIELD1 <> TABLE1.FIELD1

This always evaulates to false. I think you may have an error in your SQL, and that might be why you are getting duplicate values. You should rarely use <> in a join clause, and you shouldn't have the same field on both sides.

Maybe you intended:

ON [DB1].[dbo].TABLE1.FIELD1 <> [DB2].[dbo].TABLE1.FIELD1

but this will generate a Cartesian product of all the rows that don't match. I doubt this is what you really mean. Perhaps you want this:

ON [DB1].[dbo].TABLE1.ID = [DB2].[dbo].TABLE1.ID
WHERE[DB1].[dbo].TABLE1.FIELD1 <> [DB2].[dbo].TABLE1.FIELD1

This matches the rows from each database that have the same ID, but differ in a certain column. Notice that the <> is not in the JOIN clause.

--- UPDATE ---

Perhaps you mean to select the results from the two different databases and then union them?

SELECT appointment_datas.appointment_date 
        ,appointment_datas.appointment_no
        ,costumer_datas.costumer_name
        ,appointment_datas.appointment_type
        ,personel_datas.personel_ADI
FROM [LONI].[dbo].appointment_datas  
        INNER JOIN [LONI].[dbo].costumer_datas ON appointment_datas.appointment_costumer = costumer_datas.costumer_id
        INNER JOIN [LONI].[dbo].personel_datas ON appointment_datas.appointment_personel = personel_datas.personel_id
WHERE CONVERT(nvarchar(8),appointment_datas.appointment_date,112)

UNION

SELECT appointment_datas.appointment_date 
        ,appointment_datas.appointment_no
        ,costumer_datas.costumer_name
        ,appointment_datas.appointment_type
        ,personel_datas.personel_ADI
FROM [GUONU].[dbo].appointment_datas  
        INNER JOIN [GUONU].[dbo].costumer_datas ON appointment_datas.appointment_costumer = costumer_datas.costumer_id
        INNER JOIN [GUONU].[dbo].personel_datas ON appointment_datas.appointment_personel = personel_datas.personel_id
WHERE CONVERT(nvarchar(8),appointment_datas.appointment_date,112)

--- SOLUTION ---

Use NOT EXISTS in WHERE clause. Read comments to see why.

Answer 2

The line

INNER JOIN [DB2].[dbo].TABLE1 ON TABLE1.FIELD1 <> TABLE1.FIELD1

makes no sense, you want to rephrase that...

Answer 3

SELECT DISTINCT TABLE1.FIELD1 ,TABLE2.FIELD1 ,TABLE1.FIELD3 ,TABLE3.FIELD1 FROM ...

NB in some variants you will have to bracket the field list ie
SELECT DISTINCT (TABLE1.FIELD1 ,TABLE2.FIELD1 ,TABLE1.FIELD3 ,TABLE3.FIELD1 ) FROM ...

Answer 4

If I understand your question correctly (after your edit)

but if appointment_no is not in [GUONU].[dbo].dayend

, you actually want a NOT EXISTS subquery:

set ANSI_NULLS ON 
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_close]
@DATE NVARCHAR(8)
AS
BEGIN
SELECT appointment_datas.appointment_date 
        ,appointment_datas.appointment_no
        ,costumer_datas.costumer_name
        ,appointment_datas.appointment_type
        ,personel_datas.personel_ADI
FROM [LONI].[dbo].appointment_datas  
        INNER JOIN [LONI].[dbo].costumer_datas ON appointment_datas.appointment_costumer = costumer_datas.costumer_id
        INNER JOIN [LONI].[dbo].personel_datas ON appointment_datas.appointment_personel = personel_datas.personel_id
WHERE CONVERT(nvarchar(8),appointment_datas.appointment_date,112) = @DATE 
  AND NOT EXISTS (SELECT 'X' FROM [GUONU].[dbo].dayend WHERE dayend.appointment_no = appointment_datas.appointment_no)     
END