Home » SQL & PL/SQL » SQL & PL/SQL » inane title goes here.
inane title goes here. [message #254452] Thu, 26 July 2007 15:18 Go to next message
usernew
Messages: 1
Registered: July 2007
Junior Member
Hello SQL Gurus,

I need help writing a query of the following kind. Consider a table called EMPLOYEE, containing,

EMPNO FNAME LNAME STATE
-----------------------------------------------------------
1 BOB TOM CA
2 MARY JACOBS VA
3 VIV RICH CA
4 RYAN ALEX VA
5 MIKE JONES CA


I need an output that will display the firstname, lastname of employees that belong to the same state. The required output is:

FNAME LNAME FNAME LNAME STATE
----- ----- ----- ------ -----------------------------
BOB TOM VIV RICH CA
BOB TOM MIKE JONES CA
VIV RICH MIKE JONES CA
MARY JACOBS RYAN ALEX VA


Your help is greatly appreciated!!

[mod-edit] removed the famous "U" word from the title.

[Updated on: Thu, 26 July 2007 15:21] by Moderator

Report message to a moderator

Re: inane title goes here. [message #254454 is a reply to message #254452] Thu, 26 July 2007 15:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
why is "BOB TOM MIKE JONES CA" missing from or not part of the result set?

[Updated on: Thu, 26 July 2007 15:59] by Moderator

Report message to a moderator

Re: inane title goes here. [message #254490 is a reply to message #254452] Thu, 26 July 2007 23:05 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
It is a self join. Although you did not specify the order rule of employees (eg. why 'BOB TOM VIV RICH CA' and not 'VIV RICH BOB TOM CA'); I will take it by EMPNO (employee with smaller EMPNO is first).
SELECT e1.fname, e1.lname, e2.fname, e2.lname, e1.state
FROM employee e1, employee e2
WHERE e1.state = e2.state AND e1.empno < e2.empno;
Previous Topic: Query to retrieve a letter
Next Topic: Reg Dynamic Sql.
Goto Forum:
  


Current Time: Sun Dec 11 08:14:05 CST 2016

Total time taken to generate the page: 0.10078 seconds