List:General Discussion« Previous MessageNext Message »
From:Uttam Date:April 24 2003 1:13pm
Subject:RE: Can I do two LEFT JOINs in one SELECT?
View as plain text  
i guess u need something like this:

SELECT
	rep_fname, rep_lname, dept_name, contact_fname, contact_lname
FROM
	company_table INNER JOIN dept_table ON
company_table.comp_id=dept_table.comp_id
	LEFT JOIN contact_table ON dept_table.dept_id=contact_table.dept_id
	LEFT JOIN rep_table ON dept_table.rep_id = rep_table.rep_id
WHERE company_id = '20'
ORDER BY dept_name;


regds,
-----Original Message-----
From: Daevid Vincent [mailto:daevid@stripped]
Sent: Thursday, April 24, 2003 07:41
To: mysql@stripped
Subject: Can I do two LEFT JOINs in one SELECT?


Hello mySQL gurus, I have companies which have departments which have
customers. Departments are owned by reps. Customers know the dept they
belong to. Depts know the rep that owns them and the company they belong
to.
Companies are dumb. That is key to understanding this. The schema is
below
for the four tables. What I want to do is get a list of all reps,
departments and contacts within those departments for a given company.
Sometimes the rep is NULL (as in they are no longer working here) and
sometimes the department has no contacts currently (but I still want to
get
the dept info, despite it being empty).

I've tried a few things with different results, but it seems I need to
do a
double LEFT JOIN. Perhaps I just don't know the syntax, or perhaps this
isn't implemented, or perhaps I just am doing this Query wrong...

mysql> SELECT rep_fname, rep_lname, dept_name, contact_fname,
contact_lname
    -> FROM company_table,
    ->  dept_table LEFT JOIN contact_table ON contact_dept_table_id =
dept_id,
    ->      dept_table LEFT JOIN rep_table ON dept_rep_table_id = rep_id

    -> WHERE dept_company_table_id = company_id
    ->   AND company_id = '20'
    -> ORDER BY dept_name;
ERROR 1066: Not unique table/alias: 'dept_table'

mysql> SELECT rep_fname, rep_lname, dept_name, contact_fname,
contact_lname
    -> FROM company_table, contact_table,
    ->      dept_table LEFT JOIN rep_table ON dept_rep_table_id = rep_id

    -> WHERE dept_company_table_id = company_id
    ->   AND contact_dept_table_id = dept_id
    ->   AND company_id = '20'
    -> ORDER BY dept_name;
+-----------+-----------+--------------+---------------+---------------+
| rep_fname | rep_lname | dept_name    | contact_fname | contact_lname |
+-----------+-----------+--------------+---------------+---------------+
| Daevid    | Vincent   | --N/A--      | daevid        | vincent       |
| Daevid    | Vincent   | --N/A--      | jim           | sawicki       |
| Daevid    | Vincent   | No Name Dept | Eric          | Johnson       |
+-----------+-----------+--------------+---------------+---------------+

This one above is close, but it is missing the hypothetical row:
+-----------+-----------+--------------+---------------+---------------+
| rep_fname | rep_lname | dept_name    | contact_fname | contact_lname |
+-----------+-----------+--------------+---------------+---------------+
| 		| 	  	|  No Rep      | joe           | blow
|
+-----------+-----------+--------------+---------------+---------------+

This one below gave me all permutations:

mysql> SELECT rep_fname, rep_lname, dept_name, contact_fname,
contact_lname
    -> FROM company_table, contact_table,
    ->      dept_table LEFT JOIN rep_table ON dept_rep_table_id = rep_id

    -> WHERE dept_company_table_id = company_id
    ->   AND company_id = '20'
    -> ORDER BY dept_name;
+-----------+-----------+---------------------+---------------+---------
----
--+
| rep_fname | rep_lname | dept_name           | contact_fname |
contact_lname |
+-----------+-----------+---------------------+---------------+---------
----
--+
| Daevid    | Vincent   | --N/A--             | Bert          | Hopkins
|
					...
| Daevid    | Vincent   | --N/A--             | Robert        | Cornet
|
| Daevid    | Vincent   | No Name Dept        | Berry         |
Shediuhyme
|
					...
| Daevid    | Vincent   | No Name Dept        | jim           | sawicki
|
| NULL      | NULL      | No Rep   		    | Jay           |
|
					...
| NULL      | NULL      | No Rep   		    | Tony          |
Burg
|
+-----------+-----------+---------------------+---------------+---------
----
--+
105 rows in set (0.01 sec)


CREATE TABLE rep_table (
  rep_id smallint(5) unsigned NOT NULL auto_increment,
  rep_login varchar(15) NOT NULL default '',
  rep_password varchar(15) NOT NULL default '',
  rep_fname varchar(20) NOT NULL default '',
  rep_lname varchar(20) NOT NULL default '',
  rep_title varchar(50) NOT NULL default '',
  rep_email varchar(50) NOT NULL default '',
  rep_phone_office varchar(20) NOT NULL default '',
  rep_phone_cell varchar(20) NOT NULL default '',
  rep_address1 varchar(70) NOT NULL default '',
  rep_address2 varchar(20) NOT NULL default '',
  rep_city varchar(30) NOT NULL default '',
  rep_state varchar(20) NOT NULL default '',
  rep_zip varchar(20) NOT NULL default '',
  rep_country varchar(30) NOT NULL default '',
  rep_timestamp timestamp(14) NOT NULL,
  rep_incept date NOT NULL default '0000-00-00',
  rep_last_login date default '0000-00-00',
  rep_admin enum('Y','N') NOT NULL default 'N',
  rep_login_tally int(10) unsigned NOT NULL default '0',
  rep_limit smallint(5) unsigned NOT NULL default '20',
  rep_notes longtext,
  PRIMARY KEY  (rep_id),
  UNIQUE KEY rep_login (rep_login),
  KEY rep_logpass (rep_login,rep_password)
) TYPE=InnoDB;

CREATE TABLE company_table (
  company_id mediumint(8) unsigned NOT NULL auto_increment,
  company_timestamp timestamp(14) NOT NULL,
  company_name varchar(255) NOT NULL default '',
  company_incept date NOT NULL default '0000-00-00',
  company_phone varchar(20) NOT NULL default '',
  company_url varchar(50) NOT NULL default '',
  company_address1 varchar(70) NOT NULL default '',
  company_address2 varchar(70) NOT NULL default '',
  company_city varchar(50) NOT NULL default '',
  company_state varchar(50) NOT NULL default '',
  company_zip varchar(50) NOT NULL default '',
  company_country varchar(50) NOT NULL default '',
  company_type enum('d','r','e') default NULL,
  company_notes longtext,
  PRIMARY KEY  (company_id),
  KEY company_name (company_name),
  KEY company_type (company_type)
) TYPE=InnoDB;

CREATE TABLE dept_table (
  dept_id mediumint(8) unsigned NOT NULL auto_increment,
  dept_timestamp timestamp(14) NOT NULL,
  dept_incept date NOT NULL default '0000-00-00',
  dept_company_table_id mediumint(8) unsigned NOT NULL default '0',
  dept_rep_table_id smallint(8) unsigned default NULL,
  dept_name varchar(255) NOT NULL default '',
  dept_scm_table_id tinyint(3) unsigned NOT NULL default '10',
  dept_SCM_10 date NOT NULL default '0000-00-00',
  dept_SCM_20 date NOT NULL default '0000-00-00',
  dept_SCM_40 date NOT NULL default '0000-00-00',
  dept_SCM_60 date NOT NULL default '0000-00-00',
  dept_SCM_70 date NOT NULL default '0000-00-00',
  dept_SCM_80 date NOT NULL default '0000-00-00',
  dept_SCM_90 date NOT NULL default '0000-00-00',
  dept_SCM_100 date NOT NULL default '0000-00-00',
  dept_notes text,
  PRIMARY KEY  (dept_id),
  KEY dept_company_table_id (dept_company_table_id),
  KEY dept_rep_table_id (dept_rep_table_id),
  KEY dept_scm_table_id (dept_scm_table_id),
  FOREIGN KEY (`dept_company_table_id`) REFERENCES `crm.company_table`
(`company_id`) ON DELETE CASCADE,
  FOREIGN KEY (`dept_rep_table_id`) REFERENCES `crm.rep_table`
(`rep_id`) ON
DELETE SET NULL
) TYPE=InnoDB;

CREATE TABLE `contact_table` (
  `contact_id` mediumint(8) unsigned NOT NULL auto_increment,
  `contact_timestamp` timestamp(14) NOT NULL,
  `contact_dept_table_id` mediumint(8) unsigned NOT NULL default '0'
REFERENCES dept_table(dept_id),
  `contact_fname` varchar(50) NOT NULL default '',
  `contact_lname` varchar(50) NOT NULL default '',
  `contact_title` varchar(50) NOT NULL default '',
  `contact_email` varchar(50) NOT NULL default '',
  `contact_incept` date NOT NULL default '0000-00-00',
  `contact_phone` varchar(20) NOT NULL default '',
  `contact_address1` varchar(70) NOT NULL default '',
  `contact_address2` varchar(30) NOT NULL default '',
  `contact_city` varchar(50) NOT NULL default '',
  `contact_state` varchar(30) NOT NULL default '',
  `contact_zip` varchar(10) NOT NULL default '',
  `contact_country` varchar(50) NOT NULL default '',
  `contact_notes` longtext,
  PRIMARY KEY  (`contact_id`),
  KEY `contact_dept_table_id` (`contact_dept_table_id`),
  KEY `contact_lname` (`contact_lname`,`contact_fname`),
  KEY `contact_fname` (`contact_fname`),
  FOREIGN KEY (contact_dept_table_id) REFERENCES dept_table(dept_id) ON
DELETE CASCADE
) TYPE=InnoDB;


Thread
Can I do two LEFT JOINs in one SELECT?Daevid Vincent24 Apr
  • RE: Can I do two LEFT JOINs in one SELECT?Uttam24 Apr
    • RE: INNER JOIN? Was: Can I do two LEFT JOINs in one SELECT?Daevid Vincent24 Apr
  • Re: Can I do two LEFT JOINs in one SELECT?Brent Baisley24 Apr
  • Re: Can I do two LEFT JOINs in one SELECT?Joshua J . Kugler24 Apr
RE: INNER JOIN? Was: Can I do two LEFT JOINs in one SELECT?Uttam25 Apr