Re: Complex Query Help- For Me, Anyway - Mailing list pgsql-novice
From | |
---|---|
Subject | Re: Complex Query Help- For Me, Anyway |
Date | |
Msg-id | 20051227214627.90069.qmail@web33310.mail.mud.yahoo.com Whole thread Raw |
In response to | Re: Complex Query Help- For Me, Anyway ("Keith Worthington" <keithw@narrowpathinc.com>) |
Responses |
Re: Complex Query Help- For Me, Anyway
|
List | pgsql-novice |
--- 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
pgsql-novice by date: