2

For example, if we have two tables: Table1 and table 2

Table1 - main table (with unique entry for all records) table2 - is a history table(where is containg many entries for same record on time stampe basis) (also contains some specific field type)

I need to retreive data like, all data from table1 where there is no entry for the record with type = 'Some vaue'

McNets
  • 23,749
  • 10
  • 48
  • 88
thiyaga rajan
  • 31
  • 1
  • 1
  • 2
  • 2
    Hi there, welcome to the site. Which RDBMS are you using (MySQL, SQL Server, Oracle)? Also please add some sample data and expected output as well as table definitions. Please see help us help you – Tom V Dec 19 '16 at 15:17
  • What have you tried? What was the result, and how was it different from what you'd expected? – mustaccio Dec 19 '16 at 19:42

2 Answers2

7

I typically write this as NOT EXISTS query because this aligns with the wording of the problem ("find everything in table1 where no corresponding row exists in table2")

select t1.* 
from table1 t1
where not exists (select *
                  from table2 t2
                  where t1.id = t2.table1_id
                    and type = 'Some Value');

Depending on your DBMS, mcNets' solutions might be faster - that depends a lot on the query optimizer, in Oracle for example it won't make any difference.

3

Basically you could use a LEFT JOIN to get all records from table1 and then play with IS NULL and IS NOT NULL to get all existing and not existing related values in table2.

SELECT table1.id
FROM table1
    LEFT JOIN table2
    ON table2.table1_id = table2.id
WHERE
    table2.table1.id is null

You can also use GROUP BY and COUNT() > 0 or COUNT() = 0

SELECT table1.id, count(table2.table1_id) as records_in_table2
FROM table1
    LEFT JOIN table2
    ON table2.table1_id = table1.id
GROUP BY 
    table1.id
HAVING 
    COUNT(table2.table1_id) > 0
McNets
  • 23,749
  • 10
  • 48
  • 88