looped query

  • 13 years ago

    hello people

    I have 2 tables, employee and engineer.

     

    I need to get all the details from employee for the employee number obtained from engineer

     

    the employee table has both operators and engineers details and the only way they are distinguishable is through the engineer table which has a primary foreign key of employee number so

     

    IF engineer.emplyeeNumber == employee.employeeNumber THEN Give details

     

    fields are as follows

     

    EMPLOYEE

    --------------

    EmployeeNumber

    Phone

    Email

     

    ENGINEER

    -------------

    EmployeeNumber

    Specialise

     

    and i need to create a view that shows the engineers with what they specialise in and thier details i have currently

     

    CREATE VIEW specialise AS
         SELECT * FROM employee WHERE employeenumber = (SELECT employeeNumber FROM engineer);

     

    but i get the error of "single row subquery returns multiple rows"

     

    please help

     

    Steve

  • 13 years ago

    What you want is this:

    Create VIEW specialise AS

       Select  a.employeenumber, a.Specialise, b.email, b.phone from Engineer a left join Employee b on a.employeenumber = b.employeenumber

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Nine people can't make a baby in a month.” - Fred Brooks