How to perform an INTERSECT query in Access SQL

Question

I have this Python snippet for querying SQLite tables 'note', 'tag', and the many-to-many join table 'fkeys'. Very procedural I guess, but it works. Now I want to do a similar thing in Access but Jet SQL does not support INTERSECT.

I have been trying code from similar posts here for a few hours. None of the WHERE EXISTS or GROUP BY ... HAVING + subquery stuff works. SQL-92 option didn't help either. Can this be done in Jet SQL?

  db.execute('SELECT DISTINCT n.rowid as rowid, n.note_txt as note_txt,\
  date(n.timestamp) as timestamp\
  FROM note n\
  JOIN fkeys f\
  ON n.rowid = f.note_id\
  JOIN tag t\
  ON t.rowid = f.tag_id\
  WHERE t.tag_text = ?\
INTERSECT  \
  SELECT DISTINCT n.rowid as rowid, n.note_txt as note_txt,\
  date(n.timestamp) as timestamp\
  FROM note n\
  JOIN fkeys f\
  ON n.rowid = f.note_id\
  JOIN tag t\
  ON t.rowid = f.tag_id\
  WHERE t.tag_text = ?\
INTERSECT\
  SELECT DISTINCT n.rowid as rowid, n.note_txt as note_txt,\
  date(n.timestamp) as timestamp\
  FROM note n\
  JOIN fkeys f\
  ON n.rowid = f.note_id\
  JOIN tag t\
  ON t.rowid = f.tag_id\
  WHERE t.tag_text = ?\
  ORDER BY timestamp',[srchtxt0,srchtxt1,srchtxt2])

Gord Thompson · Accepted Answer · 2014-03-02T16:47:15.173

2

I believe that the following should work against an ACE/Jet database:

sql = """
SELECT rowid, note_txt, timestamp
FROM [note]
WHERE
    rowid IN
        (
            SELECT f.note_id
            FROM 
                [fkeys] AS f 
                INNER JOIN 
                [tag] AS t 
                    ON t.rowid = f.tag_id 
            WHERE t.tag_text = ?
        )
        AND rowid IN
            (
                SELECT f.note_id
                FROM 
                    [fkeys] AS f 
                    INNER JOIN 
                    [tag] AS t 
                        ON t.rowid = f.tag_id 
                WHERE t.tag_text = ?
            )
        AND rowid IN
            (
                SELECT f.note_id
                FROM 
                    [fkeys] AS f 
                    INNER JOIN 
                    [tag] AS t 
                        ON t.rowid = f.tag_id 
                WHERE t.tag_text = ?
            )
ORDER BY 3 
"""
cursor1 = db.execute(sql,[srchtxt0,srchtxt1,srchtxt2])

edited Mar 02 '14 at 16:47

answered Mar 02 '14 at 14:04

Gord Thompson

116,920
32
215
418

Tried first, cut down to 2 tags to search, and converted to Jet SQL and a few naming detail tweaks: `SELECT DISTINCT n.[rowid] AS rowid, n.[notetext] AS notetext, n.[timestamp] FROM ([note] AS n INNER JOIN NoteTag AS f ON n.[rowid] = f.[noteid]) INNER JOIN tag AS t ON t.[rowid] = f.[tagid] WHERE t.tagtext In ('searchtxtliteral1','searchtxtliteral2');` It doesn't filter for notes having both tags. It returns note having either tag. The UNION ALL thing will of course do that too. – klausnrooster Mar 02 '14 at 16:09
1

@klausnrooster My apologies. I have updated my answer. – Gord Thompson Mar 02 '14 at 16:40
1

In case anybody has a similar case, I don't always have 3, or even 2 tags to search on. So in VBA (a button_click event) concatenate the SQL together with a recursive function which allows any number of tags. But if you'd rather a saved parameterized query with a static tag count limit, just set unused tag-params redundantly to a tag you have. With my prior limit of 3 that has worked fine. – klausnrooster Mar 02 '14 at 17:40

How to perform an INTERSECT query in Access SQL

1 Answers1