Join with two tables with select specific columns or all columns

[shaangidwani]

Join with two tables with select specific columns or all columns

//Join with two tables with multiple relational/duplicate columns

We have two tables and both have multiple columns with the same name ex: We are joining with PatientID which are exits on both the tables and another column with ExternalPatientID which are same name with different data in both the tables but it gives error that ExternalPatientID is exited for both the tables.

// Same columns are exits in both the tables at the time of joining

Query :

return this.connection.select({ from: this.tableName, join: { with: 'Patient', on: "Patient.PatientID=Encounter.PatientID" } })

Both the tables have column name "OLPatientID" and when we execute its gives the error that : column OLPatientID exists in both table Encounter & Patient

Is there any way to select a specific column or avoid this issue?

[ujjwalguptaofficial]

You can use the as option in join.

e.g- here column customerId is present in both table - in this situation you can use as to create alias of the column.

    from: table1 name,
    join: {
        with: table2_name,
        on: "table1.common_field=table2.common_field",
        as: {
            customerId: table2_customerId
        } 
    }
});

Here is example link - https://ujjwalguptaofficial.github.io/idbstudio/?db=Demo&query=select(%7B%0A%20%20%20%20from%3A%20%22Customers%22%2C%0A%20%20%20%20join%3A%20%7B%0A%20%20%20%20%20%20%20%20with%3A%20%22Orders%22%2C%0A%20%20%20%20%20%20%20%20type%3A%20%22inner%22%2C%0A%20%20%20%20%20%20%20%20on%3A%20%22Orders.customerId%3DCustomers.customerId%22%2C%0A%20%20%20%20%20%20%20%20as%3A%20%7B%0A%20%20%20%20%20%20%20%20%20%20%20%20customerId%3A%20%27cId%27%0A%20%20%20%20%20%20%20%20%7D%0A%20%20%20%20%7D%0A%7D)

[xuyongweb]

Association query message table appendixId Multiple messages are associated with the uuid of a Chatappendixs table. Only the first one can find the Chatappendixs content, and the remaining few messages cannot be found @ujjwalguptaofficial ujjwalguptaofficial

getListWithMsg(data, limit, name) { this.tableName = name ? name : this.tableName let as = {} Object.keys(MessageTable.columns).forEach(key => { if (Object.keys(ChatappendixTable.columns).includes(key)) { as[key] = ChatappendixTable.name + key } }) let body = { from: this.tableName, where: data, order:{ by: 'Messages.timestamp', type: 'desc' //supprted sort type is - asc,desc }, join: { with: 'Chatappendixs', on: 'Messages.appendixId=Chatappendixs.uuid', type: 'left', as: as } } if(limit) { body.limit = limit } return connection.select(body); }

[shaangidwani]

Thanks @ujjwalguptaofficial its working fine.

Again thanks for your quick response

[ujjwalguptaofficial]

Welcome, please stars the repo.