how to combine two columns of a table to some columns of another table


i have 2 separate databases... in the 1st database i have a table with 14 columns and in 2nd database i have a table with 7 columns.i want to make this as a single table with 3 columns from 1st table and 2 columns from 2nd table. also both tables contain some common columns.how to match two tables using .net
Posted On: Saturday 6th of October 2012 12:40:41 AM Total Views:  185
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Can you combine two SELECTs which join on the same field (non trivial)

I have a table (choices) which includes two fields (first_choice,second_choise) which contain ID numbers of items, and an identifier for the row (choiceid) I have another table (items) which contains fields including the item ID (itemid), name of the item (name) and others. 1) Is it possible to create a MYSQL statement that would enable me to create a table containing the names of the choices If I was doing this in PHP I would code two seperate SELECTs... SELECT choiceid,name FROM choices,items WHERE choices.firstchoice=items.itemid ORDER BY choiceid SELECT choiceid,name FROM choices,items WHERE choices.secondchoice=items.itemid ORDER BY choiceid ... and then write a function to combine the two results by matching IDs. 2) Is it possible for the statement to work when the second choice has a value of 0 (and so will not have a value in the items table) My guess is that this may be possible with some form of JOIN statement. 3) I could do with the results being ordered in alphabetical order by first choice, and then second choice. This may be impossible, but I can hope.
VIEWS ON THIS POST

168

Posted on:

Thursday 11th October 2012
View Replies!

How can I combine my two queries?

I am trying to SELECT all the scheduled rules of all the suupliers but cant figure out how to join my two queries: 1.) THIS QUERY SELECTS ALL THE SCHEDULE OF ALL THE SUPPLIERS. THE FIELD condition_value is the suppliers unique code number: "select rules.rule_id, name, start_date, end_date, condition_value from rules, rules_schedule where rules.rule_id = rules_schedule.rule_id" sample output: rule_id| name | start_date | end_date | condition_value | 1 | first rule | 2009-01-01 | 2009-02-02 | 1234 | 2.)THIS QUERY SELECTS THE SUPPLIERS PP-ID- JUST SOME ID AND "NAME" AND "ASI" - WHICH IS THE SUPPLIERS UNIQUE CODE NUMBER OR SAME AS THE condition_value in query#1: "SELECT pp_id, (SELECT value FROM suppliers_attribute_map INNER JOIN suppliers_varchar USING (value_id) WHERE suppliers.supplier_id=suppliers_attribute_map.supplier_id AND meta_attribute_id=215 AND approved=1) as supplier_name, (SELECT value FROM suppliers_attribute_map INNER JOIN suppliers_int USING (value_id) WHERE suppliers.supplier_id=suppliers_attribute_map.supplier_id AND meta_attribute_id=127 AND approved=1) as asi FROM suppliers" sample output: pp_id | supplier_name | asi | 00-01 | ABC CO. | 1234 |
VIEWS ON THIS POST

161

Posted on:

Friday 12th October 2012
View Replies!

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.
VIEWS ON THIS POST

276

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

173

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

158

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

178

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

159

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

179

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

158

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

172

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

174

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

174

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

230

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

153

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

456

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

98

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

278

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

109

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

91

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

187

Posted on:

Tuesday 20th November 2012
View Replies!