CODEWITHSUNDEEP
mysqlquery-builderknex.js
psytron
psytron

Reputation: 75

Knex.js : How to select columns from multiple tables?

Example query, 

SELECT a.driverID, a.dCarID, a.dDeviceID, b.carRegiNum FROM driverProfile a, carProfile b WHERE a.dManagerID = 7 AND b.carID=a.dCarID

Query runs fine on MySQL. driverProfile and carProfile are two separate tables. Please comment if you need more clarification. I am stuck here.

Help is appreciated. Thank you.

Upvotes: 4

Views: 13050

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35583

The original Query (divided into row so we can read it [hint])

SELECT a.driverID, a.dCarID, a.dDeviceID, b.carRegiNum 
FROM driverProfile a, carProfile b 
WHERE a.dManagerID = 7 AND b.carID=a.dCarID

Step 1, Join Syntax (fix it!)

Over 25 years ago SQL best practice in joins was redefined and we stopped using commas between table names. Just stop it... please! and you can't do it in Knex.js anyway.... so best get used to it. Fix the join syntax first:

SELECT a.driverID, a.dCarID, a.dDeviceID, b.carRegiNum 
FROM driverProfile a
INNER JOIN carProfile b ON b.carID=a.dCarID
WHERE a.dManagerID = 7

Step 2, Aliases (not)

It also seems that Knex doesn't do aliases easily, so replace with table names:

SELECT driverProfile.driverID, driverProfile.dCarID, driverProfile.dDeviceID, carProfile.carRegiNum 
FROM driverProfile
INNER JOIN carProfile ON carProfile.carID=driverProfile.dCarID
WHERE driverProfile.dManagerID = 7

Step 3, "Knexisfy" the query

knex.select(['driverProfile.driverID', 'driverProfile.dCarID', 'driverProfile.dDeviceID', 'carProfile.carRegiNum' ])
.from('driverProfile')
.innerJoin('carProfile','carProfile.carID','driverProfile.dCarID')
.where('driverProfile.dManagerID',7)
.then(function(output){
    //Deal with the output data here 
});
  1. http://knexjs.org/#Builder-select
  2. http://knexjs.org/#Builder-from
  3. http://knexjs.org/#Builder-innerJoin
  4. http://knexjs.org/#Builder-where
  5. http://knexjs.org/#Interfaces-then

Upvotes: 5

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

SELECT 
  a.driverID, a.dCarID, a.dDeviceID, b.carRegiNum 
FROM 
  driverProfile a, 
  carProfile b 
WHERE 
  a.dManagerID = 7 AND b.carID=a.dCarID

With knex 0.14.0:

knex({ a: 'driverProfile', b: 'carProfile' })
  .select('a.driverID', 'a.dCarID', 'a.dDeviceID', 'b.carRegiNum')
  .where('a.dManagerID', 7)
  .where('b.carID', knex.raw('??', ['a.dCarID']))

Generates (https://runkit.com/embed/b5wbl1e04u0v):

select 
  `a`.`driverID`, `a`.`dCarID`, `a`.`dDeviceID`, `b`.`carRegiNum` 
from 
  `driverProfile` as `a`, `carProfile` as `b` 
where 
  `a`.`dManagerID` = ? and `b`.`carID` = `a`.`dCarID`

Upvotes: 1

Related Questions