SELECT FROM 2 TABLES and verify the combination in a 3th table

Question

I have three tables : table_1 (id_1, col_1) , table_2 (id_2, col_2) and a third table which has some relation like:
table_3 (id_tbl3, col_1, col_2)

In table 3 there are some values from table_1 combined with some values from table_2.

I want to create a query whch is able to make a table with all the combination between table_1 and table_2 and that are not already in table_3
The query is like this:

SELECT id_1, col_1, id_2, col_2 FROM table_1, table_2 WHERE (combination id_1, id_2 NOT IN table_3)

Can somebody help me please?

---

Thanks in advance, Ciprian LUPU

Answer 1

Hi,

please try this query

select a.*,b.* from table_1 a,table_2 b where (a.col_1,b.col_2) not in (select col_1,col_2 from table_3) 

---

Best Regards,
Silna
Please feel free to ask if you have any doubts and remember to mark the correct replies as answers.

Answer 2

SELECT id_1, col_1, id_2, col_2 FROM table_1 JOIN  table_2 ON table_1.PK=table_2.pk

LEFT JOIN table_3 on table_1.id_1=table_3.id_3 OR  table_2.id_2=table_3.id_3

 ---pk Primary Keys

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

Hi,

Try this

select * from 
     Table_1 INNER JOIN Table_2 on id_1=id_2
where cast(id_1, varchar)+cast(id_2, varchar)
not in select (cast(id_1, varchar)+cast(id_2, varchar) from Table_3)

Regards
Satheesh

Answer 4

This is not running as I am expecting. :(

---

Thanks in advance, Ciprian LUPU

Answer 5

Also this is not running well... Not the expevcted result :(

---

Thanks in advance, Ciprian LUPU

Answer 6

SELECT T1.id_1, T1.col_1, T2.id_2, T2.col_2 FROM table_1 T1, table_2 T2-- note, this is CROSS JOIN


 WHERE not exists (select 1 from Table_3 T3
where T3.Col_1 = T1.Col_1 and T3.Col_2 = T2.Col_2)

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 7

Not sure whether you are looking for something like this or not -

;WITH CTE AS
(
SELECT DISTINCT a.Id_1,a.Col_1,b.Id_1,b._Col2
FROM table1 AS a
 INNER JOIN table2 AS b
  ON a.Id_1=b.Id_1
   AND a.Id_2=b.Id_2
)
SELECT * 
FROM CTE AS d 
WHERE 0=(SELECT COUNT(*)
		 FROM table3 AS c
		 WHERE d.Col_1=c.Col_1
			ADN d.Col_2=b.Col_2
		)

---

Narsimha