0

I have 2 tables in 2 databases:

table call in history.db:

ROWID | ADDRESS | DATE 
1     | +98765  | 1396771532    
2     | +98765  | 1396771533    
3     | +98765  | 1396771534    
4     | +98765  | 1396771535    
5     | +98765  | 1396771536    
6     | +98765  | 1396771537    
7     | +98765  | 1396771538    
8     | +98765  | 1396771539    
9     | +98765  | 1396771510

table info in voices.db:

ID    | CALLID  | PATH       | CODE    
1     | 2       | voice1.m4a | 12234    
2     | 5       | voice2.m4a | 12234    
3     | 1       | voice4.m4a | 89765

First, I did an attach:

conn = sqlite3.connect("history.db")
conn.row_factory = sqlite3.Row
cursor = conn.cursor()

cursor.execute("attach ? as voice", ("voices.db",))
conn.commit()

Then, I joined 2 tables:

cursor.execute("SELECT * FROM call c JOIN (SELECT PATH, CALLID, CODE FROM voice.info WHERE CODE = ?) f ON c.ROWID = f.CALLID ORDER BY c.DATE", ("12234",))

So, I got the following result:

ROWID | ADDRESS | DATE       | PATH       | CALLID | CODE    
2     | +98765  | 1396771533 | voice1.m4a | 2      | 12234    
5     | +98765  | 1396771536 | voice2.m4a | 5      | 12234

But, I need a full outer join to get something like:

ROWID | ADDRESS | DATE       | PATH       | CALLID | CODE    
1     | +98765  | 1396771532 | NULL       | NULL   | NULL    
2     | +98765  | 1396771533 | voice1.m4a | 2      | 12234    
3     | +98765  | 1396771534 | NULL       | NULL   | NULL    
4     | +98765  | 1396771535 | NULL       | NULL   | NULL    
5     | +98765  | 1396771536 | voice2.m4a | 5      | 12234    
6     | +98765  | 1396771537 | NULL       | NULL   | NULL    
7     | +98765  | 1396771538 | NULL       | NULL   | NULL    
8     | +98765  | 1396771539 | NULL       | NULL   | NULL    
9     | +98765  | 1396771510 | NULL       | NULL   | NULL

I tried this, but I got an error of ... UNION do not have same number result columns ....

How could I have a full outer Join?

I am using Python 2.7, so

Right and FULL OUTER JOINs are not currently supported

Community
  • 1
  • 1
  • The answer you linked to is correct. Show what you've tried. – CL. May 28 '14 at 12:47
  • @CL - I had problem with selecting from voice.info using conditions, I mean I couldn't match my code with that answer –  May 28 '14 at 12:54

1 Answers1

2

Your original query is badly written. Write it like this:

SELECT c.*, i.* FROM CALL c
JOIN voice.info i ON i.CODE = ? AND c.ROWID = i.CALLID
ORDER BY c.DATE;

Now transforming it into the full outer join as in the answer you linked is trivial:

SELECT c.*, i.* FROM CALL c
LEFT JOIN voice.info i ON c.ROWID = i.CALLID AND i.CODE = ?
UNION
SELECT c.*, i.* FROM voice.info i 
LEFT JOIN CALL c ON c.ROWID = i.CALLID
WHERE i.CODE = ?;

In the answer you linked they use UNION ALL, which keeps duplicates in the result set. I don't think you want that, so therefore I prefer to use UNION, which removes duplicates (rows where all the columns are equal).

Also: it's actually even better to write out all columns instead of using *, but I didn't do that here for brevity.

wvdz
  • 16,251
  • 4
  • 53
  • 90
  • I got the following error: `Right and FULL OUTER JOINs are not currently supported` –  May 28 '14 at 13:02
  • Thanks, But I got 2 rows like that I mentioned in my question –  May 28 '14 at 13:13
  • What does the first part of the query return? `SELECT c.*, i.* FROM CALL c LEFT JOIN voice.info i ON c.ROWID = i.CALLID AND i.CODE = ?` – wvdz May 28 '14 at 13:55
  • It returns those 2 rows, too. I think `UNION` does not work properly here. –  May 28 '14 at 16:50