6 Replies - 994 Views - Last Post: 10 March 2010 - 08:11 AM Rate Topic: -----

#1 James Bond C++ Spy   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 132
  • Joined: 03-October 07

Trying to JOIN tables

Posted 09 March 2010 - 09:05 AM

I need to pull a report of Last Name, First Name from the employee table and all the training from the annual table for all the employees in one result:

Here are my tables:
employee table
-Emp_Id
-LastName
-FirstName
-Dept
-Shift

annual table
-Emp_Id
-harassment
-bbp
-loto
-hazcomm
-ppe
-evaluation
-drug

I have tried using a full join. Here is the query:

SELECT employee.LastName, employee.FirstName, annual.Emp_Id, 
annual.harassment, annual.bbp, annual.loto, annual.hazcomm, annual.ppe, annual.evaluation, annual.drug 
FROM employee
FULL JOIN annual
ON employee.Emp_Id=annual.Emp_Id
ORDER BY employee.LastName



Here is the error I get:
#1054 - Unknown column 'employee.LastName' in 'field list'

Thanks for any and all help

Is This A Good Question/Topic? 0
  • +

Replies To: Trying to JOIN tables

#2 P4L   User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,789
  • Joined: 07-February 08

Re: Trying to JOIN tables

Posted 09 March 2010 - 09:36 AM

Try this:

SELECT employee.LastName, employee.FirstName, annual.Emp_Id,  
annual.harassment, annual.bbp, annual.loto, annual.hazcomm, annual.ppe, annual.evaluation, annual.drug  
FROM employee,
annual 
Where employee.Emp_Id=annual.Emp_Id 
ORDER BY employee.LastName


Was This Post Helpful? 0
  • +
  • -

#3 James Bond C++ Spy   User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 132
  • Joined: 03-October 07

Re: Trying to JOIN tables

Posted 09 March 2010 - 09:56 AM

Thanks works perfect.
Was This Post Helpful? 0
  • +
  • -

#4 P4L   User is offline

  • Your worst nightmare
  • member icon

Reputation: 34
  • View blog
  • Posts: 2,789
  • Joined: 07-February 08

Re: Trying to JOIN tables

Posted 09 March 2010 - 10:02 AM

Welcome, but do you know why it works vs your code? Not trying to be a smartass, but I just want to make sure someone understands the difference.
Was This Post Helpful? 0
  • +
  • -

#5 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6246
  • View blog
  • Posts: 24,014
  • Joined: 23-August 08

Re: Trying to JOIN tables

Posted 09 March 2010 - 10:49 AM

P4L, I would like you to explain it, actually. It's escaping me as I look at it.
Was This Post Helpful? 0
  • +
  • -

#6 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7250
  • View blog
  • Posts: 15,123
  • Joined: 16-October 07

Re: Trying to JOIN tables

Posted 09 March 2010 - 11:12 AM

Or, you can just change one word and not use the archaic non join syntax:
SELECT employee.LastName, employee.FirstName, annual.Emp_Id, 
		annual.harassment, annual.bbp, annual.loto, annual.hazcomm, annual.ppe, annual.evaluation, annual.drug 
	FROM employee
		INNER JOIN annual
			ON employee.Emp_Id=annual.Emp_Id
	ORDER BY employee.LastName



A "FULL JOIN" and an "INNER JOIN" are different critters. In most SQL implementations, just "JOIN" is "INNER JOIN". You will very rarely have cause to use full joins, inner you'll use all the time.

A full join is quirky, bringing in data from both sides, if even there is no match. It's like an outer join without direction. An inner join behaves as you'd expect; both sides actually need the fields to exist.
Was This Post Helpful? 0
  • +
  • -

#7 JackOfAllTrades   User is offline

  • Saucy!
  • member icon

Reputation: 6246
  • View blog
  • Posts: 24,014
  • Joined: 23-August 08

Re: Trying to JOIN tables

Posted 10 March 2010 - 08:11 AM

Yeah, I'd never seen FULL JOIN used before, that was the only thing that looked weird to me.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1