How to combine text with 'IN' and a subquery?


Quite hard coming up with a title for this post This is formatted with some PHP but I thought this forum was probably more appropriate.

Basically, I have the following query:

Code: SELECT objName,objID,objImg FROM wwObjects WHERE objLocation = 'loc_" . $_POST['loc'] . "' OR objLocation IN (SELECT locID from wwLocations WHERE locParent = '" . $_POST['loc'] . "') ORDER BY objName

Now, my problem is that objLocation is stored with a prefix (in this case "loc_", whereas locParent is just the number which follows the prefix.

This is fine with the '=' because I can just insert the string before the number into the query, however I can't for the life of me work out how to do the same thing with the 'IN' and subquery.

Posted On: Friday 12th of October 2012 12:22:54 AM Total Views:  155
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Full text search over two tables (with fields that have identical names)

Hi Guys, I'm currently coding a website search script for my website. The data I want to search is stored in site_content.title, site_title.text, site_content_data.text. Does full text searching support searching across multiple tables I've tried the following with no joy: Code: $query1 = @mysql_query(sprintf("SELECT T.name, T.title, T.text, D.text, T.main_cat, T.sub_cat, T.page FROM site_content AS T, site_content_data AS D WHERE MATCH(T.title, T.text, D.text) AGAINST('%s' IN BOOLEAN MODE) AND T.active = '2' LIMIT 50", mysql_real_escape_string($searchtext))) or die(mysql_error());
VIEWS ON THIS POST

67

Posted on:

Friday 12th October 2012
View Replies!

Full text search won't search multiple columns??

Any ideas what I am doing wrong I want to search the full text indexes on "content_title" and "content_text" and return all results. My sql is: PHP Code: SELECT`content`.`content_title`,`content`.`content_text`,`templates`.`template_name`,`content`.`content_id`,`content`.`content_section`,`content`.`content_frontpage`,`content`.`content_created_to_display`FROM`content`InnerJoin`templates`ON`content`.`content_template`=`templates`.`template_id`WHEREMATCH(content_title,content_text)AGAINST('test')AND`content`.`content_active`='1'ORDERBY`content`.`content_created_to_display`DESC and my two index is: Index Name (index fields) content_search (content_title, content_text) It only returns results where there is a match on the content_text field
VIEWS ON THIS POST

57

Posted on:

Friday 12th October 2012
View Replies!

UPDATE combined with SELECT gives headaches

Hi all, I am trying to combine an UPDATE with a SELECT statement to change multiple rows. The query works without errors, but whatever I try, zero rows are affected (and that's not what it should do, or better, what i would like it to do). I've searched everywhere on the Internet to find a solution but to no avail, so that's why I'm trying here. The SELECT statements all work fine on the table when I run them seperately, with the deed results, but combined in the UPDATE, I get no results (no affected rows). Here's the query: UPDATE forum_article SET childs = (SELECT COUNT(`response_to_forum_article_id`) FROM (SELECT * FROM `forum_article`) AS X WHERE `response_to_forum_article_id` '0' GROUP BY `response_to_forum_article_id`) WHERE forum_article_id = (SELECT `response_to_forum_article_id` FROM (SELECT * FROM `forum_article`) AS Y WHERE `response_to_forum_article_id` '0' GROUP BY `response_to_forum_article_id`) Hope somebody has a clue. The subqueries are there because I learned that MySQL needs aliased subqueries when the UPDATE and SELECT statements are affecting the same table. Also, to explain a little bit more, the 'childs' is the column that needs to be populated.
VIEWS ON THIS POST

98

Posted on:

Friday 12th October 2012
View Replies!

Possible to combine these 3 SELECT's into one query?

I have 3 SELECT queries and I am trying to combine them into 1. 3 tables: 1. 'article' table (id, title, ...) -- holds title, subtitle etc. 2. 'subtypes' table (id, subtype) -- holds the subtype of each article (e.g if article type is "news", the subtype might be "local".) 3. 'keyword' table (id, keyword) -- holds any number of keywords associated with each article. [Note in my particular case only one type of article has a subtype, so that is why I had to split it off into it's own table.] The 3 queries I have for pulling an article (with for example, id = 266), it's subtype (if any) and it's kewords from the database are: Code: select * from articles where id = 266 select subtype from subtypes where id = ( select subtype_id from articles2subtypes where article_id = 266 ) select * from keywords where id in ( select keyword_id from articles2keywords where article_id = 266 ) 'articles2subtypes' table matches articles with their subtype (if any), and 'articles2keywords' table matches articles up with their associated keywords. I would really like to combine those 3 queries into 1 if possible. Is that possible, and what would the query be THANKS!
VIEWS ON THIS POST

96

Posted on:

Friday 12th October 2012
View Replies!

Can this query with two tables be combined?

Right now I have two different webpages that give me the data I need. But I was wondering if I could combine them onto one report. Here are the two queries I use: $dailySearches = "SELECT DATE_FORMAT(date, '%Y-%m-%d') as Date, COUNT(*) as Searches FROM searches GROUP BY DATE_FORMAT(date, '%Y-%M-%D') order by Date desc limit 120;"; $dailyLogins = "SELECT DATE_FORMAT(last, '%Y-%m-%d') as Date, COUNT(*) as Logins FROM users GROUP BY DATE_FORMAT(last, '%Y-%M-%D') order by Date desc limit 120;";
VIEWS ON THIS POST

111

Posted on:

Sunday 21st October 2012
View Replies!

Speeding up queries with combined indexes

Does it speed up queries to use a combined index on the fields you're searching one, rather than indexes on each field eg if we were to query with the condition: Code: SELECT * FROM my_table WHERE col1 < 100 AND col2>300 If we have an index on col1 and and index on col2, the optimiser would only use one of the indexes, would it not however if we added Code: alter my_table add index combined_index(col1,col2) The optimiser seems to prefer this joined index. I haven't run any benchmarks, but is this the way to go Utilising a combined index for a combination of fields you are likely to use in your condition Maybe there's a better term for it that I could look up.
VIEWS ON THIS POST

99

Posted on:

Sunday 21st October 2012
View Replies!

Can these queries be combined?

I am retrieving a set of rows and then running a loop for each of the rows. Inside the loop there is a query to find out the total number of related rows. Is it possible to do it all in one shot instead of running separate queries Code PHP: $questions=mysql_query("SELECT id, key, content FROM table WHERE type='question'"); while($row=mysql_fetch_array($questions)){ $answers=mysql_query("SELECT COUNT(id) FROM table WHERE key='{$row['key']}' AND type='answer'"); }
VIEWS ON THIS POST

108

Posted on:

Sunday 21st October 2012
View Replies!

Anyway to combine these together?

Hi , Is there anyway of combining these to pieces of code together So the query can count the messages that are stored for that user Code: // count how many new messages they are for that user $new_count = mysql_query(" SELECT COUNT(new) as NumNew FROM gallery_messages WHERE userId='".$got_id[0]."' AND new='1' "); $new_counted = mysql_fetch_row( $new_count ); mysql_free_result( $new_count ); // count how many messages have been replied and stored for that user $rep_count = mysql_query(" SELECT COUNT(replied) as NumReplied FROM gallery_messages WHERE userId='".$got_id[0]."' AND replied='1' "); $rep_counted = mysql_fetch_row( $rep_count ); mysql_free_result( $rep_count ); Thank you for your help and time, spence
VIEWS ON THIS POST

99

Posted on:

Monday 22nd October 2012
View Replies!

How do I combine tables in MYSQL so that 2 different sites share the same username an

I have a site using WebNetwork by WebScribble and phpbb.... They share the same database. My goal is to log into one WebNetwork and not have to log in again when bridging to phpbb... phpBB is the application I use to run the web forum on my WebNetwork site. I need to sync username and password functions only... as I don't want administrators and moderators of the forum to have that power on the webnetwork site.... More info... table for WebNetwork is "sn_user" table for phpBB is "phpBB_user" Please help! - Ken
VIEWS ON THIS POST

108

Posted on:

Wednesday 24th October 2012
View Replies!

Muliple databases or one combined?

I have multiple database-driven webapps that I'd like to install on a single server (bugDB, wiki, etc). From a performance standpoint, what makes more sense: 1. Create a database for each of the webapps 2. Create a single database for all of the webapps and differentiate the tables by appending a string at the beginning.
VIEWS ON THIS POST

102

Posted on:

Wednesday 24th October 2012
View Replies!

Please help me combine these queries

Original Query: SELECT * FROM `tx_categories_build` WHERE `galleries` >= 20 ORDER BY `name` But I also need to do this as well.. but in ONE query. SELECT tag FROM `tx_categories` WHERE name LIKE 'the name column' SELECT COUNT(*) as MOVIES FROM `tx_galleries` WHERE format='movies' AND categories LIKE '%tag%' SELECT COUNT(*) as PICS FROM `tx_galleries` WHERE format='pictures' AND categories LIKE '%tag%'
VIEWS ON THIS POST

88

Posted on:

Wednesday 24th October 2012
View Replies!

How to combine these 2 sql statements

so i got 2 tables. checks id int gid int ... user id int status enum('active','suspended') ... so one sql that i want is select count(*),u.status from checks c left join user u on c.id=u.id where c.gid>0 group by u.status but then i also want one that just says select count(*) from user where user='active'; is there a way i can join those 2 into 1 single sql statement\t one thing to note is that not all id in user exist in check (check only has a subset of entries in user ) basically i need to know all the users that are active, and then all those active users that have entries in checks and gid > 0 , but then i also want to have the number of active users as well regardless if they have entries in checks or not. thanx
VIEWS ON THIS POST

130

Posted on:

Wednesday 24th October 2012
View Replies!

Possible to combine these three queries?

I'm not using mysql v4.1!!! Is it possible to combine these queries into one Code: SELECT max( id ) FROM table WHERE id > '$my_id' SELECT * FROM table WHERE id = '$my_id' SELECT min( id ) FROM table WHERE id > '$my_id' I am trying to use the query to develop a "previous" and "next" navigation system.
VIEWS ON THIS POST

84

Posted on:

Wednesday 24th October 2012
View Replies!

How to combine effective keyword search with structured data criteria

I'm curious what solutions people are employing to provide effective solutions for searching text and structured data in combination. For example, I have a collection of documents stored in a database and I need to make them searchable using both keywords and categories. The categories are stored using a typical, normalized database structure and are exposed to the user as checkboxes and select lists on the search form. In this case, for a document to match the search, it has to match the keyword phrase and also be associated with all of the categories selected by the user. The user can omit either the keyword phrase or categories and just search using one or the other. I have the category part handled. But how to get effective text search to go with that is trickier. The application is running on PHP 4 & MySQL 4.1. I'm currently using MySQL's full-text search features, but they leave quite a bit to be deed -- e.g. there's no stemming, it doesn't ignore punctuation, etc. I'm interested in finding out how other developers have effectively handled this kind of situation. I'm considering ditching, or at least supplementing, MySQL's full-text search in favor of some more effective keyword search solution. I've been taking a look at Google Site Search for example. I think that would probably provide a more effective text search solution than using MySQL full-text, but I don't know that there'd be a way to integrate that with the structured data that needs to be part of the criteria for searches in my app. Any suggestions
VIEWS ON THIS POST

317

Posted on:

Thursday 25th October 2012
View Replies!

combine all query results in one column

, I got a query similar to that: Code: SELECT c_01, c_02, c_03, c_04, c_05 FROM table WHERE c_01 != '' OR c_02 != '' OR c_03 != '' OR c_04 != '' OR c_05 and I get results similar to: Code: +---------+ ---------+ ---------+ ---------+ ---------+ | c_01 | c_02 | c_03 | c_04 | c_05 | +---------+ ---------+ ---------+ ---------+ ---------+ | 0 | 0 | 0 | 0 | data_06 | | data_01 | data_02 | 0 | data_04 | 0 | | 0 | 0 | data_03 | data_05 | 0 | | data_07 | 0 | 0 | 0 | 0 | +---------+ ---------+ ---------+ ---------+ ---------+ But the result I need is something like: Code: +---------+ | c_ALL | +---------+ | data_01 | | data_02 | | data_03 | | data_04 | | data_05 | | data_06 | | data_07 | +---------+ I just can't figure out how to combine several column in 1 column. I could loop through everything in PHP but that data is also sometimes redundant so I'd like use GROUP BY first before it gets to PHP. I have no control over the architecture of the DB...
VIEWS ON THIS POST

66

Posted on:

Thursday 25th October 2012
View Replies!

Complex Multiple table query combine

I'll will do my best to describe what I am trying to do so that can understand What I am trying to do is to combine 2 queries into 1 query for a edit record page. Both of these queries work to my knowledge. The first query pulls up information about a company Code: SELECT company.companyid , company.name , company.homepage , useraddress.suite , useraddress.number , useraddress.street , useraddress.city , useraddress.state , useraddress.zip , usercontact.phonenumber , usercontact.faxnumber , usercontact.emailaddress , comamens.pricepersqftmin , comamens.pricepersqftmax , comamens.onethousandbelow , comamens.onethousand , comamens.fifteenhundred , comamens.twothousand , comamens.twentyfivehundred , comamens.threethousand , comamens.single , comamens.singlewbasement , comamens.tri , comamens.triwbasement , comamens.two , comamens.twowbasement , comamens.singlewbonus FROM company, useraddress, usercontact, comamens WHERE company.companyid = 10420 AND useraddress.companyid = company.companyid AND usercontact.companyid = company.companyid AND comamens.companyid = company.companyid The second query pulls up information about contacts for that company Code: SELECT userstandard.fname , userstandard.lname , userstandard.jobtitle , usercontact.emailaddress , usercontact.phonenumber FROM userstandard INNER JOIN usercontact ON usercontact.userid = userstandard.username WHERE userstandard.companyid = 10420 I could leave these as 2 seperate queries but I am unsure if that is the correct way to handle this or not. My table structure is this Company Code: CREATE TABLE `company` ( `companyid` mediumint(9) NOT NULL auto_increment, `name` varchar(128) default NULL, `homepage` varchar(256) default NULL, `comments` text, `companyclass` varchar(32) default NULL, `showroom` tinyint(1) default NULL, `maps` tinyint(1) default NULL, `logo` tinyint(1) default NULL, PRIMARY KEY (`companyid`), UNIQUE KEY `companyid` (`companyid`) ) usercontact Code: CREATE TABLE `usercontact` ( `contactpk` int(11) NOT NULL auto_increment, `emailaddress` varchar(128) default NULL, `emailclass` varchar(64) default NULL, `phonenumber` varchar(64) default NULL, `phoneclass` varchar(64) default NULL, `cellnumber` varchar(64) default NULL, `cellclass` varchar(64) default NULL, `faxnumber` varchar(64) default NULL, `faxclass` varchar(64) default NULL, `userid` varchar(128) default NULL, `companyid` mediumint(9) default NULL, `cmlsnumber` int(11) default NULL, `schnumber` smallint(6) default NULL, PRIMARY KEY (`contactpk`), UNIQUE KEY `username` (`userid`,`companyid`,`cmlsnumber`), UNIQUE KEY `schnumber` (`schnumber`), KEY `FK_usercontact` (`companyid`), KEY `FK_usercontactcom` (`cmlsnumber`), CONSTRAINT `usercontact_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `userstandard` (`username`), CONSTRAINT `usercontact_ibfk_2` FOREIGN KEY (`companyid`) REFERENCES `company` (`companyid`), CONSTRAINT `usercontact_ibfk_3` FOREIGN KEY (`cmlsnumber`) REFERENCES `community` (`cmlsnumber`), CONSTRAINT `usercontact_ibfk_4` FOREIGN KEY (`schnumber`) REFERENCES `schools` (`schnumber`) ) userstandard Code: CREATE TABLE `userstandard` ( `stanpk` int(11) NOT NULL auto_increment, `fname` varchar(64) default NULL, `lname` varchar(64) default NULL, `username` varchar(128) default NULL, `passcode` varchar(128) default NULL, `companyid` mediumint(9) default NULL, `companychange` tinyint(1) default NULL, `jobtitle` varchar(64) default NULL, `apprmovedate` date default NULL, `privacyread` tinyint(1) default NULL, PRIMARY KEY (`stanpk`), UNIQUE KEY `username` (`username`,`companyid`), KEY `FK_userstandard` (`companyid`), CONSTRAINT `userstandard_ibfk_1` FOREIGN KEY (`companyid`) REFERENCES `company` (`companyid`) ) I tried doing an OUTER JOIN so that I could pull up contact info too by the username Code: SELECT company.companyid , company.name , company.homepage , useraddress.suite , useraddress.number , useraddress.street , useraddress.city , useraddress.state , useraddress.zip , usercontact.phonenumber , usercontact.faxnumber , usercontact.emailaddress , userstandard.fname , userstandard.lname , userstandard.jobtitle , comamens.pricepersqftmin , comamens.pricepersqftmax , comamens.onethousandbelow , comamens.onethousand , comamens.fifteenhundred , comamens.twothousand , comamens.twentyfivehundred , comamens.threethousand , comamens.single , comamens.singlewbasement , comamens.tri , comamens.triwbasement , comamens.two , comamens.twowbasement , comamens.singlewbonus FROM company, useraddress, usercontact, comamens LEFT OUTER JOIN usercontact ON usercontact.userid = userstandard.username WHERE company.companyid = 10420 AND userstandard.companyid = company.companyid AND useraddress.companyid = company.companyid AND usercontact.companyid = company.companyid AND usercontact.userid = userstandard.username AND comamens.companyid = company.companyid But I receive an error that usercontact is not a unique table. I then tried doing a UNION Code: SELECT company.companyid , company.name , company.homepage , useraddress.suite , useraddress.number , useraddress.street , useraddress.city , useraddress.state , useraddress.zip , usercontact.phonenumber , usercontact.faxnumber , usercontact.emailaddress , comamens.pricepersqftmin , comamens.pricepersqftmax , comamens.onethousandbelow , comamens.onethousand , comamens.fifteenhundred , comamens.twothousand , comamens.twentyfivehundred , comamens.threethousand , comamens.single , comamens.singlewbasement , comamens.tri , comamens.triwbasement , comamens.two , comamens.twowbasement , comamens.singlewbonus FROM company, useraddress, usercontact, comamens WHERE company.companyid = 10420 AND useraddress.companyid = company.companyid AND usercontact.companyid = company.companyid AND comamens.companyid = company.companyid UNION SELECT userstandard.fname , userstandard.lname , userstandard.jobtitle , usercontact.phonenumber , usercontact.faxnumber , usercontact.emailaddress FROM userstandard, usercontact, company WHERE userstandard.companyid = company.companyid AND usercontact.userid = userstandard.username But I get an error stating that the amount of columns do not equal each other. Now for the hard part of trying to explain this. I store company information into multiple tables, for this problem I store company information in usercontact useraddress company userstandard Those are linked together by the companyid. The information stored in the userstandard is more of Point of Contact inforation, linking users to a company, but not all users are linked to a company. I store user information into multiple tables, for this problem I store user information in usercontact useraddress userstandard As stated above a user CAN be linked to a company through the userstandard table using a companyid. A user can also be linked to contact information for that user using the username / userid. What I am trying to accomplish is to pull up all the users that are linked to the company and also pull up the user contact information at the same time. I hope this time I have explained what I am trying to accomplish and to give the info needed.
VIEWS ON THIS POST

188

Posted on:

Thursday 25th October 2012
View Replies!

Need to combine 2 queries

I need to combine 2 queries into 1 so that I can get a single listing of properties in our database, ordered by distance of the listing zip code from the search zip, then sub-ordered by date listed. I have 1 query that already works quite well. It takes the search zip, say 90210, then returns all of the nearest zipcodes in decending order of distance within a certain radius. I have another query that will then get the list of properties within a single zip code, ordered by most recent date listed first and descending. Finally, I iterate through nested loop, first the number of zipcodes, then the 2nd loop loops through the property listings for each zip. This displays the results. I have 2 tables. The 1st stores zip code, latitude, and longitude for each zip code. The 2nd table stores listings properties, which includes the zipcode of the property, the date listed, and an auto-increment ID. The IDs correspond to the date. The most recent listing added has the highest ID. My goal is to be able to move forward and back in the results, say to show 10 listings per pageview by using LIMIT. Currently that's not possible because the zip code rankings are separate from the listing search. This is the code I'm using, which is PHP, for the queries. Its the complete function. PHP Code: functionmatchby_zip($zip){ $radius=10; //getthelat&lonofstartingzipcode $query="SELECTzipcode,lat,lonFROMzipDataWHEREzipcode='$zip'"; $result=mysql_query($query)ordie('errorBoxDatabaseerror:'.mysql_error()); \t\t//getvaluesoutof$result \t\t$row=mysql_fetch_row($result); \t\t$lat=$row[1]; \t\t$lon=$row[2]; //Getzipcodesinrange,resultsareautomaticallyorderedbydistancefromrequestedzip $query="SELECTzipcode,lat,lonFROMzipDataWHERE(POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))
VIEWS ON THIS POST

64

Posted on:

Monday 29th October 2012
View Replies!

Trying to combine COUNT and SUM queries

I am hoping there is a way to combine these two queries: The first query is: Code: SELECT ck.campaign_id, COUNT(*) as total_clicks FROM clicks ck WHERE click_timestamp >= DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 7 DAY) GROUP BY ck.campaign_id For each campaign_id returned, total_commission needs to be calculated...i.e.: Code: SELECT SUM(publisher_rate) as total_commission FROM clicks ck WHERE campaign_id IN (-the results from above query-) GROUP BY ck.campaign_id So the final result set would be: campaign_id, total_clicks, total_commission The IDEAL query would do the above, but also return total_commission/total_clicks. Can anyone point me in the right direction Thank you!
VIEWS ON THIS POST

57

Posted on:

Monday 29th October 2012
View Replies!

create a DTS for two tables to combine

hi .. i am new to DTS's can someone tell me what i need to do in order to create a DTS so that i will be able to combine two tables together. I know i have to create a connection and i did that i just dont know what i need to choose to get the two tables..
VIEWS ON THIS POST

112

Posted on:

Tuesday 20th November 2012
View Replies!

combine query

Hi Guys, need your advise regarding my problem. I have to query to dispaly a result from same table, but different clause. table and data like this : - code type data1 data2 591 72 aa aa 593 72 bb bb 595 72 cc cc 591 72 dd dd 593 72 ee ee 595 72 ff ff 591 73 gg gg 593 74 hh hh 595 75 ii ii so I want to combine this 2 query :- 1st Query : SELECT * FROM test WHERE (code = '591' OR code = '593') AND (type = '72') 2nd Query SELECT * FROM test WHERE (code = '595') AND (type '72') So can you help me on this
VIEWS ON THIS POST

65

Posted on:

Monday 26th November 2012
View Replies!