What is a self join? Explain it with an example?
Answer Posted / lalithg
CREATE TABLE candytest
(kidId char(2),
candycolor varchar(10)
)
GO
INSERT INTO candytest SELECT 'K1', 'Yellow'
INSERT INTO candytest SELECT 'K1', 'Red'
INSERT INTO candytest SELECT 'K2', 'Red'
INSERT INTO candytest SELECT 'K2', 'Blue'
INSERT INTO candytest SELECT 'K3', 'White'
INSERT INTO candytest SELECT 'K3', 'Red'
INSERT INTO candytest SELECT 'K3', 'Yellow'
Go
--Now to find those kidid's which has both yellow and red
colored candies, we can write query as:
SELECT c1.kidid
FROM candytest AS c1 JOIN candytest AS c2
ON c1.candycolor = 'Red' AND c2.candycolor = 'Yellow'
AND c1.kidid = c2.kidid
| Is This Answer Correct ? | 85 Yes | 69 No |
Post New Answer View All Answers
How can you fix a poorly performing query?
What are the 18 schemas?
How long can an error message be in raiseerror function?
When installing the datastage7.5x2 edtion iam getting aproblem i.e the cpu count is 2 but the cpu count in installation is one . how can i install in my p.c (system is dual core).
Which four data types cannot be used as a return type from a user-defined function?
How to customize error conditions.
In which database can extended stored procedures be added?
Who are naive users?
Does QTP Support the Propjects which are running in Dot Net? As we are starting with new project in Dot net need to use automation tool?Please advice & let me know what would be the best.
How to use timestamp datatypes
In which table collections errors are stored.
What is dbms and its application?
Where is dbms used?
Explain about the hierarchical model of the database?
fact table and dimension table containg one to many relationship or many to one relastionship