Re: Complex Query Help- For Me, Anyway

On Tue, 27 Dec 2005 13:46:27 -0800 (PST), operationsengineer1 wrote
> --- Keith Worthington <keithw(at)narrowpathinc(dot)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