CODEWITHSUNDEEP
ms-accessintersect
klausnrooster
klausnrooster

Reputation: 560

How to perform an INTERSECT query in Access SQL

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])

Upvotes: 2

Views: 1723

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123654

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])

Upvotes: 2

Related Questions