-3

I've very new to SQL Developer and I have some practice business problems to help build my knowledge..

What im trying to do is I have a table called 'VMStaff' with columns of Name, Department, Favourite Colour, Gender, Job Role and Manger name.. Within this table, some of the managers have entries under 'Manager Name' and 'Name'

What I want from my output is Name of a colleague and their managers favourite colour.. I've tried doing this via self joins but must be missing something - Is anyone able to help or shed light on this?

I have tried twice to make this work, see below -

NUMBER1 - 

SELECT vmstaff1.name, vmstaff2."FAVOURITE COLOUR"
from vmstaff name, vmstaff "Favourite Colour"
Left JOIN vmstaff1 vmstaff2
on vmstaff1."Manager Name" = Vmstaff2.Name

NUMBER 2 - 

Select VM1.Name, VM2."Favourite Colour"
From vmstaff.Name as VM1
Inner Join vmstaff."Favourite Colour" as VM2
on VM1."Manager Name" = VM2."Favourite Colour"

I im lacking understanding of how to join the table and how to use the alias. In attempt NUMBER1 I get the error message Invalid SQL Statement and in attempt number 2 I get the error message SQL Command not properly ended

Álvaro González
  • 142,137
  • 41
  • 261
  • 360
Hawdon1992
  • 1
  • 1
  • 1
    Show your attempt, it's better for learning (because we can correct a specific issue) and faster for us (because we don't need to repeat what you already know...) – Adriano Repetti Feb 07 '17 at 13:03
  • Not without posting the SQL that you've tried. – duffymo Feb 07 '17 at 13:03
  • The SQL query is determined by the database server (in this case Oracle) rather than the client (SQL Developer). In any case, you should edit the question and share the relevant information: table structure, sample data, expected output and (important) what you got so far and how it fails to meet your needs. – Álvaro González Feb 07 '17 at 13:04
  • http://www.oracle-dba-online.com/sql/oracle_sql_tutorial.htm – AntDC Feb 07 '17 at 13:04
  • Please post a [MCVE] preferably with the DDL statements for your table structure, DML statements for some sample data and your attempt at answering the question with where you think it has gone wrong. Please note: StackOverflow is not a tutorial site. – MT0 Feb 07 '17 at 13:04
  • Show us sample data and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) And here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) to learn how improve your question quality and get better answers. – Juan Carlos Oropeza Feb 07 '17 at 13:05
  • Welcome to Stackoverflow. Please see http://stackoverflow.com/help/how-to-ask on how to ask a good question. You should add sample data, table structures, queries you've tried but didn't work (and why they didn't work). You should also choose a more meaningful title. A title should reference the question being asked, not the person who's asking the question. – HoneyBadger Feb 07 '17 at 13:05

1 Answers1

1

You'll be joining back to the table again:

select t1.name, t2.favouritecolour as man_fav_col
from Table1 t1
left join Table1 t2
  on t1.managername = t2.name

Using the alias allows you to use the same table as if it's a different one. In this case, every name provides the favourite colour and acts like a primary key, then managername is essentially a foreign key within the same table.

Now, the reason for a left join instead of an inner join? The top boss won't have a manager, but might not want to be excluded...

JohnHC
  • 10,935
  • 1
  • 24
  • 40