| Author: Ratheesh 15 Jul 2008 | Member Level: Gold | Rating:  Points: 1 |
we have to use the group by with having command to get the duplicate values. this query shall show the result of only the users have duplicate values in the employee table.
Syntex:
Select columnName From Table_name
Group By columnName
Having count (*) > 1
Example:
SELECT UserID FROM employee
GROUP BY userid
HAVING count( * ) > 1
self-join
-------------
A self-join is simply a normal SQL join that joins one table to itself. This is accomplished by using table name aliases to give each instance of the table a separate name. Joining a table to itself can be useful when you want to compare values in a column to other values in the same column. A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. A table is joined to itself based upon a field or combination of fields that have duplicate data in different records. The data-type of the inter-related columns must be of the same type or needs to cast them in same type.
When all of the data you require is contained within a single table, but data needed to extract is related to each other in the table itself. Examples of this type of data relate to Employee information, where the table may have both an Employee’s ID number for each record and also a field that displays the ID number of an Employee’s supervisor or manager. To retrieve the data tables are required to relate/join to itself.
Another example which can be tried on SQL SERVER 2005 sample database AdventureWorks is to find products that are supplied by more than one vendor. Please refer the sample database for table structure.
USE AdventureWorks;
GO
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID = pv2.VendorID
ORDER BY pv1.ProductID
|
| Author: chandramohan 15 Jul 2008 | Member Level: Gold | Rating: Points: 1 |
SET ROWCOUNT 1
SELECT NULL
WHILE @@rowcount > 0
DELETE cust
FROM #Customers as cust
INNER JOIN
(SELECT CustID
FROM #Customers
GROUP BY CustID HAVING count(*) > 1)
AS c ON c.CustID = cust.CustID
SET ROWCOUNT
------------
SELECT DISTINCT pv1.ProductID, pv1.VendorID
FROM Purchasing.ProductVendor pv1
INNER JOIN Purchasing.ProductVendor pv2
ON pv1.ProductID = pv2.ProductID
AND pv1.VendorID = pv2.VendorID
ORDER BY pv1.ProductID
---------
|
| Author: http://dotnetvj.blogspot.com 15 Jul 2008 | Member Level: Diamond | Rating: Points: 6 |
Hi
ON DELETE CASCADE
=================
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.
If you specify this option, later when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.
DELETE DUPLICATE RECORDS
========================
http://www.dotnetspider.com/forum/159756-pls-solve-this-query-Interview-qst.aspx
SELF JOIN
=========
You can use a self-join to simplify nested SQL queries where the inner and outer queries reference the same table. These joins allow you to retrieve related records from the same table. The most common case where you'd use a self-join is when you have a table that references itself.
Check out the below link
http://databases.about.com/od/sql/a/selfjoins.htm
Thanks -- Vj
http://dotnetvj.blogspot.com
Thanks -- Vj
http://dotnetvj.blogspot.com
|
| Author: Sriram 15 Jul 2008 | Member Level: Gold | Rating: Points: 6 |
Duplicate values
Select empid from emp
groupby empid having count(*)>1
self join ;
To join within that table is called self join
select empid a,empname b from emp a
inner join emp b
on a.empid=b.empid
ON DELETE CASCADE
=================
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.
If you specify this option, later when you delete a row in the parent table, the database server also deletes any rows associated with that row (foreign keys) in a child table. The principal advantage to the cascading-deletes feature is that it allows you to reduce the quantity of SQL statements you need to perform delete actions.
SriramRamaswamy
|
| Author: Bunty 16 Jul 2008 | Member Level: Diamond | Rating: Points: 3 |
Hi,
Self join is a join which join to itself.
select empid ,empname from emp a
inner join emp b
on a.empid=b.empid
Thanks and Regards
S.S.Bajoria
Thanks & Regards
S.S.Bajoria
|