Re: Complex Query Help- For Me, Anyway

--- 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...

__________________________________________
Yahoo! DSL – Something to write home about.
Just $16.99/mo. or less.
dsl.yahoo.com