Home » SQL & PL/SQL » SQL & PL/SQL » Help creating view for each record...
Help creating view for each record... [message #412962] Mon, 13 July 2009 12:07 Go to next message
rashowlist
Messages: 4
Registered: July 2009
Junior Member
Hey all, I need some SQL help to create a view similar to below:

I have one table which contains employee titles:
101
102
103
104

I have another table which contains department descriptions:
Sales
Purchasing
Quality Control

I would like to create a view which provides:
101 Sales
101 Purchasing
101 Quality Control
102 Sales
102 Purchasing
102 Quality Control
103 Sales
103 Purchasing
103 Quality Control
104 Sales
104 Purchasing
104 Quality Control

Any help would be greatly appreciated.

Chuck
Re: Help creating view for each record... [message #412963 is a reply to message #412962] Mon, 13 July 2009 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
That certainly is a nice Cartesian Product, but WHY?

Standard homework assignment?
Re: Help creating view for each record... [message #412965 is a reply to message #412963] Mon, 13 July 2009 12:22 Go to previous messageGo to next message
rashowlist
Messages: 4
Registered: July 2009
Junior Member
Not a homework assignment. I work for the CPD finance division and we were trying to figure out how to put together a table or view this way to get every combination of paytitles and certain codes (what we call caps codes) from our budget department. The data I provided in the example is just generic so it wouldn't be too difficult to explain.
Re: Help creating view for each record... [message #412967 is a reply to message #412965] Mon, 13 July 2009 12:28 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Then SELECT from both tables and leave out the join. QED.
Re: Help creating view for each record... [message #412968 is a reply to message #412962] Mon, 13 July 2009 12:29 Go to previous messageGo to next message
rashowlist
Messages: 4
Registered: July 2009
Junior Member
Thanks for mentioning cartesian product. I found the answer. Basically just select the columns your looking for in each table but don't add a filter on the join statement.

www.dba-oracle.com/t_garmany_9_sql_cross_join.htm

[Updated on: Mon, 13 July 2009 12:30]

Report message to a moderator

Re: Help creating view for each record... [message #412969 is a reply to message #412962] Mon, 13 July 2009 12:29 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
create view title_desc as select emp.title, dept.desc from emp, dept
Re: Help creating view for each record... [message #412970 is a reply to message #412962] Mon, 13 July 2009 12:30 Go to previous message
rashowlist
Messages: 4
Registered: July 2009
Junior Member
Thanks again
Previous Topic: Updation
Next Topic: Parametrize a date (merged 4)
Goto Forum:
  


Current Time: Sat Dec 10 08:45:11 CST 2016

Total time taken to generate the page: 0.15453 seconds