0

Forgive my ignorance, I am new to RDMS and SQL both.
My question is this:
I have 2 tables, one a primary table and 1 a relational table
table 1 has ten rows valued 1,2,3,4,5,6,7,8,9,10
table 2 has 4 rows keying from table 1
row1 = 1 2
row2 = 3 4
row3 = 5 6
row4 = 9 10

row 7 and 8 do not show up in table 2
how would I write a query to tell me that only row 7 and row 8
are not a from/to row in table 2??
I should only get 2 rows back.

Thanks for your help

2
Contributors
5
Replies
6
Views
10 Years
Discussion Span
Last Post by pty
0

Forgive my ignorance, I am new to RDMS and SQL both.
My question is this:
I have 2 tables, one a primary table and 1 a relational table
table 1 has ten rows valued 1,2,3,4,5,6,7,8,9,10
table 2 has 4 rows keying from table 1
row1 = 1 2
row2 = 3 4
row3 = 5 6
row4 = 9 10

row 7 and 8 do not show up in table 2
how would I write a query to tell me that only row 7 and row 8
are not a from/to row in table 2??
I should only get 2 rows back.

Thanks for your help

Hi,

Could you give us the names of the columns? I'm not sure I understand exactly what you're doing.

0

OK, TABLE 1 HAS ONE FIELD CALLED TASK.
THE FIRST ROW HAS A TASK OF 1, NEXT ROW HAS A TASK OF 2 , AND SO FOR 10 ROWS.
tABLE 2 (RELATIONAL TABLE) HAS TWO FIELDS PER ROW. tHEY ARE TASK-1 AND TASK-2 (WHICH GET PERFORMED TOGETHER)
tHERE IS ALSO A FOREIGN KEY CONSTRAINT MAKING SURE THAT TASK-1 AND TASK-2 EXIST IN TABLE 1.
NOW IF TABLE 2 HAS 4 ROWS:
ROW1 TASK1=1 AND TASK2=2
ROW2 TASK1=3 AND TASK2=4
ROW3 TASK1=5 AND TASK2=6
ROW4 TASK1=9 AND TASK2=10
IN THIS SCENARIO TABLE 2 IS MISSING 2 TASKS FROM TABLE 1 (7 AND 8)
I NEED TO WRITE A QUERY THAT WILL TELL ME THAT TASKS 7 AND 8 FROM TABLE 1 DO NOT APPEAR IN ANY TASK1 OR TASK2 IN TABLE2.
I HOPE THIS IS NOT TOO CONFUSING.
tHANKS

0

Ok,

For this I've called the tables t1 and t2.

select * 
from t1 
where task not in (
  select task1 
  from t2 
  union 
  select task2 
  from t2
);
0

I Just Got An Error Saying That The Union Operator Cannot Be Used Inside A Subquery

0

I Just Got An Error Saying That The Union Operator Cannot Be Used Inside A Subquery

Limitation of SQL server I presume (I use PostgreSQL).

Here is an ugly way to do it

select *
from t1
where task not in (select task1 from t2)
and task not in (select task2 from t2)
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.