Bash script array from MySQL query - HELP Please!!!
I'm having problems getting a monitoring script to run.
I've put the troublesome bit in a separate script just to test, and it goes
like this:
declare -a HNdeclares the array HN
HN=(`echo SELECT url FROM hosts | mysql --user=netmon --password=n3tm0n
--skip-column-names check_http`) runs the query and assigns each record to a
new element in the array
echo ${#HN} echo's number of elements in array
for ((i=0;i${#HN};i++)); do
echo ${HN[${i}]} echo value of each element.
done
Seems simple enough yeah?! Well if I run echo SELECT url FROM hosts |
mysql --user=user --password=pass --skip-column-names check_http at the
command line, i get all of the records - 32. If I run the script above, it
simply refuses to put more than 14 elements in the array.
Then, to confuse it even more, if I sort the query, it gives a different
amount depending on what its sorted by!! For example, if I sort it by 'url'
it seems to generate 569 elements!
Can anyone please spot the undoubtedly obvious error I've made here?! I've
been scratching my head for days, to no avail!
Many thanks in advance,
Ben Benson
Re: Bash script array from MySQL query - HELP Please!!!
I would look at the 15th URL to see if there are specials in there that are
breaking the hash somehow.
On 5/22/07, Ben Benson [EMAIL PROTECTED] wrote:
I'm having problems getting a monitoring script to run.
I've put the troublesome bit in a separate script just to test, and it
goes
like this:
declare -a HNdeclares the array HN
HN=(`echo SELECT url FROM hosts | mysql --user=netmon --password=n3tm0n
--skip-column-names check_http`) runs the query and assigns each record to
a
new element in the array
echo ${#HN} echo's number of elements in array
for ((i=0;i${#HN};i++)); do
echo ${HN[${i}]} echo value of each element.
done
Seems simple enough yeah?! Well if I run echo SELECT url FROM hosts |
mysql --user=user --password=pass --skip-column-names check_http at the
command line, i get all of the records - 32. If I run the script above, it
simply refuses to put more than 14 elements in the array.
Then, to confuse it even more, if I sort the query, it gives a different
amount depending on what its sorted by!! For example, if I sort it by
'url'
it seems to generate 569 elements!
Can anyone please spot the undoubtedly obvious error I've made here?! I've
been scratching my head for days, to no avail!
Many thanks in advance,
Ben Benson
--
We are all slave to our own paradigm. -- Joshua Williams
If the letters PhD appear after a person's name, that person will remain
outdoors even after it's started raining. -- Jeff Kay
MySQL Query Help Needed
Can anybody help me sort my results in the correct way. My Table description is: +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | link_id | int(11) | | MUL | 0 || | user_id | int(11) | | MUL | 0 || | keywords | varchar(255) | YES | | NULL|| | bid | decimal(3,2) | | MUL | 0.01|| | bid_time | timestamp(14) | YES | | NULL|| +--+---+--+-+-++ I want to do a query that will return the most relevant keywords and they highest bid amount my query looks like: select * from ppc_keywords where keywords like %job%; The results I get is something like: ++-+-+--+--++ | id | link_id | user_id | keywords | bid | bid_time | ++-+-+--+--++ | 20 | 4 | 3 | job | 0.01 | 20030916130423 | | 23 | 4 | 3 | jobs | 0.01 | 20030916130537 | | 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 | | 41 | 2 | 3 | job | 0.05 | 20030916144655 | | 42 | 2 | 3 | ajob | 0.06 | 20030917124722 | | 43 | 2 | 3 | ajobs| 0.01 | 20030917120650 | | 46 | 1 | 5 | job | 0.01 | 20030917134229 | | 47 | 1 | 5 | jobs | 0.01 | 20030917134236 | | 48 | 1 | 5 | ajob | 0.01 | 20030917134240 | | 49 | 1 | 5 | ajobs| 0.01 | 20030917134245 | ++-+-+--+--++ As you can see this is not correct. I want job to be grouped together with it's highest bid amount. The following should be listed by relevance and bid. Can anyone help refine this search, please Johan Potgieter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Query Help Needed
Not sure I understand what you need? Can you create a dummy output table of what you would like your result to look like? At first glance, from reading your question, it sounds like you simply want this: select * from ppc_keywords where keywords like %job% [group by keywords] order by bid; where the group by keywords is kinda optional for this query, but it can't be that simple right? ;) On Wed, 2003-09-17 at 13:31, Johan Potgieter wrote: Can anybody help me sort my results in the correct way. My Table description is: +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | link_id | int(11) | | MUL | 0 || | user_id | int(11) | | MUL | 0 || | keywords | varchar(255) | YES | | NULL|| | bid | decimal(3,2) | | MUL | 0.01|| | bid_time | timestamp(14) | YES | | NULL|| +--+---+--+-+-++ I want to do a query that will return the most relevant keywords and they highest bid amount my query looks like: select * from ppc_keywords where keywords like %job%; The results I get is something like: ++-+-+--+--++ | id | link_id | user_id | keywords | bid | bid_time | ++-+-+--+--++ | 20 | 4 | 3 | job | 0.01 | 20030916130423 | | 23 | 4 | 3 | jobs | 0.01 | 20030916130537 | | 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 | | 41 | 2 | 3 | job | 0.05 | 20030916144655 | | 42 | 2 | 3 | ajob | 0.06 | 20030917124722 | | 43 | 2 | 3 | ajobs| 0.01 | 20030917120650 | | 46 | 1 | 5 | job | 0.01 | 20030917134229 | | 47 | 1 | 5 | jobs | 0.01 | 20030917134236 | | 48 | 1 | 5 | ajob | 0.01 | 20030917134240 | | 49 | 1 | 5 | ajobs| 0.01 | 20030917134245 | ++-+-+--+--++ As you can see this is not correct. I want job to be grouped together with it's highest bid amount. The following should be listed by relevance and bid. Can anyone help refine this search, please Johan Potgieter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Query Help Needed
I think the most difficult ( if not impossible with just using mysql query) is the part where you apply your logic as to the most relevant keyword. You need to be able to have a mechanism of telling the query which keyword is the most relevant, ie, human logic says, the one that matches the search string exactly, the next most relevant is the search string that has the least deviation from the keyword etc. I'm almost convinced this cannot be done with one or even multiple queries, and I would personally use something like PHP to apply that kind of logic on the result set. try the following: select distinct keywords, bid from ppc_keywords where keywords like %job% group by keywords order by bid; This should return the result grouped by the keyword ( in alphabetical order I think) and the order sorted by the highest bid, but as you can see, ajob and ajobs will be reported BEFORE job. But once you have this result, you can use PHP or PERL or the language of choice to sort it by relevance. Again, I stand to be corrected on whether mysql can return this kind of result on it's own, and I would certainly be very interested to see this done. With the advent of PHP5 with subselects and stored procedures, it might be more feasible? On Wed, 2003-09-17 at 14:51, Johan Potgieter wrote: It's not that simple as you say. I want the most relevant keywords at the top but it must be ordered by bid amount. The output I want must be: ++-+-+--+--++ | id | link_id | user_id | keywords | bid | bid_time | ++-+-+--+--++ | 41 | 2 | 3 | job | 0.05 | 20030916144655 | | 20 | 4 | 3 | job | 0.01 | 20030916130423 | | 46 | 1 | 5 | job | 0.01 | 20030917134229 | | 42 | 2 | 3 | ajob | 0.06 | 20030917124722 | | 48 | 1 | 5 | ajob | 0.01 | 20030917134240 | | 43 | 2 | 3 | ajobs| 0.01 | 20030917120650 | | 49 | 1 | 5 | ajobs| 0.01 | 20030917134245 | | 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 | | 23 | 4 | 3 | jobs | 0.01 | 20030916130537 | | 47 | 1 | 5 | jobs | 0.01 | 20030917134236 | ++-+-+--+--++ I hope it makes more sense. The idea is to return keywords that are exactly what the user supplied but also keywords that closely resemble what was entered. All those results must be grouped by keyword relevance and second by bid amount. Johan At 02:25 PM 2003/09/17 +0200, you wrote: Not sure I understand what you need? Can you create a dummy output table of what you would like your result to look like? At first glance, from reading your question, it sounds like you simply want this: select * from ppc_keywords where keywords like %job% [group by keywords] order by bid; where the group by keywords is kinda optional for this query, but it can't be that simple right? ;) On Wed, 2003-09-17 at 13:31, Johan Potgieter wrote: Can anybody help me sort my results in the correct way. My Table description is: +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | link_id | int(11) | | MUL | 0 || | user_id | int(11) | | MUL | 0 || | keywords | varchar(255) | YES | | NULL|| | bid | decimal(3,2) | | MUL | 0.01|| | bid_time | timestamp(14) | YES | | NULL|| +--+---+--+-+-++ I want to do a query that will return the most relevant keywords and they highest bid amount my query looks like: select * from ppc_keywords where keywords like %job%; The results I get is something like: ++-+-+--+--++ | id | link_id | user_id | keywords | bid | bid_time | ++-+-+--+--++ | 20 | 4 | 3 | job | 0.01 | 20030916130423 | | 23 | 4 | 3 | jobs | 0.01 | 20030916130537 | | 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 | | 41 | 2 | 3 | job | 0.05 | 20030916144655 | | 42 | 2 | 3 | ajob | 0.06 | 20030917124722 | | 43 | 2 | 3 | ajobs| 0.01 | 20030917120650 | | 46 | 1 | 5 | job | 0.01 | 20030917134229 | | 47 | 1 | 5 | jobs | 0.01 | 20030917134236 | | 48 | 1 | 5 | ajob | 0.01 | 20030917134240 | | 49 | 1 | 5 | ajobs| 0.01 | 20030917134245 |
mysql query help
Hello, I have the following table structure and need assistance on a mysql query: Members MemberID (unique) Name Address Credits CreditID MemberID Project Title Credit_Images = ImageID CreditID Location Comments Showroom = ShowroomID MemberID Size Comments I need to do a full search on all the fields (except ID) and return a list of names from the members table. I am kind of stuck - any help will be appreciated. I am running on version 3.23 so full-text is not available Thanks. --Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mySQL query help please
Greetings: RE: mySQL query help please INSERT INTO Customer_Equipment SELECT Customer.ID, Server_ID, Configuration, Equipment_Type, Group_ID, Location, Rack_Location, Network_Status_Name, Creator_ID, Primary_IP_Address FROM Customer_Equipment, Customer WHERE SUBSTRING(Server_ID, 5,4) = Customer.User_ID; The select statement works perfectly, but when I try to combine the select statement with an INSERT INTO statement (I've done this in the past), I get the error message: ERROR 1066: Not unique table/alias: 'Customer_Equipment' What does this error message mean, and how should I accomplish my insert into statement so I can use the results of the select statement above? Thank you. Peter M. Perchansky, President/CEO Dynamic Net, Inc. Helping companies do business on the Net 420 Park Road; Suite 201 Wyomissing PA 19610 Non-Toll Free: 1-610-736-3795 Personal Email: [EMAIL PROTECTED] Company Email: [EMAIL PROTECTED] Web:http://www.dynamicnet.net/ http://www.manageddedicatedservers.com/ http://www.wemanageservers.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mySQL query help please
[snip] INSERT INTO Customer_Equipment SELECT Customer.ID, Server_ID, Configuration, Equipment_Type, Group_ID, Location, Rack_Location, Network_Status_Name, Creator_ID, Primary_IP_Address FROM Customer_Equipment, Customer WHERE SUBSTRING(Server_ID, 5,4) = Customer.User_ID; The select statement works perfectly, but when I try to combine the select statement with an INSERT INTO statement (I've done this in the past), I get the error message: ERROR 1066: Not unique table/alias: 'Customer_Equipment' [/snip] You cannot INSERT into a table that you have SELECT 'ed from; INSERT INTO Customer_Equipment SELECT ... FROM Customer_Equipment, Customer HTH! Jay sql, mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mySQL query help please
Greetings Jay: RE: mySQL query help please I love rules that don't make sense ;-) So if the end result is to have the records selected appended to the Customer_Equipment table, I have to make a temporary table based on the characteristics of the Customer_Equipment table... then insert from that temporary table. Correct? Thank you. At 12:29 PM 6/24/2002 -0500, you wrote: [snip] INSERT INTO Customer_Equipment SELECT Customer.ID, Server_ID, Configuration, Equipment_Type, Group_ID, Location, Rack_Location, Network_Status_Name, Creator_ID, Primary_IP_Address FROM Customer_Equipment, Customer WHERE SUBSTRING(Server_ID, 5,4) = Customer.User_ID; The select statement works perfectly, but when I try to combine the select statement with an INSERT INTO statement (I've done this in the past), I get the error message: ERROR 1066: Not unique table/alias: 'Customer_Equipment' [/snip] You cannot INSERT into a table that you have SELECT 'ed from; INSERT INTO Customer_Equipment SELECT ... FROM Customer_Equipment, Customer Peter M. Perchansky, President/CEO Dynamic Net, Inc. Helping companies do business on the Net 420 Park Road; Suite 201 Wyomissing PA 19610 Non-Toll Free: 1-610-736-3795 Personal Email: [EMAIL PROTECTED] Company Email: [EMAIL PROTECTED] Web:http://www.dynamicnet.net/ http://www.manageddedicatedservers.com/ http://www.wemanageservers.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mySQL query help please
[snip] So if the end result is to have the records selected appended to the Customer_Equipment table, I have to make a temporary table based on the characteristics of the Customer_Equipment table... then insert from that temporary table. Correct? [/snip] BINGO! Goofy rules can be frustrating :) Jay sql, mysql, query (goofy spam rule) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql query help needed!!
Hi Chris, On Fri, 2002-06-14 at 02:08, Chris Kay wrote: I have a rather longer query which I would like to get all records past todays date. Here is my query $ttwo = date(YmdGi); snip 'detail_start_date_y,detail_start_date_m,detail_start_date_d,detail_start_time_h,detail_start_time_m' = '$ttwo' order by detail.detail_start_date_m DESC, detail.detail_start_date_d DESC; If you quote the column names, then MySQL will interprete them as a string. Also, in this query, you would need to explicitly state the table that the columns are from. Also, you will be able to simplify your query by using a datetime field, instead of separately storing the year, month, day, hour and minute. This would reduce the last part of your query down to something like: detail.start_date = NOW() ORDER BY MONTH(detail.start_date) DESC, DAY(detail.start_date) DESC; ... -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Zak Greant [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Advocate /_/ /_/\_, /___/\___\_\___/ Calgary, Canada ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql query help needed!!
I have a rather longer query which I would like to get all records past todays date. Here is my query $ttwo = date(YmdGi); $abc = select detail.*, type.type_name, status.status_name, staff.staff_name, source.source_long, source.source_short from detail, type, status, staff, source where type.type_id = detail.detail_type status.status_id = detail.detail_status staff.staff_id = detail.detail_staff source.source_short = detail.detail_source 'detail_start_date_y,detail_start_date_m,detail_start_date_d,detail_start_time_h,detail_start_time_m' = '$ttwo' order by detail.detail_start_date_m DESC, detail.detail_start_date_d DESC; $dbq = select($abc); The query works fine before I try to get all records in the furure as shown below 'detail_start_date_y,detail_start_date_m,detail_start_date_d,detail_start_time_h,detail_start_time_m' = '$ttwo' The query does not error out it just does not give any records, and I know there are 4 records Detail_start_date_y = 4 digit year Detail_start_date_m = 2 digit month Deatil_start_date_d = 2 digit day Detail_start_time_h = 24 hour time Can anyone see what I am doing wrong? Thanks in advance. --- Chris Kay Technical Support - Techex Communications Website: www.techex.com.au Email: [EMAIL PROTECTED] Telephone: 1300 88 111 2 - Fax: (02) 9970 5788 Address: Suite 13, 5 Vuko Place, Warriewood, NSW 2102 Platinum Channel Partner of the Year - Request DSL - Broadband for Business --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql query help
I need some help... The query below runs fine in access, when running it in mysql it tells me the column wrainfo.wuc doesn't exist in the having clause.. can't figure out why its giving me this error. the column is in the select statement... any ideas? The column does exist.. I'm looking at the database right now. Query: SELECT wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, min(updates.updates) as udate, wrainfo.wuc as srawuc, wucpartsinfo.notea, wucpartsinfo.noteb FROM (((wucpartsinfo LEFT JOIN parts ON wucpartsinfo.parts_id =parts.parts_id) LEFT JOIN updates on wucpartsinfo.wucpn_id=updates.wucpart_id) RIGHT JOIN wucinfo on wucpartsinfo.wuc_id=wucinfo.wuc_id) RIGHT JOIN wrainfo on wucinfo.wuc5 = wrainfo.wuc5 where wrainfo.WUC5 = '46X1B' group by wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, wrainfo.wuc, wucpartsinfo.notea, wucpartsinfo.noteb having (wucinfo.wuc wrainfo.wuc) order by wucinfo.wuc, wucpartsinfo.seq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql query help
I've not looked over your query in-depth so I may be off-base here. Try removing the parentheses from the FROM clause and see if that makes it work. =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Scott Raley [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 7:23 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: mysql query help I need some help... The query below runs fine in access, when running it in mysql it tells me the column wrainfo.wuc doesn't exist in the having clause.. can't figure out why its giving me this error. the column is in the select statement... any ideas? The column does exist.. I'm looking at the database right now. Query: SELECT wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, min(updates.updates) as udate, wrainfo.wuc as srawuc, wucpartsinfo.notea, wucpartsinfo.noteb FROM (((wucpartsinfo LEFT JOIN parts ON wucpartsinfo.parts_id =parts.parts_id) LEFT JOIN updates on wucpartsinfo.wucpn_id=updates.wucpart_id) RIGHT JOIN wucinfo on wucpartsinfo.wuc_id=wucinfo.wuc_id) RIGHT JOIN wrainfo on wucinfo.wuc5 = wrainfo.wuc5 where wrainfo.WUC5 = '46X1B' group by wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, wrainfo.wuc, wucpartsinfo.notea, wucpartsinfo.noteb having (wucinfo.wuc wrainfo.wuc) order by wucinfo.wuc, wucpartsinfo.seq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql query help
Actually it was the alias, but the query is producing the wrong results. It should produce 3 results which I get in Access I cut and paste this query into mysql and get 12371 results. Same databases. Cal Evans [EMAIL PROTECTED] on 06/06/2002 08:50:51 AM Please respond to [EMAIL PROTECTED] To: Scott Raley/LPEC/ASD/SEMCORINC@SEMCOR INC., [EMAIL PROTECTED], [EMAIL PROTECTED] cc: Subject: RE: mysql query help I've not looked over your query in-depth so I may be off-base here. Try removing the parentheses from the FROM clause and see if that makes it work. =C= * * Cal Evans * Journeyman Programmer * Techno-Mage * http://www.calevans.com * -Original Message- From: Scott Raley [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 06, 2002 7:23 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: mysql query help I need some help... The query below runs fine in access, when running it in mysql it tells me the column wrainfo.wuc doesn't exist in the having clause.. can't figure out why its giving me this error. the column is in the select statement... any ideas? The column does exist.. I'm looking at the database right now. Query: SELECT wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, min(updates.updates) as udate, wrainfo.wuc as srawuc, wucpartsinfo.notea, wucpartsinfo.noteb FROM (((wucpartsinfo LEFT JOIN parts ON wucpartsinfo.parts_id =parts.parts_id) LEFT JOIN updates on wucpartsinfo.wucpn_id=updates.wucpart_id) RIGHT JOIN wucinfo on wucpartsinfo.wuc_id=wucinfo.wuc_id) RIGHT JOIN wrainfo on wucinfo.wuc5 = wrainfo.wuc5 where wrainfo.WUC5 = '46X1B' group by wrainfo.wuc5, wucinfo.wuc, wucinfo.nomen, wucpartsinfo.weapon, wucinfo.refdes, wucpartsinfo.seq, parts.part_no, wucpartsinfo.tms, wucpartsinfo.sbuno, wucpartsinfo.ebuno, wucpartsinfo.lots, parts.cage, wucpartsinfo.smr, wucpartsinfo.se1, wucpartsinfo.se2, parts.niin, wucpartsinfo.changes, wrainfo.wuc, wucpartsinfo.notea, wucpartsinfo.noteb having (wucinfo.wuc wrainfo.wuc) order by wucinfo.wuc, wucpartsinfo.seq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [PHP-DB] MYSQL query help
It's not the IN, it's the sub-query you cannot use. MySQL doesn't support them and it's just about the main reason I don't like it. Alternatives? You could execute your subquery and return the results to an array. Loop through the array, using the index and the indexed element to drive a series of queries, accumulating your results as you go. If the number of items return by the subquery is not large, you could build an in set, for lack of a better term, so you would end up with ... and IN (first, second, ...nth) . (Check syntax!!) But that's likely to be slower than cold molasses and run like a pig. If you're not too far into the project, and you will have a lot of subqueries, switch to PostgreSQL or a database that supports them. I don't really have an answer. I hope someone comes up with a more elegant solution. Regards - Miles Thompson At 09:34 AM 12/14/2001 -0500, Harpreet wrote: I dont think we can use 'IN' and 'NOT IN' in mysql. Is there an alternative that would work? select * from lib_asset_tbl where material_id '' and asset_id in (select asset_id from lib_copy_tbl) Help is appreciated. Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] To contact the list administrators, e-mail: [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MYSQL query help
I dont think we can use 'IN' and 'NOT IN' in mysql. Is there an alternative that would work? select * from lib_asset_tbl where material_id '' and asset_id in (select asset_id from lib_copy_tbl) Help is appreciated. Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MYSQL query help
Harpreet writes: I dont think we can use 'IN' and 'NOT IN' in mysql. Is there an alternative that would work? select * from lib_asset_tbl where material_id '' and asset_id in (select asset_id from lib_copy_tbl) Help is appreciated. Regards, Harpreet Kaur Software Developer Crispin Corporations Inc. The above query is easily resolved by a join: select lib_assed_tbl.* from lib_asset_tbl, lib_copy_tbl where material_id '' and lib_asset_tbl.asset_id = lib_copy_tbl.asset_id -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
