SQL newbbie question 2

[pedrosolorzano]

Hi All:

I don't know which forum is the right one to post this question, in fact, I post it here because I usually work with PostgreSQL, but it is a really newbbie question about SQL:

Say I have a table (or view) "A" with one column which content are numbers from 1 to 5:
A =

number
-----+
1
2
3
4
5

If i have another table B with the same column but numbers from 1 to 10:

B=
number
-----+
1
2
3
4
5
6
7
8
9
10


How can I get B-A?
In other words, how is the query to get:
number
-----+
6
7
8
9
10

from the join of the A and B tables?

I mean,

"what IS in B and IS NOT in A",

the complement or difference or something similar.

I know it is a dummie question but I really appreciate any help.

Thanks in advance for any answer.

Best Regards.

 

[WaltK]

One way to do this is, given your example tables:

SELECT number FROM A
WHERE number NOT IN (SELECT number FROM b)

Of course this implies that b is always a subset of a.

 

[WaltK]

Sorry - reverse tables a and b in my previous response to your question. I guess I didn't have enough coffee.

 

[ceco]

except the way WaltK wrote, another one is availavle

SELECT number FROM b EXCEPT SELECT number FROM a

you may check which is faster with EXPLAIN

 

[pedrosolorzano]

Thank you very much WaltK and ceco. Star for you both!

 

[dsanchez2]

You can also do this with:

SELECT number FROM B
EXCEPT
SELECT number FROM A;

As the documentation saids: EXCEPT is similar to UNION, except that it produces only rows that appear in the left query's output but not in the right query's output.

 

[pedrosolorzano]

Thanks dsanchez2, I quote ceco:

"another one is availavle

SELECT number FROM b EXCEPT SELECT number FROM a"

Anyway, do you have a link to the documentation page where you find the explanation?

Thanks again.

 

[ceco]

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=sql-select.html#SQL-EXCEPT

http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=typeconv-union-case.html

 

[pedrosolorzano]

Thanks again ceco.

Pedro Andrés Solorzano
Pontificia Universidad Javeriana
Bogotá, Colombia, SurAmérica.

Let me know if this post have actually helped you, by clicking the purple star.

 

[Sanate]

We have an intranet application here which I work on.

When PHP sends pq_querry to SELECT ALL documents which contents a word "worflow", PostgreSQL server crashed.

I would be glad if someone refers me to some explanation or solution.

Or maybe recommend me how to overcome this.

this is the error message:

"Warning: pg_query() [function.pg-query]: Query failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. . in /usr/local/apache2/htdocs/include/db.php on line 17

Chyba pøi zápisu do logu. //means: Error writing to log"


Thank You.


yogale Sanate

 

[Sanate]

I am sorry for mispelling the word "workflow"!

We use here pgSQL 7.3.2 and PHP 4.3.1 configured as follows:

System Linux tucnak.ourdomain.cz 2.4.20 #3 Mon May 12 21:51:43 CEST 2003 i686
Build Date May 19 2003 19:24:41

Configure Command './configure' '--with-apache2=/usr/src/httpd-2.0.45' '--with-pqsql=shared' '--enable-track-vars' '--with-xml' '--enable-sockets' '--enable-gd-imgstrttf' '--enable-ftp' '--with-imap=/usr/src/imap-2002c1/' '--with-gd' '--with-ttf' '--enable-calendar' '--with-zlib-dir=/usr' '--with-jpeg-dir=/usr' '--with-imap-ssl' '--with-openssl' '--with-apxs2=/usr/local/apache2/bin/apxs' '--with-pgsql'

Server API Apache 2.0 Filter
Virtual Directory Support disabled

PHP API 20020918
PHP Extension 20020429
Zend Extension 20021010

Debug Build no
Thread Safety disabled

Registered PHP Streams php, http, ftp, https, ftps, compress.zlib
--------------------------------------------------
End.

yogale Sanate