Help to query using != expression

I got one table where the data is updated after every 15 min in the form of server name and othere columns likes rows inserted.

This is basically a logtable file which logs all the entries of the data collection happenning for a list of servers.

Now i want to find out what are all the servers which are missing in that table in comparison with the table which has the original list of all servers.

when i compare them using table1.rowname = table2.rowname it is working that is it is showing the list of servers that are common in both tables.

but when i use a table1.rowname != table2.rowname or table1.rowname <> table2.rowname it is combinning both the entries and showing in to one.in simple words it is doing a UNION operation


How do i change this query ?

Regards
Praveen

assuming that you are joining two tables with "table1.rowname = table2.rowname" predicate then you should consider using set operators IN, NOT IN or EXISTS like so:

select rowname from table1 where rowname not in (select rowname from table2)

Good luck
aka.

Praveen wrote:

> I got one table where the data is updated after every 15 min in the form
> of server name and othere columns likes rows inserted.
>
> This is basically a logtable file which logs all the entries of the data
> collection happenning for a list of servers.
>
> Now i want to find out what are all the servers which are missing in that
> table in comparison with the table which has the original list of all
> servers.
>
> when i compare them using table1.rowname = table2.rowname it is working
> that is it is showing the list of servers that are common in both tables.
>
> but when i use a table1.rowname != table2.rowname or table1.rowname <>
> table2.rowname it is combinning both the entries and showing in to one.in
> simple words it is doing a UNION operation

No, using "expr <> expr" does _not_ do a UNION operation. You should read
up on what UNION does, what joins are, and how join predicates apply. In
short: if you join table1 and table2, you get the cross product of all rows
in table1 with all rows in table2. The "... <> ..." only filters out those
rows from the cross product where the rowname is not the same in both
original tables.


Think again about what you want to achieve: get records from table1
(original table) for which there is no matching record in table2 (log
table). So just do exactly that:

SELECT a, b, c
FROM table1
EXCEPT
SELECT a, b, c
FROM table2

This assumes that you have the same structure in table1 and table2, or
rather the same columns and types in the select list of both subselects.
If that is not the case, a simple NOT IN predicate could work:

SELECT ...
FROM table1
WHERE rowname NOT IN ( SELECT rowname
FROM table2 )

p.s: I would recommend that you grab a book that introduces the relational
data model. Understanding relational algebra is extremely helpful to
understand SQL.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Dear Mr Stolze,

Thank you very much for the help.I was able to trace that out on my own after reffering the book.It is same as the solution you have provided.


I welcome your suggestion to understand relataional data model,Will work on that.Is there any website link or redbook that you can suggest to understand this better.

Regards