Can't join two tables in sqlite

Question

i have two tables:

CREATE TABLE "object_comment" 
("object_id" INTEGER PRIMARY KEY  NOT NULL, 
"object_comment_text" TEXT, 
"object_comment_title" TEXT, 
"object_comment_date" DATETIME)

and

CREATE TABLE "object_comment_mark" 
("object_id" INTEGER PRIMARY KEY  DEFAULT null, 
"object_comment_mark_value" FLOAT DEFAULT null, 
"object_comment_mark_date" DATETIME DEFAULT null)

I need to join them with the object_id field but the unique rows should present in results too. (there are some equal object_id values which I need to join in one row and some object_id values are different but they should be in the result table)

Now I have this select query:

SELECT *
FROM object_comment
LEFT OUTER JOIN object_comment_mark ON object_comment.object_id = object_comment_mark.object_id

But in this case I don't have the rows from the second table where the object_id has unique value. Any help?

EDIT: what I need

object_comment

1 | bla-bla | first | 2013
2 | be-be   | sec   | 2014

object_comment_mark

1 | 5 | 2013
4 | 3 | 2013

result

1 |bla-bla | first| 2013 | 5 | 2013
2 | be-be  | sec  | 2014 |   |     
4 |        |      |      | 3 | 2013

Answer 1

What you want is full outer join, which is not supported by SQLite. Instead, you could combine a left join and the unmatched (NULL) records of a right join. A right join isn't supported either, so use a left join with the two tables swapped:

SELECT oc.*, ocm.*
FROM object_comment AS oc
LEFT JOIN object_comment_mark AS ocm ON oc.object_id = ocm.object_id
UNION ALL
SELECT oc.*, ocm.*
FROM object_comment_mark AS ocm
LEFT JOIN object_comment AS oc       ON oc.object_id = ocm.object_id
WHERE oc.object_id IS NULL

Alternatively, search for unmatched records by hand:

SELECT oc.*, ocm.*
FROM object_comment AS oc
LEFT JOIN object_comment_mark AS ocm ON oc.object_id = ocm.object_id
UNION ALL
SELECT NULL, NULL, NULL, NULL, *
FROM object_comment_mark
WHERE object_id NOT IN (SELECT object_id
                        FROM object_comment) 

Answer 2

I'm not understanding exactly your question...

Is this you need?

SELECT * FROM object_comment
INNER JOIN object_comment_mark ON object_comment.object_id = object_comment_mark.object_id
UNION ALL
SELECT *, NULL, NULL, NULL, NULL FROM object_comment