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
To convert IDMS to DB2, how to deal with IDMS occurs and redefined clause?
What is dbms and its application?
Explain about relational database management systems?
How can I detect whether a given connection is blocked?
Explain the rollup operator?
You are testing the performance of a query the first time you run the query, the performance is slow. the second time you run the query, the performance is fast. why is this?
What are the three basic rules which are to be followed for the relational model of the database?
Explain the cube operator?
Write the fastest query to find out how many rows exist in a table?
What are the key features of dbms?
Which four data types cannot be used as a return type from a user-defined function?
Explain about normal forms?
Hi my program is accesing data from two db2 databases.what is the bind card for this program to genarate plan? how to specify the two owners and two qualifiers in bind card
Why does this query return 0?
Explain about the hierarchical model of the database?