Обсуждение: Complex Query Help- For Me, Anyway
i'm having a problem sorting out a query - the biggest problem is that i'm not wrapping my mind around the thought process required to solve the issue (hint - step by step thought process guidance is what is really important to me, not so much the answer - i want to be able to address this situation next time, too). the table structure (shortened for brevity): t_sn link_id serial_number t_link link_id job_number_id contract_id t_job_number job_number_id product_id t_product product_id product_number the links: t_sn.link_id <-> t_link.link_id t_link.job_number_id <-> t_job_number.job_number_id t_job_number.product_id <-> t_product.product_id i'm trying to create an array a multidimensional array of all serial numbers by product number. for example: prod1 sn1 sn2 sn3 prod2 sn4 sn5 sn6 this allows my user to choose a product and then view a select box populated with only the associated serial numbers. i *think* i'm trying to find product_id, product_number, sn_id, serial_number where the t_sn.link_id = t_link.link_id AND t_link.job_number_id = t_job_number.job_number_id AND t_job_number.product_id = t_product.product_id. i receive the following error: ERROR: missing FROM-clause entry for table "t_link" the last time i saw this error, a few people pointed me to the solution of using a subquery. excluding this error, i'm thinking i'm wrong b/c i don't see how the above "logic" can produce multiple serial number results. i'm not grasping what my thought process should be when i view this situation. any help is greatly appreciated. __________________________________________ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com
On Tue, 27 Dec 2005 10:29:44 -0800 (PST), operationsengineer1 wrote
> i'm having a problem sorting out a query - the biggest
> problem is that i'm not wrapping my mind around the
> thought process required to solve the issue (hint -
> step by step thought process guidance is what is
> really important to me, not so much the answer - i
> want to be able to address this situation next time,
> too).
>
> the table structure (shortened for brevity):
>
> t_sn
> link_id
> serial_number
>
> t_link
> link_id
> job_number_id
> contract_id
>
> t_job_number
> job_number_id
> product_id
>
> t_product
> product_id
> product_number
>
> the links:
>
> t_sn.link_id <-> t_link.link_id
> t_link.job_number_id <-> t_job_number.job_number_id
> t_job_number.product_id <-> t_product.product_id
>
> i'm trying to create an array a multidimensional array
> of all serial numbers by product number. for example:
>
> prod1
> sn1
> sn2
> sn3
>
> prod2
> sn4
> sn5
> sn6
>
> this allows my user to choose a product and then view
> a select box populated with only the associated serial
> numbers.
>
> i *think* i'm trying to find product_id,
> product_number, sn_id, serial_number where the
> t_sn.link_id = t_link.link_id AND t_link.job_number_id
> = t_job_number.job_number_id AND
> t_job_number.product_id = t_product.product_id.
>
> i receive the following error:
>
> ERROR: missing FROM-clause entry for table "t_link"
>
> the last time i saw this error, a few people pointed
> me to the solution of using a subquery.
>
> excluding this error, i'm thinking i'm wrong b/c i
> don't see how the above "logic" can produce multiple
> serial number results.
>
> i'm not grasping what my thought process should be
> when i view this situation.
>
> any help is greatly appreciated.
I don't have the experience to help you with the array portion of your question
but perhaps this query will help.
SELECT t_product.product_number,
t_sn.serial_number
FROM t_sn
LEFT JOIN t_link
ON ( t_sn.link_id = t_link.link_id )
LEFT JOIN t_job_number
ON ( t_link.job_number_id = t_job_number.job_number_id )
LEFT JOIN t_product
ON ( t_job_number.product_id = t_product.product_id )
ORDER BY product_number,
serial_number;
Add a WHERE BY clause if you only want data for one product_number.
HTH
Kind Regards,
Keith
--- Keith Worthington <keithw@narrowpathinc.com>
wrote:
> On Tue, 27 Dec 2005 10:29:44 -0800 (PST),
> operationsengineer1 wrote
> > i'm having a problem sorting out a query - the
> biggest
> > problem is that i'm not wrapping my mind around
> the
> > thought process required to solve the issue (hint
> -
> > step by step thought process guidance is what is
> > really important to me, not so much the answer - i
> > want to be able to address this situation next
> time,
> > too).
> >
> > the table structure (shortened for brevity):
> >
> > t_sn
> > link_id
> > serial_number
> >
> > t_link
> > link_id
> > job_number_id
> > contract_id
> >
> > t_job_number
> > job_number_id
> > product_id
> >
> > t_product
> > product_id
> > product_number
> >
> > the links:
> >
> > t_sn.link_id <-> t_link.link_id
> > t_link.job_number_id <->
> t_job_number.job_number_id
> > t_job_number.product_id <-> t_product.product_id
> >
> > i'm trying to create an array a multidimensional
> array
> > of all serial numbers by product number. for
> example:
> >
> > prod1
> > sn1
> > sn2
> > sn3
> >
> > prod2
> > sn4
> > sn5
> > sn6
> >
> > this allows my user to choose a product and then
> view
> > a select box populated with only the associated
> serial
> > numbers.
> >
> > i *think* i'm trying to find product_id,
> > product_number, sn_id, serial_number where the
> > t_sn.link_id = t_link.link_id AND
> t_link.job_number_id
> > = t_job_number.job_number_id AND
> > t_job_number.product_id = t_product.product_id.
> >
> > i receive the following error:
> >
> > ERROR: missing FROM-clause entry for table
> "t_link"
> >
> > the last time i saw this error, a few people
> pointed
> > me to the solution of using a subquery.
> >
> > excluding this error, i'm thinking i'm wrong b/c i
> > don't see how the above "logic" can produce
> multiple
> > serial number results.
> >
> > i'm not grasping what my thought process should be
> > when i view this situation.
> >
> > any help is greatly appreciated.
>
> I don't have the experience to help you with the
> array portion of your question
> but perhaps this query will help.
>
> SELECT t_product.product_number,
> t_sn.serial_number
> FROM t_sn
> LEFT JOIN t_link
> ON ( t_sn.link_id = t_link.link_id )
> LEFT JOIN t_job_number
> ON ( t_link.job_number_id =
> t_job_number.job_number_id )
> LEFT JOIN t_product
> ON ( t_job_number.product_id =
> t_product.product_id )
> ORDER BY product_number,
> serial_number;
>
> Add a WHERE BY clause if you only want data for one
> product_number.
>
> HTH
>
> Kind Regards,
> Keith
Keith, thanks. I have the array deal down pat, i just
need the ids and the name in the format id1, name1,
id2, name2. my php script handles the resulting
recordset and gets the data ready for my forms class
to manipulate it into linked selects.
back to the code... let me see if i understand...
SELECT t_product.product_number,
t_sn.serial_number
we'll go with your example since i can easily add in
the ids to fit my situation.
FROM t_sn
LEFT JOIN t_link
ON ( t_sn.link_id = t_link.link_id )
this pulls all the sns in the serial number table and
its respective link_id (or nothing, if it doesn't
exist), right?
LEFT JOIN t_job_number
ON ( t_link.job_number_id =
t_job_number.job_number_id )
this pulls all the sns in the serial number table and
its respective job_number_id (or nothing, if it
doesn't exist), right?
LEFT JOIN t_product
ON ( t_job_number.product_id =
t_product.product_id )
this pulls all the sns in the serial number table and
its respective product_id (or nothing, if it doesn't
exist), right?
ORDER BY product_number,
serial_number;
at this point, i have a "table" that lists every sn in
the sn table, along with its respective (if they
exist, otherwise NULL) link_id, job_number_id and
product_id, right?
something like:
sn, lk, jn, pn (ids)
1, 1, 1, 1
2, 2, 2, 1
3, 3, 2, 2
etc...
so now all i need to do is to sort through this
"table" with something like:
in order to get the data i need, i will want to sort
through the table in order to grab all sns with a pn =
1 (if i wanted the p/n represented by a pn_id of 1).
this must be when the WHERE BY clause comes into play.
i googled "where by" and didn't come up with anything
of substance. can you give me an example how i should
implement it here? is it:
WHERE BY t_product.product_id =
t_job_number.product_id
tia...
__________________________________________
Yahoo! DSL Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
On Tue, 27 Dec 2005 13:46:27 -0800 (PST), operationsengineer1 wrote
> --- Keith Worthington <keithw@narrowpathinc.com>
> wrote:
>
> > On Tue, 27 Dec 2005 10:29:44 -0800 (PST),
> > operationsengineer1 wrote
> > > i'm having a problem sorting out a query - the
> > > biggest problem is that i'm not wrapping my
> > > mind around the thought process required to
> > > solve the issue (hint - step by step thought
> > > process guidance is what is really
> > > important to me, not so much the answer - i
> > > want to be able to address this situation next
> > > time, too).
> > >
> > > the table structure (shortened for brevity):
> > >
> > > t_sn
> > > link_id
> > > serial_number
> > >
> > > t_link
> > > link_id
> > > job_number_id
> > > contract_id
> > >
> > > t_job_number
> > > job_number_id
> > > product_id
> > >
> > > t_product
> > > product_id
> > > product_number
> > >
> > > the links:
> > >
> > > t_sn.link_id <-> t_link.link_id
> > > t_link.job_number_id <->
> > > t_job_number.job_number_id
> > > t_job_number.product_id <-> t_product.product_id
> > >
> > > i'm trying to create an array a multidimensional
> > > array of all serial numbers by product number.
> > > for example:
> > >
> > > prod1
> > > sn1
> > > sn2
> > > sn3
> > >
> > > prod2
> > > sn4
> > > sn5
> > > sn6
> > >
> > > this allows my user to choose a product and then
> > > view a select box populated with only the
> > > associated serial numbers.
> > >
> > > i *think* i'm trying to find product_id,
> > > product_number, sn_id, serial_number where the
> > > t_sn.link_id = t_link.link_id AND
> > > t_link.job_number_id
> > > = t_job_number.job_number_id AND
> > > t_job_number.product_id = t_product.product_id.
> > >
> > > i receive the following error:
> > >
> > > ERROR: missing FROM-clause entry for table
> > > "t_link"
> > >
> > > the last time i saw this error, a few people
> > > pointed me to the solution of using a subquery.
> > >
> > > excluding this error, i'm thinking i'm wrong b/c i
> > > don't see how the above "logic" can produce
> > > multiple serial number results.
> > >
> > > i'm not grasping what my thought process should be
> > > when i view this situation.
> > >
> > > any help is greatly appreciated.
> >
> > I don't have the experience to help you with the
> > array portion of your question
> > but perhaps this query will help.
> >
> > SELECT t_product.product_number,
> > t_sn.serial_number
> > FROM t_sn
> > LEFT JOIN t_link
> > ON ( t_sn.link_id =
> > t_link.link_id
> > )
> > LEFT JOIN t_job_number
> > ON ( t_link.job_number_id =
> > t_job_number.job_number_id
> > )
> > LEFT JOIN t_product
> > ON ( t_job_number.product_id =
> > t_product.product_id
> > )
> > ORDER BY product_number,
> > serial_number;
> >
> > Add a WHERE BY clause if you only want data for one
> > product_number.
> >
> > HTH
> >
> > Kind Regards,
> > Keith
>
> Keith, thanks. I have the array deal down pat, i just
> need the ids and the name in the format id1, name1,
> id2, name2. my php script handles the resulting
> recordset and gets the data ready for my forms class
> to manipulate it into linked selects.
>
> back to the code... let me see if i understand...
>
> SELECT t_product.product_number,
> t_sn.serial_number
>
> we'll go with your example since i can easily add in
> the ids to fit my situation.
>
> FROM t_sn
> LEFT JOIN t_link
> ON ( t_sn.link_id = t_link.link_id )
>
> this pulls all the sns in the serial number table and
> its respective link_id (or nothing, if it doesn't
> exist), right?
>
> LEFT JOIN t_job_number
> ON ( t_link.job_number_id =
> t_job_number.job_number_id )
>
> this pulls all the sns in the serial number table and
> its respective job_number_id (or nothing, if it
> doesn't exist), right?
>
> LEFT JOIN t_product
> ON ( t_job_number.product_id =
> t_product.product_id )
>
> this pulls all the sns in the serial number table and
> its respective product_id (or nothing, if it doesn't
> exist), right?
>
> ORDER BY product_number,
> serial_number;
>
> at this point, i have a "table" that lists every sn in
> the sn table, along with its respective (if they
> exist, otherwise NULL) link_id, job_number_id and
> product_id, right?
>
> something like:
>
> sn, lk, jn, pn (ids)
> 1, 1, 1, 1
> 2, 2, 2, 1
> 3, 3, 2, 2
> etc...
>
> so now all i need to do is to sort through this
> "table" with something like:
>
> in order to get the data i need, i will want to sort
> through the table in order to grab all sns with a pn =
> 1 (if i wanted the p/n represented by a pn_id of 1).
>
> this must be when the WHERE BY clause comes into play.
>
> i googled "where by" and didn't come up with anything
> of substance. can you give me an example how i should
> implement it here? is it:
>
> WHERE BY t_product.product_id =
> t_job_number.product_id
>
> tia...
Oops. that WHERE BY happened when my fingers got ahead of my brain. It is
simply a WHERE clause. Given the following corrected query.
SELECT t_product.product_number,
t_sn.serial_number
FROM t_sn
LEFT JOIN t_link
ON ( t_sn.link_id =
t_link.link_id
)
LEFT JOIN t_job_number
ON ( t_link.job_number_id =
t_job_number.job_number_id
)
LEFT JOIN t_product
ON ( t_job_number.product_id =
t_product.product_id
)
WHERE t_product.product_number = 1
ORDER BY product_number,
serial_number;
I would expect a result similar to this.
product_number | serial_number
---------------+--------------
1 | 1
1 | 2
1 | 3
The data that is used to connect the tables is not returned because it was not
requested. If there is a serial_number without a link_id it will not be
returned. Similarly if there is a link_id without a job_number_id it and its
serial_numbers if any will not be in the result. The use of foreign keys in
your tables could prevent that IF it is appropriate. If indeed it is possible
to have serial numbers without links without jobs... then there is no way to
connect them to the product table without some other data.
Kind Regards,
Keith
<Keith>
Oops. that WHERE BY happened when my fingers got ahead
of my brain. It
is simply a WHERE clause. Given the following
corrected query.
SELECT t_product.product_number,
t_sn.serial_number
FROM t_sn
LEFT JOIN t_link
ON ( t_sn.link_id =
t_link.link_id
)
LEFT JOIN t_job_number
ON ( t_link.job_number_id =
t_job_number.job_number_id
)
LEFT JOIN t_product
ON ( t_job_number.product_id =
t_product.product_id
)
WHERE t_product.product_number = 1
ORDER BY product_number,
serial_number;
I would expect a result similar to this.
product_number | serial_number
---------------+--------------
1 | 1
1 | 2
1 | 3
The data that is used to connect the tables is not
returned because it
was not
requested. If there is a serial_number without a
link_id it will not
be
returned. Similarly if there is a link_id without a
job_number_id it
and its
serial_numbers if any will not be in the result. The
use of foreign
keys in
your tables could prevent that IF it is appropriate.
If indeed it is
possible
to have serial numbers without links without jobs...
then there is no
way to
connect them to the product table without some other
data.
Kind Regards,
Keith
</keith>
Keith, i want to make sure we are on the same page. i
think the output from your query should be as follows:
product_number | serial_number
---------------+--------------
1 | 1
1 | 2
serial number 3 is linked to product 2 in the original
"table":
> sn, lk, jn, pn (ids)
> 1, 1, 1, 1
> 2, 2, 2, 1
> 3, 3, 2, 2 (<--- notice "2" here)
> etc...
also, i thought a left join included everything on the
left - even if there was no match on the right. i
guess this is known as a "left outer join" (i just did
some research). does an inner join only return values
with matches?
does postgresql's left join refer to a left inner join
by default?
tia...
__________________________________________
Yahoo! DSL Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com
operationsengineer1@yahoo.com wrote: > <Keith> > > Oops. that WHERE BY happened when my fingers got ahead > of my brain. It > is simply a WHERE clause. Given the following > corrected query. > > SELECT t_product.product_number, > t_sn.serial_number > FROM t_sn > LEFT JOIN t_link > ON ( t_sn.link_id = > t_link.link_id > ) > LEFT JOIN t_job_number > ON ( t_link.job_number_id = > t_job_number.job_number_id > ) > LEFT JOIN t_product > ON ( t_job_number.product_id = > t_product.product_id > ) > WHERE t_product.product_number = 1 > ORDER BY product_number, > serial_number; > > I would expect a result similar to this. > > product_number | serial_number > ---------------+-------------- > 1 | 1 > 1 | 2 > 1 | 3 > > The data that is used to connect the tables is not > returned because it > was not > requested. If there is a serial_number without a > link_id it will not > be > returned. Similarly if there is a link_id without a > job_number_id it > and its > serial_numbers if any will not be in the result. The > use of foreign > keys in > your tables could prevent that IF it is appropriate. > If indeed it is > possible > to have serial numbers without links without jobs... > then there is no > way to > connect them to the product table without some other > data. > > Kind Regards, > Keith > > </keith> > > Keith, i want to make sure we are on the same page. i > think the output from your query should be as follows: > > product_number | serial_number > ---------------+-------------- > 1 | 1 > 1 | 2 > > serial number 3 is linked to product 2 in the original > "table": > > >>sn, lk, jn, pn (ids) >>1, 1, 1, 1 >>2, 2, 2, 1 >>3, 3, 2, 2 (<--- notice "2" here) >>etc... > > > also, i thought a left join included everything on the > left - even if there was no match on the right. i > guess this is known as a "left outer join" (i just did > some research). does an inner join only return values > with matches? > > does postgresql's left join refer to a left inner join > by default? > > tia... You are right about the output. Serial number 3 would not be included if it is paired only with part number 2. Again you are correct. A LEFT JOIN implies an OUTER JOIN. From the manual "The words INNER and OUTER are optional in all forms. INNER is the default; LEFT, RIGHT, and FULL imply an outer join." Yes. An INNER JOIN only returns records that match the join condition. When you add the left and right you either get the left table with nulls on the right or the right table with nulls on the left respectively. The manual here http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html explains it better than I can. So in your situation if you use the LEFT JOIN you could end up with serial numbers that did not have a part number. Obviously they would not be selectable by part number unless you matched on part number IS NULL. In a real world model that doesn't make sense so it would probably suit your needs better to use just plain joins or for readability explicitly state INNER JOIN. Of course you have to be the final judge on that. ;-) If it were me I would make sure that there were some foreign key assignments preventing a serial_number without a link_id and a link_id without a job_number_id etc. etc. HTH -- Kind Regards, Keith
A book that has really helped me understand SQL better is 'The Practical SQL Handbook' by Bowman, Emerson, and Darnovsky. You may want to check it out. HTH, Kevin -----Original Message----- From: pgsql-novice-owner@postgresql.org [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of Keith Worthington Sent: Tuesday, December 27, 2005 8:22 PM To: operationsengineer1@yahoo.com Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Complex Query Help- For Me, Anyway operationsengineer1@yahoo.com wrote: > <Keith> > > Oops. that WHERE BY happened when my fingers got ahead > of my brain. It > is simply a WHERE clause. Given the following > corrected query. > > SELECT t_product.product_number, > t_sn.serial_number > FROM t_sn > LEFT JOIN t_link > ON ( t_sn.link_id = > t_link.link_id > ) > LEFT JOIN t_job_number > ON ( t_link.job_number_id = > t_job_number.job_number_id > ) > LEFT JOIN t_product > ON ( t_job_number.product_id = > t_product.product_id > ) > WHERE t_product.product_number = 1 > ORDER BY product_number, > serial_number; > > I would expect a result similar to this. > > product_number | serial_number > ---------------+-------------- > 1 | 1 > 1 | 2 > 1 | 3 > > The data that is used to connect the tables is not > returned because it > was not > requested. If there is a serial_number without a > link_id it will not > be > returned. Similarly if there is a link_id without a > job_number_id it > and its > serial_numbers if any will not be in the result. The > use of foreign > keys in > your tables could prevent that IF it is appropriate. > If indeed it is > possible > to have serial numbers without links without jobs... > then there is no > way to > connect them to the product table without some other > data. > > Kind Regards, > Keith > > </keith> > > Keith, i want to make sure we are on the same page. i > think the output from your query should be as follows: > > product_number | serial_number > ---------------+-------------- > 1 | 1 > 1 | 2 > > serial number 3 is linked to product 2 in the original > "table": > > >>sn, lk, jn, pn (ids) >>1, 1, 1, 1 >>2, 2, 2, 1 >>3, 3, 2, 2 (<--- notice "2" here) >>etc... > > > also, i thought a left join included everything on the > left - even if there was no match on the right. i > guess this is known as a "left outer join" (i just did > some research). does an inner join only return values > with matches? > > does postgresql's left join refer to a left inner join > by default? > > tia... You are right about the output. Serial number 3 would not be included if it is paired only with part number 2. Again you are correct. A LEFT JOIN implies an OUTER JOIN. From the manual "The words INNER and OUTER are optional in all forms. INNER is the default; LEFT, RIGHT, and FULL imply an outer join." Yes. An INNER JOIN only returns records that match the join condition. When you add the left and right you either get the left table with nulls on the right or the right table with nulls on the left respectively. The manual here http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.htm l explains it better than I can. So in your situation if you use the LEFT JOIN you could end up with serial numbers that did not have a part number. Obviously they would not be selectable by part number unless you matched on part number IS NULL. In a real world model that doesn't make sense so it would probably suit your needs better to use just plain joins or for readability explicitly state INNER JOIN. Of course you have to be the final judge on that. ;-) If it were me I would make sure that there were some foreign key assignments preventing a serial_number without a link_id and a link_id without a job_number_id etc. etc. HTH -- Kind Regards, Keith ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org