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
What is database design and development?
Explain what a database is?
Explain about xml databases?
Why does this query return 0?
In which database can extended stored procedures be added?
If a column is an image value type, how you can compare column values? How can you use this column in join clause?
Explain about a primary key?
Explain about the storage and physical database design?
Explain about network model?
What are data modelling techniques?
How to replace not in with not exist?
Write short notes on manual refreshes.
There is a trigger defined for INSERT operations on a table, in an OLTP system. The trigger is written to instantiate a COM object and pass the newly insterted rows to it for some custom processing. What do you think of this implementation? Can this be implemented better?
What is the maximum length of an extended property?
To convert IDMS to DB2, how to deal with IDMS occurs and redefined clause?