CODEWITHSUNDEEP
sqlstored-proceduresjoin
Kushal Rajanwal
Kushal Rajanwal

Reputation: 11

Implementing this join in SQL Stored procedure

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

Upvotes: 1

Views: 93

Answers (3)

Ian P
Ian P

Reputation: 1724

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

Upvotes: 0

Ian P
Ian P

Reputation: 1724

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)

Upvotes: 1

Colin &#39;t Hart
Colin &#39;t Hart

Reputation: 7729

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.

Upvotes: 1

Related Questions