In an SQL only application, the EMPLOYEE and COMPANY tables can be queried to determine things like, "Who is Vladimir's manager?", "What company does Josef work for?", and "What employees work for Widgets Inc.". This is done by joining tables with an SQL query.

Who works for Widgets Inc.?

SELECT first_name, last_name FROM employee, company
       WHERE employee.companyid = company.companyid
	     AND company.company_name = "Widgets Inc."

Who is Vladimir's manager?

SELECT managerid FROM employee
       WHERE employee.first_name = "Vladimir"
	     AND employee.last_name = "Lenin"

What company does Josef work for?

SELECT company_name FROM company, employee
       WHERE employee.first_name = "Josef"
	     AND employee.last-name = "Stalin"
	     AND employee.companyid = company.companyid

With CLSQL however we do not need to write out such queries because our view classes can maintain the relations between employees and companies, and employees to their managers for us. We can then access these relations like we would any other attribute of an employee or company object. In order to do this we define some join slots for our view classes.

What company does an employee work for? If we add the following slot definition to the employee class we can then ask for it's COMPANY slot and get the appropriate result.

    ;; In the employee slot list
    (company
      :accessor employee-company
      :db-kind :join
      :db-info (:join-class company
	        :home-key companyid
		:foreign-key companyid
		:set nil))

Who are the employees of a given company? And who is the president of it? We add the following slot definition to the company view class and we can then ask for it's EMPLOYEES slot and get the right result.

      ;; In the company slot list
      (employees
	:reader company-employees
	:db-kind :join
	:db-info (:join-class employee
		  :home-key companyid
		  :foreign-key companyid
		  :set t))

       (president
        :reader president
	:db-kind :join
	:db-info (:join-class employee
		  :home-key presidentid
		  :foreign-key emplid
		  :set nil))

And lastly, to define the relation between an employee and their manager:

	;; In the employee slot list
       (manager
        :accessor employee-manager
	:db-kind :join
	:db-info (:join-class employee
	          :home-key managerid
		  :foreign-key emplid
		  :set nil))

CLSQL join slots can represent one-to-one, one-to-many, and many-to-many relations. Above we only have one-to-one and one-to-many relations, later we will explain how to model many-to-many relations. First, let's go over the slot definitions and the available options.

In order for a slot to be a join, we must specify that it's :db-kind :join, as opposed to :base or :key. Once we do that, we still need to tell CLSQL how to create the join statements for the relation. This is what the :db-info option does. It is a list of keywords and values. The available keywords are:

There are other :join-info options available in CLSQL, but we will save those till we get to the many-to-many relation examples.