Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2008
    Posts
    117

    Unanswered: Mastering in Joins

    Hi,
    I am using SQL Server2005. I know all the basic joins on theory. But i want to know where exactly all those joins needs to used. Also the query execution times needs to be faster on using those joins. Any Ideas?
    Thankyou

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bharanidharanit View Post
    But i want to know where exactly all those joins needs to used.
    each join type is used in different circumstances, depending on whether you want only matching rows, or always all rows from one tbale with or without matching rows from the other table, or vice versa, or both

    without completely re-writing entire join tutorials, it is difficult to give you a concise summary of those circumstances beyond that which i just gave

    if you could come up with a scenario or two, i'd be happy to tell you which joins to use

    Quote Originally Posted by bharanidharanit View Post
    Also the query execution times needs to be faster on using those joins. Any Ideas?
    certainly -- make sure your tables are properly optimized with indexes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    117
    Hi,
    I used this coding for creating joins under cursor. I want to print the first mark of each exam and the total marks of all subjects with corresponding student name. But the join is not correctly fetching the record. Its displaying the name multiple times.

    Code:
    CREATE TABLE student_master(
    stud_regno INT NOT NULL IDENTITY PRIMARY KEY,
    stud_name VARCHAR(15),
    admission_date DATETIME DEFAULT GETDATE())
    
    CREATE TABLE class_master(
    class_id INT NOT NULL IDENTITY PRIMARY KEY,
    class_name VARCHAR(10))
    
    CREATE TABLE subject_master(
    subject_id INT NOT NULL IDENTITY PRIMARY KEY,
    subject_name VARCHAR(10),
    class_id INT REFERENCES class_master(class_id))
    
    CREATE TABLE student_exam(
    exam_id INT NOT NULL IDENTITY PRIMARY KEY,
    student_marks FLOAT,
    exam_date DATETIME,
    subject_id INT REFERENCES subject_master(subject_id),
    stud_regno INT REFERENCES student_master(stud_regno))
    
    INSERT INTO student_master(stud_name) VALUES('aaa')
    INSERT INTO student_master(stud_name) VALUES('bbb')
    INSERT INTO student_master(stud_name) VALUES('ccc')
    
    INSERT INTO class_master(class_name) VALUES('SSLC')
    INSERT INTO class_master(class_name) VALUES('HSC')
    
    INSERT INTO subject_master(subject_name,class_id) VALUES('English',1)
    INSERT INTO subject_master(subject_name,class_id) VALUES('Biology',2)
    
    INSERT INTO student_exam(student_marks,exam_date,subject_id,stud_regno) VALUES(50,'1-JAN-2011',1,1)
    INSERT INTO student_exam(student_marks,exam_date,subject_id,stud_regno) VALUES(60,'2-JAN-2011',1,2)
    INSERT INTO student_exam(student_marks,exam_date,subject_id,stud_regno) VALUES(70,'3-JAN-2011',2,3)
    Code:
    DECLARE samp_cursor CURSOR
    FOR
    	SELECT max(t1.student_marks) as 'First', sum(t1.student_marks) as 'Total', t2.stud_name, t3.subject_name
    	FROM student_exam t1
    	INNER JOIN student_master t2 ON t1.stud_regno=t2.stud_regno
    	INNER JOIN subject_master t3 ON t1.subject_id=t3.subject_id
    	INNER JOIN class_master t4 ON t3.class_id=t4.class_id AND class_name='SSLC'
    	GROUP BY t1.student_marks,t2.stud_name,t3.subject_name
    OPEN samp_cursor
    DECLARE @FirstMark FLOAT
    DECLARE @Total FLOAT
    DECLARE @StudentName VARCHAR
    DECLARE @SubjectName VARCHAR
    FETCH NEXT FROM samp_cursor INTO @FirstMark,@Total,@StudentName,@SubjectName
    WHILE @@FETCH_STATUS=0
    	BEGIN
    		PRINT @FirstMark
    		PRINT @Total
    		PRINT @StudentName
    		PRINT @SubjectName
    			FETCH NEXT FROM samp_cursor INTO @FirstMark,@Total,@StudentName,@SubjectName
    	END
    CLOSE samp_cursor
    DEALLOCATE samp_cursor

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bharanidharanit View Post
    I want to print the first mark of each exam and the total marks of all subjects with corresponding student name.
    unless the homework assignment calls for it, do ~not~ use a cursor
    Code:
    SELECT student_master.stud_name
         , subject_master.subject_name
         , subject_marks.top_mark
         , subject_marks.total_marks
      FROM class_master
    INNER 
      JOIN subject_master
        ON subject_master.class_id = class_master.class_id
    INNER
      JOIN ( SELECT subject_id
                  , MAX(student_marks) AS top_mark
                  , SUM(student_marks) AS total_marks
               FROM student_exam
             GROUP
                 BY subject_id ) AS subject_marks
        ON subject_marks.subject_id = subject_master.subject_id
    INNER
      JOIN student_exam
        ON student_exam.subject_id = subject_marks.subject_id
       AND student_exam.student_marks = subject_marks.top_mark
    INNER
      JOIN student_master  
        ON student_master.stud_regno = student_exam.stud_regno
     WHERE class_master.class_name = 'SSLC'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2008
    Posts
    117
    Hi thankyou,
    tats perfectly working. i tried these but fed up with wrong fetching. Is there any order in selection of tables as which table to be fetched first as here you fetched from class_master ?

    And in the previous query, wat about wrong in my cursor ?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by bharanidharanit View Post
    Is there any order in selection of tables as which table to be fetched first as here you fetched from class_master ?
    yes, i prefer to write my FROM clause with the tables in the sequence which makes most sense, and this is almost always achieved by putting the table with the greatest restriction first (as this is how the optimizer will choose to execute the query)

    in your example, there was only one restriction, i.e. only one WHERE condition

    Quote Originally Posted by bharanidharanit View Post
    And in the previous query, wat about wrong in my cursor ?
    horribly inefficient
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2008
    Posts
    117
    thankyou very much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •