MySQL Forums
Forum List  »  Newbie

Selecting rows from one table based on two columns in another table
Posted by: Thomas Stieve
Date: November 19, 2018 12:14PM

Hello all,

I have two tables, for example:

Pages
title, page_id, year, ipnum
'Sophie_Daumier', '43865', '2016', '1405562816'
'Sophie_Daumier', '43865', '2016', '1430582913'
'Sophie_Daumier', '43865', '2016', '2588162115'

Reversions:
Ipnum, page_id
'00000000001430582913', '43865'

Basically I want a new table of records from the table Pages that are not in Reversions based on the variable Ipnum and page_id

This seems to work:

SELECT *
FROM pages
WHERE page_id IN
(SELECT page_id
FROM reversions)
and ipnum IN
(SELECT ipnum
FROM reversions)

It produces:
'Sophie_Daumier', '43865', '2016', '1430582913'

Two questions:

1.However, I am confused why when I include "NOT IN", I get nothing as a result. Wouldn't the other two records show up under this query?
2. This is for extremely large datasets. Is this the best method?

Your help is much appreciated.
Tom

Options: ReplyQuote


Subject
Written By
Posted
Selecting rows from one table based on two columns in another table
November 19, 2018 12:14PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.