0

I have coded a number of texts in RQDA and I am trying to export the categories in a database. I would be looking as an final result to be a Data Frame something like this:

Files   Categories
File1   Category1
File2   Category2
File3   Category3
File4   Category2
File5   Category1

I tried the following code which I have adapted from here Rblogger:

categories <- RQDAQuery("select filecat.name as category, source.name as filename 
                         from treefile, filecat, source 
                         where treefile.catid=filecat.catid and treefile.fid=source.id and treefile.status=1")

But it has resulted in an empty file so far:

str(categories)
'data.frame':   0 obs. of  2 variables:
 $ category: chr 
 $ filename: chr 

> dim(categories)
[1] 0 2
> summarise(categories)
data frame with 0 columns and 1 row

Any help is welcome.

Parfait
  • 104,375
  • 17
  • 94
  • 125
RodLL
  • 110
  • 8
  • Obviously, query returns zero results and actually that SQL is the older version of the `INNER JOIN` query. Please show sample of *treefile*, *filecat*, *source* tables/data. – Parfait Feb 12 '20 at 15:12
  • Thank you. I am not very sure how shall I get this information. Would you help? – RodLL Feb 13 '20 at 16:42

1 Answers1

1

Some friend in an old email list was kind enough to help me, so here goes the answer:

codings <-RQDAQuery("select s.name as 'filename', f.name as 'codes' from source s, 
                   coding c, 
                   freecode f where s.id = c.fid and c.cid = f.id and s.status = 1 order by s.name")

categories <- RQDAQuery("select s.name as 'filename', 
                    co.name as 'categories' from source s, 
                    coding c, freecode f, codecat co, 
                    treecode tr where s.id = 
                    c.fid and c.cid = f.id and co.catid = 
                    tr.catid and tr.cid = f.id and s.status = 1 and c.status = 1 and 
                    f.status = 1 order by s.name")

All the best

RodLL
  • 110
  • 8
  • Consider not using [implicit joins but explicit joins](https://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins) with `INNER JOIN` for readability and maintainability. Also, for SO answers please explain in words the solution for future readers and what was the issue in original problem. – Parfait Feb 19 '20 at 15:20