SQL statement to split a table based on a join

Posted by williamjones on Stack Overflow See other posts from Stack Overflow or by williamjones
Published on 2010-03-20T18:02:57Z Indexed on 2010/03/20 18:21 UTC
Read the original article Hit count: 267

Filed under:
|
|
|
|

I have a primary table for Articles that is linked by a join table Info to a table Tags that has only a small number of entries. I want to split the Articles table, by either deleting rows or creating a new table with only the entries I want, based on the absence of a link to a certain tag. There are a few million articles. How can I do this?

Not all of the articles have any tag at all, and some have many tags.

Example:

table Articles
  primary_key id
table Info
  foreign_key article_id
  foreign_key tag_id
table Tags
  primary_key id

It was easy for me to segregate the articles that do have the match right off the bat, so I thought maybe I could do that and then use a NOT IN statement but that is so slow running it's unclear if it's ever going to finish. I did that with these commands:

INSERT INTO matched_articles SELECT * FROM articles a LEFT JOIN info i ON a.id = i.article_id WHERE i.tag_id = 5;
INSERT INTO unmatched_articles SELECT * FROM articles a WHERE a.id NOT IN (SELECT m.id FROM matched_articles m);

If it makes a difference, I'm on Postgres.

© Stack Overflow or respective owner

Related posts about sql

Related posts about postgresql