SQL query with 4 related tables

Question

SQL query with 4 related tables

Navigation

#1 by (1 votes)

0

I am learning SQL to make queries and I still do not understand very well how to do them with tables that are related.

My 4 tables are the following ...

First table:

create table empleado (
    id int IDENTITY(1,1),
    nombreempleado varchar(255),
    calle varchar(255),
    ciudad varchar(255),

    -- Create Clustered
     CONSTRAINT pk_empleado PRIMARY KEY clustered (id)
     )

Second table:

create table empresa (
    id int IDENTITY(1,1),
    nombreempresa varchar(255),
    ciudad varchar(255),

    CONSTRAINT pk_empresa PRIMARY KEY clustered (id),
    )

Third table:

create table jefe (
    id int IDENTITY(1,1),
    id_empresa int,
    nombrejefe varchar(255),
    calle varchar(255),
    ciudad varchar(255),

    CONSTRAINT pk_jefe PRIMARY KEY clustered (id),

    -- Create a Foreign Key
    CONSTRAINT fk_jefe_empresa FOREIGN KEY (id_empresa) REFERENCES empresa (id)
    )

Fourth table:

create table trabaja (
    id int IDENTITY(1,1),
    id_empleado int,
    id_empresa int,
    sueldo money,

    CONSTRAINT pk_trabaja PRIMARY KEY clustered (id),

    CONSTRAINT fk_trabaja_empleado FOREIGN KEY (id_empleado) REFERENCES empleado (id),
    CONSTRAINT fk_trabaja_empresa FOREIGN KEY (id_empresa) REFERENCES empresa (id)
    )

So far, the simple queries have come out correctly, the problem comes when I have to do this:

Find all employees who live in the same city and in the same city street than their bosses.

The code I have so far is the following:

SELECT nombreempleado
    FROM empleado
    INNER JOIN trabaja ON trabaja.id_empleado = empleado.id
    INNER JOIN trabaja ON trabaja.id_empresa = empresa.id
    WHERE

What condition do I have to put? Am I linking the ID correctly?

Any help would be welcome, greetings to all and thanks in advance.

sql

asked by Robert Gomez 02.10.2017 в 02:50

source

1 answer

how to make a field input type password show the last 4 characters Concatenate Pseudoclasses and attribute selectors - jQuery

score 1 · Accepted Answer

Regards

From what I see, my suggestion is:

SELECT nombreempleado, nombrejefe
FROM empleado emp
INNER JOIN TRABAJA TRB ON TRB.id_empleado=EMP.id
INNER JOIN jefe  JEF ON JEF.calle=EMP.calle AND JEF.ciudad=EMP.ciudad  
                 /* misma calle y ciudad */
               AND JEF.id_empresa=TRB.id_empresa 
                /* Misma empresa */

Prove the above; just consider that street and city to be text data should be identified for example if someone lives in Calle cedro but in one they put Cedro and in another Cedro Street will not match the same with the city either by accents, spaces, spelling error ... and many more variables.

EDITED

Tenia

INNER JOIN TRABAJA TRB ON TRB.id_empresa=EMP.id_empresa

the correct thing is

INNER JOIN TRABAJA TRB ON TRB.id_empleado=EMP.id

I should have marked you wrong

In case the same employee keeps showing you more than once; check that each employee is assigned to a single boss and in a single company; otherwise it can cause several times repeated, add the IDs of employee, boss and company, if it is shown more than once combination you should review more thoroughly the query in order to eliminate what repeats it, but if it does not repeat itself combination of IDs then the result is correct.