Hello,
I want to LEFT OUTER JOIN two tables in SQLite but want to exclude the first row from each table (the first row in each table has the column rowid = 1).
The result of the join is written to the Joined table.
When doing an INNER JOIN using the code below, the first rows (with rowid = 1) indeed are excluded from the result:
INSERT INTO Joined
SELECT * FROM t1 INNER JOIN t2
ON t1.t1_col_1 = t2.t2_col_1
AND (t1.rowid > 1) AND (t2.rowid > 1)
When doing a LEFT OUTER JOIN using the code below, the first rows (with rowid = 1) are not excluded from the result:
INSERT INTO Joined
SELECT * FROM t1 LEFT OUTER JOIN t2
ON t1.t1_col_1 = t2.t2_col_1
AND (t1.rowid > 1) AND (t2.rowid > 1)
What am I doing wrong with the LEFT OUTER JOIN?
You should ask this question at:
https://sqlite.org/forum
rather than here as its a purely SQL/SQLite question. You’ll get betters answers too, I suspect, as the SQLite devs hang out there too.
Why GREATER THAN 1 ? (that may mean start at 2)
Isnt it here the problem ?
Try => (or is it >=)
Haven’t tested but how about:
INSERT INTO Joined
SELECT * FROM t1 LEFT OUTER JOIN t2
ON t1.t1_col_1 = t2.t2_col_1
WHERE t1.rowid > 1 AND t2.rowid > 1
Thanks for the replies.
Sometimes the first rows contain garbage, so I want to be able to exclude them.
@Frederick Roller
INSERT INTO Joined
SELECT * FROM t1 LEFT OUTER JOIN t2
ON t1.t1_col_1 = t2.t2_col_1
AND t1.rowid > 1
and
INSERT INTO Joined
SELECT * FROM t1 LEFT OUTER JOIN t2
ON t1.t1_col_1 = t2.t2_col_1
WHERE t1.rowid > 1
both have the same effect and work as expected. The result is a LEFT OUTER JOIN where the first row of t1 is excluded.
When I add " AND t2.rowid > 1 " to any of the two examples above, then the result is no more a LEFT OUTER JOIN but seems to be an INNER JOIN.
So the confusing part occurs, when the last condition " AND t2.rowid > 1 " is added.
Ok, after reading this article:
https://mode.com/sql-tutorial/sql-joins-where-vs-on/
and after some tests the following query seems to deliver the expected result:
INSERT INTO Joined
SELECT * FROM t1 LEFT OUTER JOIN t2
ON t1.t1_col_1 = t2.t2_col_1
WHERE (t1.rowid > 1) OR (t2.rowid > 1)
I totally do not understand:
You complain Row 1 is skipped (Subject line)
and
You skip Row 1 ?
Where am I wrong ?