CODEWITHSUNDEEP
sqlsqlitejoininner-join
Snhp9
Snhp9

Reputation: 539

Doing a join in a couple of sqlite tables is not working

So I have this SQLite string

SELECT
a.anchor as [From Anchor],
c.anchor as [To Anchor]
FROM table1 a
JOIN table2 b ON b.toAnchor = a.anchor
JOIN table3 c ON c.anchor = b.fromAnchor

And there is no error when I execute it but it returns an empty table. There is data in all 3 of the tables and the data does match where it is supposed to but I get nothing.

Table1

filename | farea | anchor
_________________________
file.doc   N/A     srs100

Table2

type | filename | fromAnchor | toAnchor
_______________________________________
SRS      N/A       srs100      crs888

Table3

filename | farea | anchor
_________________________
file.doc   N/A     crs888

I know I could just use table 2 but I need it to be done this way to find areas where the from and to don't match.

If I cant find a way of doing it like this then I would like to find out a way that I can combine 2 tables so it would like this.

table1

type | filename | fromAnchor | toAnchor
_______________________________________
SRS      N/A       crs100      srs888

table2

type | filename | fromAnchor | toAnchor
_______________________________________
CRS      N/A       srs888      srd999

result

crs    | srs    | srd
_______________________________________
crs888  srs888   srd999

I can provide more info if needed. For now I would like to know if the sql call is correct.

Upvotes: 0

Views: 795

Answers (3)

Snhp9
Snhp9

Reputation: 539

Turns out that the data in the database had been configured incorrectly. This turned out to work. 

SELECT
TABLE1.toAnchor AS CRS,
TABLE1.fromAnchor AS SRS
FROM tbl_traces_srstraces
LEFT JOIN TABLE2 ON TABLE2.anchor = TABLE1.toAnchor
LEFT JOIN TABLE3 ON TABLE3.anchor = TABLE1.fromAnchor

Upvotes: -1

Miller
Miller

Reputation: 1156

Try this

SELECT 
fromAnchor, 
toAnchor 
from table2 a
join table1 b on  a.fromAnchor =b.anchor
join table3 c on a.toAnchor =c.anchor

Upvotes: 1

Mihai
Mihai

Reputation: 26804

SELECT
a.anchor as [From Anchor],
c.anchor as [To Anchor]
FROM table1 a
JOIN table2 b ON b.fromAnchor = a.anchor
JOIN table3 c ON c.anchor = b.toAnchor

Upvotes: 2

Related Questions