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.

Paul Maxwell · Accepted Answer · 2020-06-06T02:49:23.907

5

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 
});

edited Jun 06 '20 at 02:49

answered Nov 03 '17 at 07:21

Paul Maxwell

33,002
3
32
51

admission: never seen knex before, so that is definitely untested – Paul Maxwell Nov 03 '17 at 07:23
It works like a charm. I don't know how to thank you enough. I will dive deep into joins. Thank you again. – psytron Nov 05 '17 at 05:51
There's heaps of info regarding joins around. Try this **[visual guide to joins](https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins)** and [SQL JOIN and different types of JOINs](https://stackoverflow.com/questions/17946221/sql-join-and-different-types-of-joins) – Paul Maxwell Nov 05 '17 at 05:54
oh, and forget about commas between table names, first rule :) – Paul Maxwell Nov 05 '17 at 05:56
This works for that query in specific but in update queries you can't do joins, and if you select by 2 equal tables your query must be aliased. – Onza Jun 05 '20 at 15:00
@Onza the query for this question is a select statement and joins are freqently needed in those. Not sure why you are mentioning an update statement restriction. It is not all that uncommon for joins to be disallowed in update statements. – Paul Maxwell Jun 06 '20 at 02:45
For aliases there is a way to do these refer to this https://stackoverflow.com/questions/28357228/alias-a-table-in-knex – Paul Maxwell Jun 06 '20 at 02:48
@Used_By_Already I mean I came here by googling the question, "How to select columns from multiple tables?" a join is not the same as a select by multiple tables and it's in the SQL standard from rule, sure a join works for this case and it's the best answer for the asker to be honest, it just doesn't cover all possible cases. However if you use a double join to the same table, eg. a one to many relationship you need aliases. – Onza Jun 09 '20 at 10:37
I think it's not possible to do so with knex, and the only option is knex.raw... since I couldn't find a way to select by multiple tables... (not joining) – Onza Jun 09 '20 at 10:38

score 1 · Answer 2 · answered Nov 07 '17 at 21:04

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`

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

2 Answers2