Implementing this join in SQL Stored procedure

Question

I have 4 tables Position, Employee, Training and Trmatrix.

Table Position

PositionId   PosName      TrainingId
  1          developer     1,2,3
  2          Designer      4,5
  3          BDA           2,3,6

Table Employee

Employeeid    Ename       Posid   Courseid 
   1          Alex         1        4
   2          Shaun        2        1,2,3
   3          Hales        3

Table Training

Trainingid   Trainingname
  1            JAVA
  2            Dot Net
  3            PHP
  4            Photoshop
  5            JQUERY
  6            Client Handling

TrMatrix

TrmatId    TrID    empID
 1           1        1
 2           2        1
 3           3        1
 4           4        1
 5           4        2
 6           5        2
 7           1        2
 8           2        2
 9           2        3
 10          3        3

foreign Key relation trmatrix trId corresponds to the trainingID of the trainingtable. Employee posid corresponds to the PositionId of the Positiontable. Employee courseId corresponds to the trainingId of the trianingtable.

BY basic Aim is to get that course/trainingname which is no present in the EMployee.Courseid column in correspondance to the trmatrix table, which defines that I have to get the all entries from the trmatrix table for which there is no entry in the employee table Courseid column. Suppose in case of Alex I have to fetch all the data from the trmatrix table except for course 4 since it is present in the courseid column of the Employee table, so it would return course no 1,2,3 not the no 4. I am Newbie to the SQL so please help me out with this problem.

Thanks in advance

Which database are you using? There is no standard for stored procedures, so you'll get a different answer for nearly every RDBMS. — Allan
– Allan, Commented Apr 23, 2013 at 14:20

Colin 't Hart · Accepted Answer · 2013-04-23 08:39:02Z

1

To start with, you should make PositionTraining and EmployeeCourse tables as well:

PositionTraining

PositionId   TrainingId
  1          1
  1          2
  1          3
  2          4
  2          5
  3          2
  3          3
  3          6

EmployeeCourse

Employeeid    Courseid 
   1          4
   2          1
   2          2
   3          3

and then remove Position.TrainingId and Employee.Courseid.

By doing this you make the data much easier to query.

edited Apr 23, 2013 at 8:39

answered Apr 23, 2013 at 8:33

Colin 't Hart

7,8084 gold badges34 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ian P · Accepted Answer · 2013-04-23 08:41:04Z

1

To get things which are not present in one table from another you can use

WHERE NOT EXISTS (SELECT value FROM OtherTable)

or

WHERE NOT IN (SELECT value FROM OtherTable)

However there is a class of queries called subqueries and these are very useful in this circumstance and a very good article on them is here http://allenbrowne.com/subquery-01.html (its written for ms access but the synstax and MS SQL rules are exactly the same so dont be put off)

answered Apr 23, 2013 at 8:41

Ian P

1,7241 gold badge10 silver badges12 bronze badges

2 Comments

Kushal Rajanwal Over a year ago

Ian P Thanks for the reply. I tried out this NOT EXIST clause but please let me know how to check the comma seperated column value(courseid in employee) with the one having the single value(trId trmatrix).

Ian P Over a year ago

Hi Kushal, that is really why you should have employee course in its own table. You can create a user defined function to split these out to a table (answer UDF for splitting out entries), the core of what you want is in the the other answer I'm about to post, but things get messy! If you have not got a lot of courses then you can manually load the table. The real lesson here is that a table should consist of rows, each with singular values and not mutiple values either conconated as delimited strings or in mutiple columns (google SQL table Normalisation) it is largly what SQL is about

Ian P · Accepted Answer · 2013-04-23 14:12:55Z

UDF for spliting out entries

Create function [dbo].[atf_BarListToTable]
    (@list ntext)
RETURNS @tbl TABLE (ListPosn int IDENTITY(1, 1) NOT NULL,
                          SString  VARCHAR(1028) NOT NULL) AS
BEGIN
    DECLARE @pos int
    DECLARE @textpos int
    DECLARE @ChunkLength smallint
    DECLARE @str nvarchar(4000)
    DECLARE @tmpstr nvarchar(4000)
    DECLARE @leftover nvarchar(4000)
    SET @textpos = 1
    SET @leftover = ''
    WHILE @textpos <= datalength(@list) / 2
    BEGIN
        SET @ChunkLength = 4000 - datalength(@leftover) / 2
        SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @ChunkLength))
        SET @textpos = @textpos + @ChunkLength
        SET @pos = charindex('|', @tmpstr)
        WHILE @pos > 0
            BEGIN
                SET @str = substring(@tmpstr, 1, @pos - 1)
                INSERT @tbl (SString) VALUES( @str)
                SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
                SET @pos = charindex('|', @tmpstr)
            END
        SET @leftover = @tmpstr
    END
    IF ltrim(rtrim(@leftover)) <> ''
        INSERT @tbl (SString) VALUES(@leftover)
    RETURN
END

Collectives™ on Stack Overflow

Implementing this join in SQL Stored procedure

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related