Owner IDs are displayed instead of departments

Owner IDs are displayed instead of departments

ClaudioGSClaudioGS Posts: 22Questions: 0Answers: 0

Hi how are things.
It is the first time that I have to make related tables. I have two tables departments and owners.

departments:
id int(11) PRIMARY KEY auto_increment,
nrodepto varchar(10) not null,
id_prop int(11) not null,

owners:
id int(11) PRIMARY KEY auto_increment,
nombre varchar(25) not null,
apellidos varchar(25) not null,

I have only put the necessary fields. I have this query:
SELECT d.*, p.id, p.nombre, p.apellidos FROM deptos d INNER JOIN propietarios p ON d.id_prop = p.id

The issue is that it shows the IDs of the owners instead of showing the IDs of the departments.

and I have realized that it is only in datatable, if I do the query through the console it works out, showing all the IDs of the departments and their respective owners.

Can you help me, please.?

Replies

  • rf1234rf1234 Posts: 2,809Questions: 85Answers: 406
    SELECT d.*, p.id, p.nombre, p.apellidos 
    FROM deptos d 
    INNER JOIN propietarios p ON d.id_prop = p.id
    

    In that query d.id is the department id and p.id is the proprietor id. If your data table shows d.id instead of p.id and you don't need d.id I would avoid to select it. Using "d.*" isn't really good practice. I would specify the fields you need explicitly like this for example:

    SELECT d.nrdepto, p.id, p.nombre, p.apellidos 
    FROM deptos d 
    INNER JOIN propietarios p ON d.id_prop = p.id
    
  • ClaudioGSClaudioGS Posts: 22Questions: 0Answers: 0

    No, it's a solution for me.
    I have to show all the other fields that the depts table has.
    Also if I run the query in the mysql console, it shows the data fine, just as I need it.

    SELECT d.*, p.id, p.name, p.surname
    FROM apartments d
    INNER JOIN owners p ON d.id_prop = p.id

    The issue is that in datatable, that query does not work the same as in the mysql console

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    How are you executing that SQL query? Or perhaps more generally, how are you reading information from the database?

    Allan

  • ClaudioGSClaudioGS Posts: 22Questions: 0Answers: 0

    "SOLVED", change the depts table id to dept_id modify the files involved and everything worked as needed. Thanks for your help

Sign In or Register to comment.