SQL Server - Duplicate rows from a JOIN

SELECT A.AID, B.BID, B.BNAME, D1.DDATE, D2.DAUTHOR FROM A

INNER JOIN B ON A.AID = B.BID

LEFT OUTER JOIN (

    SELECT AID, BID, MAX(DDATE) AS DDATE FROM D GROUP BY AID, BID

) AS D1 ON A.AID = D1.AID AND B.BID = D1.BID 

The problem is that you cannot get DAUTHOR without another join... at the end of the day, it all depends on your exact table structure and key scheme.

since you have not given detail of you query or table..'l giv you a short example of join query

SELECT ProductID, Purchasing.Vendor.VendorID, Name
FROM Purchasing.ProductVendor JOIN Purchasing.Vendor
    ON (Purchasing.ProductVendor.VendorID = Purchasing.Vendor.VendorID)
WHERE StandardPrice > $10
  AND Name LIKE N'F%'
GO

Go through this link

http://msdn.microsoft.com/en-us/library/ms191517.aspx

Post you query it will be better......

Here is the example of how to use join;

If you need to join Employees to either Stores or Offices depending on where they work

you simply LEFT OUTER JOIN to both tables, and in your SELECT clause, return data from the one that matches:

select
  E.EmployeeName, coalesce(s.store,o.office) as Location
from
  Employees E
left outer join
  Stores S on ...
left outer join
  Offices O on ...

OR

select
  E.EmployeeName, coalesce(s.store,o.office) as Location
from
  Employees E
left outer join
  Stores S on ...
left outer join
  Offices O on ...
where
  O.Office is not null OR S.Store is not null

http://weblogs.sqlteam.com/jeffs/archive/2007/04/03/Conditional-Joins.aspx

Or

Hi,

you need to use Distinct keyword or add primary and foreign key filed to your Join condition..

like below ....

select distinct * from tableName1 a inner join tableName2 b on a.primarykeyfiled = b.foreighkeyfiled and a.other uniques fields = b.other uniques fields

Eg:

table1

RegNo, EduYear, Name

table2

RegNo, EduYear, address, ,Personal details

query to get all details

select distinct * from table1 a inner join table2 b on a.RegNo = b.RegNo and a.EduYear = b.EduYear

Hello h1bworker

As yuo have not stated the  table structure and other details We are not able to understand the situation But as you are getting duplicate records means there must be a query problem. I am giving you the examples and types of JOINS.
Consdiering the table 
      Employee Table :- Department Table:- 

   EmployeeID EmployeeName DepartmentID DepartmentID DepartmentName
   1 Smith 1 1 HR
   2 Jack 2 2 Finance
   3 Jones 2 3 Security
   4 Andrews 3 4 Sports
   5 Dave 5 5 HouseKeeping
   6 Jospeh 6 Electrical


Inner Join

An Inner Join will take two tables and join them together based on the values in common columns ( linking field ) from each table.

Example 1 :- To retrieve only the information about those employees who are assinged to a department.

Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName From Employee Inner Join Department on Employee.DepartmentID = Department.DepartmentID

Example 2:- Retrieve only the information about departments to which atleast one employee is assigned.

Select Department.DepartmentID,Department.DepartmentName From Department Inner Join Employee on Employee.DepartmentID = Department.DepartmentID

Outer Joins :-

Outer joins can be a left, a right, or full outer join.

Left outer join selects all the rows from the left table specified in the LEFT OUTER JOIN clause, not just the ones in which the joined columns match.

Example 1:- To retrieve the information of all the employees along with their Department Name if they are assigned to any department.


Select Employee.EmployeeID,Employee.EmployeeName,Department.DepartmentName From Employee LEFT OUTER JOIN Department on Employee.DepartmentID = Department.DepartmentID

Right outer join selects all the rows from the right table specified in the RIGHT OUTER JOIN clause, not just the ones in which the joined columns match.


Example 2:- use Right Outer join to retrieve the information of all the departments along with the detail of EmployeeName belonging to each Department, if any is available.

Select Department.DepartmentID,Department.DepartmentName,Employee.EmployeeName From Employee Outer Join Department on Employee.DepartmentID = Department.DepartmentID

The more on this can be found at
http://www.dotnetspider.com/resources/635-SQL-Joins-wi-Examples.aspx
please go throgh it

Happy Coding takecare

if your table is normalized properly and appropriate primary , foreign key relationships are in place and if you are using proper join conditions in your query(joining master and detail table with the proper key fields), your query will not fetch duplicate rows, lets say you have a table emp (with fields id, name) and another table storing the various bank accounts which the employee has  in bank_account table(with fields emp_id, bank_id, bank_name - emp_id and bank_id being primary keys here), your query to join both tables will be as,

select

e.id,e.name, b.bank_name

from

emp e, bank_account b

where e.id = b.emp_id

in this case, id & (emp_id,bank_id) are the primary keys and emp_id will be referred as foreign key to id field in emp table. One employee can have multiple bank accounts, in this case bank_account will have duplicate emp_id, but the combination of (emp_id,bank_id) will be unique for each row.

hope it clarifies!

Please post your query for us to help you out better!