0

I'm trying to get the data from table PROJECTS column Project_id and use it in another table Work which also has a PROJECT_ID. What SQL code would I use because I'm stumped.

Richard Pascual
  • 2,023
  • 1
  • 14
  • 22
user3841039
  • 43
  • 1
  • 1
  • 8

1 Answers1

1
SELECT -- whatever
FROM 
    Work
     INNER JOIN 
    Projects ON 
        Work.PROJECT_ID = Projects.Project_id

EDIT: view syntax ... add in whatever columns you need. 

CREATE VIEW vwWorkProjects 
AS

SELECT Work.Work_ID, Work.WorkColumn, Projects.Project_ID, Projects.ProjectColumn 
FROM 
    Work
     INNER JOIN 
    Projects ON 
        Work.PROJECT_ID = Projects.Project_id
AHiggins
  • 7,029
  • 6
  • 36
  • 54
  • How would i fix it then – user3841039 Jul 21 '14 at 18:47
  • Add in the columns you want, using the table's alias to identify where each column is coming from, like in the example I just edited. – AHiggins Jul 21 '14 at 18:51
  • how would i call that view aftwards like in a tabular report – user3841039 Jul 21 '14 at 18:58
  • this is the final view CREATE VIEW vwWorkProjects AS SELECT Work.TASK_ID, Work.TASK_DESCRIPTION, Work.ASSIGNEE,Work.DATE_STARTED,Work.DATE_COMPLETED, Projects.Project_ID, Projects.project FROM Work INNER JOIN Projects ON Work.PROJECT_ID = Projects.Project_id – user3841039 Jul 21 '14 at 19:03
  • ok but how will view this view can i use a select statment or what – user3841039 Jul 21 '14 at 19:28
  • Yes - you can say `SELECT TASK_ID, TASK_DESCRIPTION ... FROM vwWorkProjects`. You could have even skipped the view and just run the `SELECT Work.TASK_ID, Work.TASK_DESCRIPTION ... FROM Work INNER JOIN Projects ...` statement. – AHiggins Jul 21 '14 at 19:32
  • when i added this view to a tabular form it gave me failed to parse SQL query: ORA-01445: cannot select ROWID from, or sample, a join view without a key-preserved table how do i fix this – user3841039 Jul 21 '14 at 19:33
  • First off, I'm not sure you even really need this as a view... you could just put the `SELECT` statement in your report. If you do want to keep the view, try Googling the error and let me know if you find anything that applies to your view. – AHiggins Jul 21 '14 at 19:41
  • ok but there are two doffrent tables that need to be joind at the columns so i need to use a view right? – user3841039 Jul 21 '14 at 19:46
  • No ... AFAIK, you can just use the SELECT statement. I'm not very familiar with your version of Oracle, so I could be wrong ... but try it with just the SELECT. – AHiggins Jul 21 '14 at 19:48
  • @a higgins i think i found the problem, i need to make the row id the same so ill take if from work and use it in projects do you know how i would do this – user3841039 Jul 22 '14 at 13:46