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

Question

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.

Answer 1

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

Answer 2

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`