| Re:Confused by these two questions [message #629648] |
Sat, 13 December 2014 19:59  |
 |
morrism35
Messages: 10
Registered: December 2014
|
Junior Member |
|
|
Need help with these two questions I know I need two join my task table with my priority table but not sure how to get all my tasks
too display. I have included my tables that are invlolved. Thank you in advance. This is my first sql class so please not too advanced stuff I'm struggling enough trying too get a handle on this stuff lol.
1.The users would like to view a list of all tasks but would like to see the Priority name field of the task rather than the Priority ID field. Please create a query that would display all items by task name, due date and priority name with priority name being displayed in UPPER case.
2.The users would like to view a list of all tasks but would like to see the Status name field of the task rather than the Status ID field. Please create a query which would list all items by task name, due date and status name.
Tablename: Tasks
Fieldname Datatype Null Comments
TaskID Number(3) Not null Unique identifier for the table
TaskName Varchar2(20) Not null Name of the task
TaskDesc Varchar2(20) Null Details of the task
DueDate Date Null Task due date
PriorityID Char(1) Null FK to Priority table
StatusID Char(1) Null FK to Status table
Tablename: Priority
Fieldname Datatype Null Comments
PriorityID Char (1) Not null Unique identifier for the table
PriorityName Varchar2(20) Not null Assigns priority level to a task
|
|
|
|
|
|
|
|
|
|
|
|
| Re:Confused by these two questions [message #629653 is a reply to message #629650] |
Sat, 13 December 2014 20:43  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
morrism35 wrote on Sat, 13 December 2014 18:15select taskname, duedate, upper(priorityname) "PRIORITYNAME" from tasks, priority where tasks.priorityid=priority.priorityid;
This is only returning one of the 4 rows that is in the table. I need all rows in the table.
Given what you have provided, this is the correct way to join the tables. If you are only getting one row, then there must only be one row that matches. If you want the non-matching rows as well, then you can use an outer join:
SELECT priority.taskname, priority.duedate, UPPER (tasks.priorityname)
FROM priority, tasks
WHERE priority.priorityid = tasks.priorityid(+);
You may also want to add an ORDER BY clause.
It might help if you provide create table and insert statements for your data or at least the results of the following:
SELECT * FROM tasks;
SELECT * FROM priority;
|
|
|
|
|
|
|
|
| Re:Confused by these two questions [message #629656 is a reply to message #629654] |
Sat, 13 December 2014 21:05 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
morrism35 wrote on Sat, 13 December 2014 18:57So you are saying I would need a outer join and yes I want all rows in the task table. I did try an order by clause with my original code that I posted but it still only returned one row . Is there a way to copy the text from a sql plus window?
.
Yes, you need the outer join. You need to make sure that the (+) is on the tasks table.
The ORDER BY clause does not affect the number of rows, just the order in which they are displayed, which seems to be something the problem is asking for.
Yes, you can copy the text from your SQL*Plus window. Left mouse clock on the little icon in the upper left corner of the bar on the top of the window. A drop-down list should appear. Select edit, then select all. Go back to the little icon, left click, select edit, then select copy. Come to the forum, click on the reply window, then hold down your ctrl key and press the V key.
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
