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!
Posted On: Friday 12th of October 2012 03:45:54 AM Total Views:  96
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Is it possible to UPDATE (or SELECT) only columns that have specific value(s)?

So, if this is my table: Code: +---------+---------+---------+---------+ | column1 | column2 | column3 | column4 | +---------+---------+---------+---------+ | abc | xyz | hij | abc | +---------+---------+---------+---------+ 1) How can I UPDATE only the columns with a value of 'abc' For example, let's say I want to replace every 'abc' with 'qrs'... However, any of the columns in the table could have 'abc' (not just column1 and column4), so I can't explicitly reference the individual columns, because I don't know which columns will have 'abc' and which columns won't. So something like: PHP Code: UPDATEmyTableSETevery_column_that_has_abc='qrs'; This seems like it should be uber-simple. So, sorry, if has seen this question before. 2) I'm pretty sure the only thing you can do with the SELECT statement is use (*) to select all the columns, or explicitly select columns by name, but I thought I'd throw this out there anyway... so is there a way to SELECT only columns that contain a specific value (abc) Something like: PHP Code: SELECTonly_columns_that='abc'FROMmyTable;
VIEWS ON THIS POST

80

Posted on:

Friday 12th October 2012
View Replies!

Is it possible?

Can we write a procedure in mysql which will fetch some values from database and insert those values into a new table
VIEWS ON THIS POST

48

Posted on:

Friday 12th October 2012
View Replies!

Is it possible

is it possible to make Code: $query1= mysql_query("UPDATE `btable` SET `active2`='".time()."' WHERE buddy2 = '".$id."'"); $query2= mysql_query("UPDATE `btable` SET `active1`='".time()."' WHERE buddy1 = '".$id."'"); $update = mysql_query("UPDATE `user` SET `ltime` = '".time()."' WHERE `id` = '".$id."';"); these 3 queries one \t
VIEWS ON THIS POST

159

Posted on:

Friday 12th October 2012
View Replies!

Presetting field options, possible?

I'll keep it simply, it is possible to set a mysql field to only except information if it matches presetting field options Example, lets say i want to record answers to a survey.. Field | Type | Null | Key | Default | Extra Question1 | varchar(10) | NO | | | Question2 | varchar(10) | NO | | | Question3 | varchar(10) | NO | | | I want to store somewhere in the table inforamtion that each of the questions can either be Yes, No, Sometimes, Mabye and perhaps even store the actually full text question, is this possible
VIEWS ON THIS POST

83

Posted on:

Friday 12th October 2012
View Replies!

Conditional select via row possible?

I am trying to generate a sql query that will select all the data where row is 'this row #'... Is it possible... Suggestions.
VIEWS ON THIS POST

177

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to store DB for each domain/user in it's home folder?

folks. I'm wondering if there is a way to set up MySQL server to keep DB's for one user in it's home folder I wonder how those virtual (shared) hosting companies set it up What I mean is say if I have two users (domains) on the server they will have all their files in their home folders. /home/domain1.com/ /home/domain2.com/ I ask this because I find it neat to have all the files for one domain in one place - mostly for backup.
VIEWS ON THIS POST

184

Posted on:

Friday 12th October 2012
View Replies!

1. possible between ASP ? MySQL?

is that possible we use asp code to connect to the MySQL database i checked thru the library, i cant even find one any related between ASP and MySQL but php and MySQL ! 15 possible
VIEWS ON THIS POST

58

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to change PART of a column?

One of the columns in my table contains paths for files, like so: /path/to/file/file001.txt A particular folder might contains lots of files, like so: /path/to/file/file001.txt /path/to/file/file002.txt /path/to/file/file003.txt If I move these files to a different folder, I would like to update my columns to change the folder part of the string in every row, but leave the filename alone: /new/path/file001.txt /new/path/file002.txt /new/path/file003.txt Can this be done with a single query I feel I am getting close with the likes of: UPDATE files SET location = "new/path/" WHERE location LIKE "old/path/file/%"; This changes the folder name, but naturally wipes the end off as the file name is not included in the "SET location =" command. Is it possible to format SET so that it only changes part of the column's string Could this be accomplished using REPLACE
VIEWS ON THIS POST

56

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to trim everything eitherside of a string?

Hi , Just before I go home for the weekend can anyone tell me how to select only part of a field, possibly using trim I have fields like below Code: |4444 4444 4444 4444,VISA - Credit | |,,,,,4444 4444 4444 4444| and basically I would like to use a regular expression to identify that the number exists and then trim everything eitherside of the number so I am left with Code: 4444 4444 4444 4444 Is this possible I am happy writing regular expression, I am just ot sure if I am trying to achieve too much with SQL, if anyone has any ideas I would be greatful if you could mention them here
VIEWS ON THIS POST

74

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to use two aggregate SQL function together

hello i am trying to use two aggregate functions ( at the same time) on an SQL query; i am not sure if this is possible. i will explain; i have a loggin script that records ,in UNIX time stamp, the exact time a member logs into the site. this is stored in a MYSQL table called ; 'loggin_times'. in a column called login_time. i now wish to receive three things from this table 1. the number of times the user has logged in . COUNT ( login_time)numberlogins 2. the last time occassion when the user logged in. MAX ( login_time ) lastlogin 3. the exact time that the user logged in. FROM_UNIXTIME( login_time )time In my SQL query i obviously first have to do a 'GROUP BY' of the loggin times, and then use the aggregate functions listed above. i am clear on this point. my question relates to part 3 of the above list, i.e obtaining the exact time of the last login. To get the time, i presume that i must convert the data ( that was obtained from the Max ( ) function) and convert this into a date format. i tried to do the following 'doubling up' of two functions but it did not work FROM_UNIXTIME (lastlogin, '%D %b %y' ( MAX ( login_time )lastlogin )time_loggin i also tried the following; MAX(lo.user_id)lastlogin, FROM_UNIXTIME (lastlogin, '%D %b %y' )time_loggin is it possible to combine these two agrregrate functions if not, how can i convert the data obtained from the Max () function into a date warm regards Andreea
VIEWS ON THIS POST

76

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to ORDER BY sum(amount) if there is $ sign in the column?

; Is it possible to ORDER BY sum(amount) if there is $ sign in the column The values are like $240, $544,...
VIEWS ON THIS POST

81

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to do this with 1 query?

I save prices data for different days. If a day doesn't have any changes, it is not in the database. table prices date | price ---------------------- 2010-10-01 | 200 2010-10-02 | 200 2010-10-03 | 220 2010-10-06 | 260 2010-10-09 | 190 ... Now I need to prices for statistic purposes for 1 month, but only for every 7 days of the month. If there is not data in each 7 day, it checks what was the price for the most recent date that exists. For this I make for each 7 day a query, so I need 4 queries 1. SELECT pageViews FROM stats WHERE date
VIEWS ON THIS POST

74

Posted on:

Friday 12th October 2012
View Replies!

Impossible with join?

I have two tables with the following fields: 1 - uid, account_number, company 2 - uid, fk_account_number, month, year, value. I can't see any way without using a sub-query to do a sum on all values in the second table where the month is 7 and the year is 2010, as well as being able to get all values where the year is 2010 regardless of month. Is there a way An example is PHP Code: SELECT u.uid, u.company, SUM(value)-Somethingisneededhere... FROMusersu INNERJOINsalessONs.fk_account_number=u.account_number GROUPBYu.uid EDIT - Would a UNION work here
VIEWS ON THIS POST

63

Posted on:

Friday 12th October 2012
View Replies!

SQL JOIN in PHP - is it possible to retrieve two different values from one column?

, my first time in this forum, i have tried looking for the solution to my problem on this forum as well as others, however i have not found an answer yet. would much appreciate your help. I am building a web based database using php and mysql. i think this problem is more associated with mysql. basically i have a two tables in the database: Location, Distance. Location consists of the fields: Location_ID, name. Distance consists of the fields: Distance_ID, origin, destination, distance. the two fields origin and destination in the distance field, reference the Location_id. what i am wondering is, is it possible to create a query that will out put a table with the following details: Distance_ID, Origin_name, Destination_name, distance. for example: Location_ID, name 1, london 2, paris 3, rome Distance_ID, origin, destination, distance 1, 1, 2, 50 2, 1, 3, 75 3, 3, 2, 12 what i want to display is: Distance_ID, Origin, Destination, Distance 1, london, paris, 50 2, london, rome, 75 3, rome, paris, 15 the code that i have so far is: Code: $query="SELECT DistanceID, Location.name, Location.name, distance FROM Distance INNER JOIN Location ON Distance.origin=Location.locationID"; $result=mysql_query($query); $num=mysql_numrows($result); mysql_close(); $i=0; if ($num==0){ echo "Sorry, there are no distances."; } else while ($i < $num) { $DistanceID=mysql_result($result,$i,"DistanceID"); $origin=mysql_result($result,$i,"Location.name"); $destination=mysql_result($result,$i,"Location.name"); $distance=mysql_result($result,$i,"distance"); echo "$DistanceID$origin$destination$distance"; $i++; } what my code will output is : Distance_ID, Origin, Destination, Distance 1, london, london, 50 2, london, london, 75 3, rome, rome, 15 this is not exactly what i want. i can sort of see where the problem is (the issue with Location.name), but i have no idea how to solve this, or whether it is even possible. i am not familiar with INNER JOIN, but i understand that Code: INNER JOIN Location ON Distance.origin=Location.locationID gets stored in origin Code: $origin=mysql_result($result,$i,"Location.name") aswell as destination Code: $destination=mysql_result($result,$i,"Location.name"); . and that only Code: INNER JOIN Location ON Distance.origin=Location.locationID is declared for origin. but if i also do it for destination, the code breaks. i.e. Code: "SELECT DistanceID, Location.name, Location.name, distance FROM Distance INNER JOIN Location ON Distance.origin=Location.locationID INNER JOIN Location ON Distance.destination=Location.locationID" any ideas on how to display the names of both the origin and destination would be much appreciated. edit: i guess i could always create 2 tables named location_origin and location_destination, but my idea was to save space by just having one table to serve the purpose of storing names of places. though i wonder if it would be a better/easier idea to have the two tables instead of one.
VIEWS ON THIS POST

85

Posted on:

Friday 12th October 2012
View Replies!

Problem with fulltext searching, possible alternatives?

Hi , I have two tables, they both contain domain names. For arguments sake, lets say: known_domains - The table of domains I care about mapped to customer ids. About 200,000 domains are listed in this table. domain_data - A table holding data about random domains, around 300,000 rows but varies significantly on a day to day basis. The domain_data table is populated by a job that has to run in the shortest time possible, it has no time to be checking if the domain it is recording data for exists in the known_domains table. I have another job that compares the domains and removes them from the domain_data table if they do not appear in the known_domains table. At first, I thought this would be easy but I ran into a big headache with subdomains Basically, imagine that exampledomain.com listed in known_domains, sub.example.com may or may not (in fact its a whole lot more complicated than this, just trying to keep it simple for now). I still need to keep the data for the subdomain even if it's not explicitly defined. Ideally, what I need to do is: Code: SELECT id from known_domains WHERE domain = 'exampledomain.com' OR ('exampledomain.com' REGEXP CONCAT('^(.+\.)', known_domains.name, '$') Obvously, thats just not viable when checking 300,000 domains as, understandably, each query takes about 5 seconds. I've setup a fulltext index on known_domains.name and call Code: SELECT id,domain, MATCH(name) AGAINST('exampledomain.com') AS score FROM known_domains MATCH(name) AGAINST('exampledomain.com') ORDER BY score DESC LIMIT 10 Now thats probablly not going to be totally accurate but the testing I have done seems to indicate that the matches I am interested in will be in the first 10 rows. The 10 rows returned are passed through some script voodoo to determine the best possible match. Thats all fine and dandy but when examining the logs, I noticed that I was deleting quite a few rows for domains that have entries in the known_domains table. Mostly these domains appeared to be 6 characters in length but thats not always the case. For example: abc.com in known_domains but nothing returned when matching against either foo.abc.com or abc.com abc.def.de in known_domains but a match against abc.def.de returns nothing. I've checked ft_min_word_length and that is set to 4 so Im somewhat confused by this and am tired of The only other thing I can think of is that the problem being caused by stopwords but after looking at the default stopwords I can't see it. I should add that I'm using Mysql v4.1.20 (not in a position to upgrade either at this time). Wondering if anyone here is able to shed any light on the issue or perhaps suggest an alternative way of getting to the end goal. TIA.
VIEWS ON THIS POST

82

Posted on:

Friday 12th October 2012
View Replies!

Set a as database variable possible ?

set a = db1 select * from a.table1 where col1='123'; is this possible in mysql 50 server or any refferences link Best
VIEWS ON THIS POST

65

Posted on:

Friday 12th October 2012
View Replies!

Is it possible to create index for a temporary table

ALL, I have to do SQL query over two temporary tables, I want to create indexes on fields of the temporary tables. Is it possible to create index on a temporary table. I am using MySQl 5.0.26 Furthermore, Is it possible to merge the statements for creating temporary table and the statement for creating index on the temporary table into one singel statement Code: Create Temporary Table Tmp AS Select Tb1.filed1 AS field1 FROM Tb1 LEFT Join Tb2 On Tb1.field1 = Tb2.field1 Code: CREATE INDEX idx_field1 ON Tmp (field1)
VIEWS ON THIS POST

73

Posted on:

Friday 12th October 2012
View Replies!

How possible is this mysql query?

all l am trying to mass update my table using this query. l use this query to select username and customerid to select from customer(table) users that have sponsors as 2 from the table. The sponsor is done when a user want to register he or she needs a sponsor to before he or she can register and the sponsor id is the username of the person who referred him. To register you must insert a sponsor id which is the same with the username of the person who referred you. On insert, the sponsor id which is the username of your referrer would be inserted in the sponsor column of the new user's row as his or her sponsor. so below is the query that select from the customer table users who have 2 sponsors. $sql = "SELECT sponsor,mstatus, count( username ) from customer Group by sponsor DESC Having count( username ) = 2 so l want to mass update users that have 2 sponsors or atleast a sponsor.
VIEWS ON THIS POST

73

Posted on:

Friday 12th October 2012
View Replies!

Single query possible?

I am building a dynamic cms. First I creat a list, then I asign inputfields to this list. So e.g. A list called 'cars' (table content_List) The inputfields are: 'brand', 'description', 'country' (table content_Field) Some different cars with different specs (table content_Text) I am trying to create a single query, but so far I could only acieve this with a loop within a loop using a second query. This is my dump: CREATE TABLE `content_Field` ( `fieldID` int(11) NOT NULL auto_increment, `fieldListID` int(11) NOT NULL default '0', `fieldName` varchar(255) NOT NULL default '', `fieldType` varchar(25) NOT NULL default '', `fieldSort` varchar(255) NOT NULL default '', `fieldOrder` int(11) NOT NULL default '0', PRIMARY KEY (`fieldID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Gegevens worden uitgevoerd voor tabel `content_Field` -- INSERT INTO `content_Field` (`fieldID`, `fieldListID`, `fieldName`, `fieldType`, `fieldSort`, `fieldOrder`) VALUES (1, 1, 'brand', 'text', '', 0), (3, 1, 'country', 'text', 'alfabet ASC', 0), (4, 1, 'description', 'html', '', 0); -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `content_List` -- CREATE TABLE `content_List` ( `listID` int(11) NOT NULL auto_increment, `listName` varchar(255) NOT NULL default '', PRIMARY KEY (`listID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ; -- -- Gegevens worden uitgevoerd voor tabel `content_List` -- INSERT INTO `content_List` (`listID`, `listName`) VALUES (1, 'cars'); -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `content_Text` -- CREATE TABLE `content_Text` ( `textID` int(11) NOT NULL auto_increment, `textItemID` int(11) NOT NULL default '0', `textListID` int(11) NOT NULL default '0', `textFieldID` int(11) NOT NULL default '0', `textContent` text NOT NULL, `textOrder` int(11) NOT NULL default '0', PRIMARY KEY (`textID`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ; -- -- Gegevens worden uitgevoerd voor tabel `content_Text` -- INSERT INTO `content_Text` (`textID`, `textItemID`, `textListID`, `textFieldID`, `textContent`, `textOrder`) VALUES (1, 1, 1, 1, 'volvo', 10), (5, 2, 1, 1, 'peugot', 20), (3, 1, 1, 3, 'sweden', 10), (4, 1, 1, 4, 'This car is very safe', 10), (6, 2, 1, 3, 'france', 20), (7, 2, 1, 4, 'This is a car with good seats', 20), (8, 3, 1, 1, 'saab', 30), (9, 3, 1, 3, 'sweden', 30), (10, 3, 1, 4, 'Car which is very expensive', 30), (11, 4, 1, 1, 'BMW', 40), (12, 4, 1, 3, 'germany', 40), (13, 4, 1, 4, 'I cannot affort this one...', 40); Now I want to create a query so that the following would be printed: peugot france This is a car with good seats BMW germany I cannot affort this one... saab sweden Car which is very expensive volvo sweden This car is very safe As you can see, the list is sorted by the countrie (alfabet). This is because I have assigned the field 'countrie' (in content_Field) to be alfabet ASC. What I have now is: query1: SELECT * FROM content_Field, content_Text, content_List WHERE listID = '1' content_List.listID = content_Field.fieldListID AND content_Text.textListID = content_Field.fieldListID AND content_Text.textFieldID = content_Field.fieldID AND fieldSort != '' do_query1 { query2: SELECT * FROM content_Text, content_List LEFT JOIN content_Field ON (textFieldID = fieldID AND fieldListID = '1') WHERE textListID = listID AND content_Text.textItemID = '$textItemID_query1' ORDER BY fieldOrder ASC do_query2 { echo the list. } while query2 } while_query1 So in fact query 1 is to get the order from the textItemID, query2 is to echo the complete list which goes with the textItemID that came out of query1 I wonder if it is possible to get these 2 into 1 query. I do get the seperate sublists sorted by textItemID, but the sublists should be ordered alfabetacally. Is there a way to do this\t
VIEWS ON THIS POST

59

Posted on:

Friday 12th October 2012
View Replies!

Multiple tables insert not possible?

hi! i can't find information on multiple tables INSERT. is it possible this query fails: mysql Code: Original - mysql Code INSERT INTO srv_stk_utils.pog_citycodes2groups ssp, srv_stk.groups ssg ( ssg.name, ssp.citycode, ssg.req ) VALUES ( 'asxasx', '234342', '2343' )
VIEWS ON THIS POST

78

Posted on:

Friday 12th October 2012
View Replies!