Getting duplicate records with select statement

[sunny124]

Hi guys,

Im getting duplicate records with a select statement.

Below is an assignment question that I have to write a query for:

'Write a query that lists only those computers that belong to a department where the department's city is the same as the city that supplied the computer.'

This is the select statement that I have written:

SELECT C.CompId, C.Description, D.DeptId, D.Deptname, D.Cityname, E.EmpId
FROM	COMPUTER C
INNER JOIN EMPLOYEE E
ON C.EmpId = E.EmpId
INNER JOIN DEPARTMENT D
ON E.DeptId = D.DeptId
INNER JOIN SUPPLIER S
ON D.Cityname = S.Cityname;

The records are retrived are correct but its getting them twice for each record.
Below are the records retrived.

COMPID	 DESCRIPTION	 DEPTID	 DEPTNAME	        CITYNAME	      EMPID
102	HP Workstation	  2	Justice	                Melbourne	      11
101	Acer Workstation  1	Human Services	        Melbourne	      10
105	DELL PCs	  1	Human Services	       Melbourne	      14
103	Sony Laptops	  3	Education and Training	Sydney	              12
104	Lenovo Laptops	  4	Health	                Canberra	      13
102	HP Workstation	  2	Justice	                Melbourne	      11
101	Acer Workstation  1	Human Services	        Melbourne	      10
105	DELL PCs	  1	Human Services       	Melbourne	      14
103	Sony Laptops	  3	Education and Training	Sydney	              12

I have only inserted 5 rows of data in the computer table, so I should get maximum of 5 rows in resultset.

Anyone know what I am doing wrong?

Thanks

Sunny124

[mwasif]

You need to use GROUP BY.

[sunny124]

I was reading the tutorial on how to use Group By on w3schools, it says to use them to aggregate functions. Im not using any aggregate functions, so Im not sure how to use Group By in this case.

I have solved the problem using the following sql statement:

SELECT C.CompId, C.Description, D.DeptId, D.Deptname, D.Cityname
FROM	COMPUTER C
INNER JOIN EMPLOYEE E
ON C.EmpId = E.EmpId
INNER JOIN DEPARTMENT D
ON E.DeptId = D.DeptId
INNER JOIN SUPPLIER S
ON D.Cityname = S.Cityname
WHERE C.SuppId = S.SuppId;

Below are the results I get:

COMPID	 DESCRIPTION	 DEPTID	 DEPTNAME	 CITYNAME
103	Sony Laptops	 5	Defense	          Geelong
101	Acer Workstation 1	Human Services	  Melbourne
105	DELL PCs	 1	Human Services	  Melbourne
104	Lenovo Laptops	 4	Health	         Canberra

Our Lecturer has said not to use the 'WHERE' Keyword to link tables, is there a better way I can the same result without using the WHERE' keyword?

Thanks

Sunny124