Thread: 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
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 > >
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
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
>
>