RE: sql with postgresql beginner

Apparently this didn't get sent to the list on first attempt.  Will try
again....


Well, the "from" clause is basically a join, but it does not restrict the
output.  Unless restricted by a "where" clause, a regular join will return
ALL the rows of the first table, joined with ALL the rows of the second
table.  If the first table has 2 rows and the second table has 3 rows, you
end up with 6 rows.

You need to add more to your where clause.  I think the following should do
it:

select list.item, vendors.vendorname
      from list, vendors
      where list.vendorcode = vendors.vendorcode and list.vendorcode = 101;

To understand the process better, try it with no where clause at all, then
add the where conditions one at a time, saving the list.vendorcode = 101 for
last.

Using an explicit JOIN as suggested by Windy is another way of getting the
same result.  Using that syntax, you can also do different kinds of joins,
which produce different results in cases where one table has records that
have no matching records in the other table.

           -- Warren


-----Original Message-----
From:    pgsql-novice-owner@hub.org [mailto:pgsql-novice-owner@hub.org] On
Behalf Of John
Sent:    Wednesday, August 30, 2000 5:44 PM
To:    pgsql-novice@postgresql.org
Subject:    Re: [NOVICE] sql with postgresql beginner

I just got it with

select list.item, vendors.vendorname
   from list, vendors
   where list.vendorcode = 101 and vendors.vendorcode = 101;

I thought that the 'from' clause was a join?

Back to the docs for me I think.

----- Original Message -----
From: Windy Elliott <windye@softshare.com>
To: <radix@ihug.com.au>
Cc: <pgsql-novice@postgresql.org>
Sent: Thursday, August 31, 2000 7:55 AM
Subject: RE: [NOVICE] sql with postgresql beginner


> I am a begginer as well, but I am under the impression that you actually
> need to join the tables.
>
> select list.item, vendors.vendorname from list LEFT JOIN vendors ON
> list.vendorcode = vendors.vendorcode where list.vendorcode = 101;
>
> This is how I would do it.  If I am wrong, please, someone correct me.
>
> Windy
>
> -----Original Message-----
> From: radix@ihug.com.au [mailto:radix@ihug.com.au]
> Sent: Wednesday, August 30, 2000 3:19 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] sql with postgresql beginner
>
>
> Could somebody comment on why my SQL query hasn't produced the desired
> result (apart from pointing out that my SQL skills suck)
>
>
> I've listed the two tables I'm using (created them from an example
> article at www.networkcomputing.com) and the select statement which
> isn't producing the correct result.
>
>
> list table
> ==========
>     item     | vendorcode | quantity
> -------------+------------+----------
>  Root Beer   |        100 |        3
>  Ice Cream   |        100 |        1
>  Napkins     |        101 |       50
>  Spark Plugs |        102 |        4
>
>
> vendors table
> =============
>  vendorcode |        vendorname
> ------------+---------------------------
>         100 | Super Grocer
>         101 | Genernal Department Store
>         102 | General Auto Parts
>
>
> Now here's the select statement which I thought should produce just
> one row containing "Napkins | Genernal Department Store"
>
> select list.item, vendors.vendorname
>      from list, vendors
>      where list.vendorcode = 101;
>   item   |        vendorname
> ---------+---------------------------
>  Napkins | Super Grocer
>  Napkins | Genernal Department Store
>  Napkins | General Auto Parts
>
>