Sub Query And Count Query
i have a database with the following structure
id | MoveDate | ItemId | SiteID
(a new entry is entered when an item is moved from 1 site to another)
and i am trying to forumlate a query so that i can count how many days each item was at a specific location
so lets assumes i have the following data
1 | 01/01/2007| 1 | 1
2 | 03/01/2007| 1 | 2
how can i run a query that will tell me that between the dates 01/01/2007 and 08/01/2007 item 1 was at site 1 for 2 days and site 2 for 5
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
SQL Count Query
I have 3 tables: j_photoparent (holds photo "parent" e..g "nature", "places") j_photocat (holds photo categories, e.g. "leaves", "snow", "london eye") j_photosmaster holds photo details The keys are as follows: j_photoparent.parentID j_photocat.catID j_photocat.parentID j_photosmaster.photoID j_photosmaster.catID I have a simple SQL statement to randomly select a photo category: SELECT jpc.catID, jpp.parentID FROM j_photocat jpc, j_photoparent jpp WHERE jpp.parentID = jpc.parentID ORDER BY RAND() LIMIT 1 What would be great though, would be if I could combine the SQL above, to also count how many photos in the "j_photosmaster" table for each randomly selected category. Could it be done in the same statement - or would I need to do two - the first one above and then this one: SELECT COUNT(*) FROM j_photosmaster WHERE CatID = &catID_FROM_ABOVE Could it be done with a sub-select?
Help With Query Using Count
I have the following query, but for some reason I can't seem to get the price count part to work. What I want to do is the get the max and min price for each indivdiual product and I then want to count how many products of the same where found with these different prices. The only problem is that the count part seems to be giving me the wrong totals. For some it is giving me 783 when there is only 2 of the same products. Here is the query that I have. PHP select ides,merchantes.merid,merchant,price,min(price) as minprice,max(price) as maxprice,count(price) as pricecount,rand3,title,imageurl,brand,description,pm from merchants,prodsearches where prodsearches.merid=merchants.merid and app=Ƈ' and match (brand,category) against ('"games"' IN BOOLEAN MODE) group by brand,category order by random2 asc
Query Count
I've seen on some sites that there is a way to total up the number of queries ran on a page, and the time it took to run them - as per the bottom of this page: Is this something which can only be done using PHP, or could I achieve the same result using ASP, by accessing something within MySQL?The reason I ask is that I have a site running MySQL on a Windows server, running IIS and ASP, and would like to set something up like the above.
COUNT() 2 Tables In 1 Query
So I have these two queries: $result= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_main where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)"); $result2= mysql_query("SELECT YEAR(add_date), MONTH(add_date), COUNT(*) FROM clogsyn_track_pop where user_id='$uid' GROUP BY YEAR(add_date),MONTH(add_date)"); They output data like Month Year, Total Views So I can see how many total times a page was visited that month for a specific user_id. The problem is I'm tracking 2 sections of the site "main" and "pop" which pop is a unique page that pops up when something is clicked. I tried EVERY way to join them together but the count(user_id) doing a LEFT JOIN kept adding the count()'s from both tables together. Is it not possible to do a count() on two tables in 1 query? Here's one of the many combined I tried. $sql = "SELECT a.user_id,b.user_id,YEAR(a.add_date),MONTH(a.add_date),COUNT(a.add_date),COUNT (b.add_date) FROM clogsyn_track_main a LEFT JOIN clogsyn_track_pop b ON a.user_id=b.user_id AND YEAR(a.add_date)=YEAR(b.add_date) AND MONTH(a.add_date)=MONTH(b.add_date) WHERE a.user_id='$uid' GROUP BY YEAR(a.add_date),MONTH(a.add_date)"; Can anyone help me here or is it not possible to do it in 1 query?
Query To Return Two Count()s
I'm trying to figure out a better way of doing this: SELECT (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 1 AND EventStatus = 'y' AND UserID = 'bob') AS Event1, (SELECT COUNT(*) FROM GuestEvents WHERE EventID = 2 AND EventStatus = 'y' AND UserID = 'bob') AS Event2 As you can see it's quite repetitive, I wondered if there isn't something like this which could optimise it a bit: SELECT COUNT(EventID=1) AS Event1, COUNT(EventID=2) AS Event2 FROM GuestEvents WHERE EventStatus = 'y' AND UserID = 'bob'
WHERE COUNT MySQL Query
PHP $queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews GROUP BY soundcard_model ORDER BY value DESC LIMIT 10"; Okay, there's my MySQL query. I have a crappy little review script that lets people review home recording sound cards. This script will display 10 sound cards with the highest average score for overall value. As you can tell, this selects the the company, model, and average overall value rating of sound cards that have been reviewed on my site. It puts them in descending order and limits the results to the top 10. I've found that these results are easily skewed when a particular sound card gets only one review. I don't want a single person to have the power to put a sound card on top of my ratings. So, I want to select sound cards from the database that have had at least 3 votes. While I could come up with a php solution, I'd prefer to use MySQL. I have tried this: PHP $queryA = " SELECT soundcard_co, soundcard_model, count(soundcard_model) as count_model, AVG(value_num) AS value FROM reviews WHERE COUNT(soundcard_model)>2 GROUP BY soundcard_model ORDER BY value DESC "; But I keep getting a "Invalid use of group function" error.
Count Query Problem
I want to count users by their status, following is the code which I am using currently, instead of using four queries I want to convert this code in one query....
Count Records Within Query
I have the following information in table 'Test1': field1 field 2 000001 000000 000002 000001 000003 000000 000004 000000 I want to write a query that will only display the unique field 2 records and as well provide a count of how many records have that id. output should look like field2 recno 000000 3 000001 1 can some one help with the trick to make this work?
Simple Count Query
I am trying to use the below query to count the number of images held for each book. Unfortunately it is only returning the books with images. I would like all books returned even if they have no corresponding image. SELECT tblBooks.Title, tblBooks.Description, tblBooks.Author, tblBooks.Annotation, tblBooks.Collection, tblBooks.Price, tblBooks.SortID, tblBooks.Heading, Count( tblImage.ImageID ) AS CountImage FROM tblBooks INNER JOIN tblImage ON tblImage.BookID = tblBooks.BookID GROUP BY tblBooks.BookID
Make A COUNT Query
I want to run a query on quite a big table, about 3,000,000 records. The query is quite simple SELECT COUNT(*) FROM TblName WHERE Something = 1 I am only interested to know whether there are at least 10,000 records that meet this condition, so counting the whole table and then comparing the result to 10,000 is overhead. Is there a way to write a query that would simply tell me if there are at least 10,000 (or any other number) records, and would stop executing/counting once it gets to 10,000?
Query Count Return Zero
I'm trying to query a database searching for everytime an event happened in the past 24 hours. I'm using a count(eventid) to add up each event during an hour time. However, if an event didn't happen say for 2AM then 2AM isn't shown in the return. How can I get 2AM to show as a row, but with a count of 0 (zero) ?? Here is my qeury... select count(eventid), DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H') as hour, time_format(eventtime, '%H:00'), eventdate from event where sensorid = '2' and eventdate between '2005-01-19' and '2005-01-20' and DATE_FORMAT(CONCAT(eventdate,' ',eventtime),'%Y%m%d%H%i%s') >= '20050119150018' group by hour order by hour desc;
MySQL Query COUNT
I need to create a sql statement that queries a mysql database for the top x amount of entries that include their name. Basically the site logs everytime they upload a file, and I want to see which users have the top number of uploads. Any ideas? Thanks in advanced to any help. Table: fileid filename submittername submitterip fileinfo filetype date
Help With Query: Order By Association Count
Allright, here are my tables: Posts: id | title | text Comments id | post_id | name | comment Basically one post may have many comments. Now, how would I select the 10 posts with most comments, and if only 5 posts contained comments (but there are in fact 10 posts) how could I make this query still return a total of 10 posts?
SQL Query To Include Count From Associated Table
I am having a tough time coming up with the sql for the statement but the long and short of it is this: Two Tables: Table 1 is a group - Table 2 is a list of items that belong to a group from table 1. I want to query all (*) information from table 1 AND get the count of the number of associated items from that group from table 2 in that same query.
Retrieve Everything AND Count Rows In One Query
set rsminmax = con.execute("SELECT * FROM `minirules_minmax` where RuleId = '" & contractId & "'") set rsminmax2 = con.execute("SELECT COUNT(*) FROM `minirules_minmax` where RuleId = '" & contractId & "'") Is there any way to do this in one SQL query?
First Group By Date, Then Count... All In One Query?
I have a mySQL database that has individual records for each time a page on my site is hit. What I'm trying to do is set up a tracking system which will display the hit counts for each page, by month, for the past three months, and ordered by total hits over that timespan. The table is simple: visitedPage | visitedTime My current query is this:
Need Single COUNT Value For Complex Query
I have a moderately complex query and I want to page the results, limiting to 50 on a page. I know that in order to do so, I need to know the total number of records so that I know whether there are more records remaining so I can show a 'NEXT PAGE' link. I'm having trouble constructing a COUNT query from my original query because the query accesses 4 tables, two of which are many-to-one association tables. Here is the original query, without a LIMIT clause:
Count The Hits For Week And Month In One Query?
I have this query to count the amount of total clicks for a whole week, but i would also like to know the total amount of clicks for the whole month. Is it possible to do both of them with one query? Or do i really need to make 2 seperate querys for that? SELECT date FROM clicks WHERE date >= '$timestamps[0]' and date <= '$timestamps[6]' AND link_id = '{$row['ad_linkid']}'
Preventing A Count Query Inside A Loop How?
I have an idea in my head and im pretty sure its possible but I don't have enough mysql knowledge to apply. Basically, I have this: SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id then in php I initialise a loop go over the results: PHP while ($row = mysql_fetch_array($get_users)) { $this_user = $row['user_id']; and then an insert query: INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign) while this is all dandy, I don't want duplicate data. My only current method would be PHP $get_users = mysql_query("SELECT user_id FROM ml_group_subscriptions WHERE user_group = $id"); while ($row = mysql_fetch_array($get_users)) { $this_user = $row['user_id']; $check = mysql_result(mysql_query("SELECT COUNT(user_id) FROM ml_subscriptions WHERE user_id = $this_user"),0); if ($check == 0) { $insert_users = mysql_query("INSERT INTO ml_subscriptions (`user_id`, `campaign_id`) VALUES ($this_user, $add_to_campaign)"); } } as you can see this is putting a query inside a loop which could be potentially huge. Can I not adapt the first SELECT query to only accept data that is not about to be duplicated?
Sql Query For Count Records Of Current Week
I have a registration form that saves the registration date on a datetime column. I would like make a query to know how many records are in the table that where for the current week. For example, I'm on week 25 and I want to know how many records I have for this week without the need of specifying the from and to dates. Is there a way to do this?
Trying To Count The Number Of Rows In A Result Set After Query
The user fills out this form to sign up to the website, the form checks the database to see if the username has already been taken with the code: $conn = mysql_connect("localhost:3306", "root", "********") or die ("Error With Connection"); echo("connected<br><br>"); $db_sel = mysql_select_db("game",$conn) or die ("Error With Database"); $check = "select * from users where 'username' = '$username'"; $db_sel = mysql_query($check,$conn) or die (mysql_error());
How To Improve The Speed Of Mysql Query Using Count(*)
I'm using this kind of queries in mysql in InnoDB engine Select count(*) from marking1 where persondate between '2007-04-23 00:00:00.000' and '2007-04-23 23:59:59.999' and PersonName='aaa' While executing these queries from front end VB, It takes above 5 secs with 50 thousand records. How can I improve speed for this kind of queries. Is there any alternation for this command.
Query The Article Table And Get A Count Of All Related Comments
Could someone please help me with this query. I have two tables -- one for my articles and another for my comments. Comments are stored in the comments table with a corresponding article id. I want to query the article table and get a count of all related comments. The following query doesn't seem to work for me. Can you please suggest how I fix it? SELECT a.id AS id, a.title AS title, a.preview AS preview, a.thumbnail AS thumbnail, a.category AS category, a.timestamp AS timestamp, a.game AS game, count( b.id ) AS commentcount FROM article_table AS a, comment_table AS b LEFT JOIN comment_table ON a.id = b.article WHERE a.news = 'Yes' AND a.type = Ƈ' AND a.saved != Ƈ' GROUP BY b.article ORDER BY a.timestamp DESC LIMIT 5
Count One Table's Rows From Multi Table Query
here are my tables (condensed) FEEDS feed_id site_id SITE site_id site_name ARTICLES article_id feed_id link I want to create a query that returns the total number of articles for every site_id (which is unique in the SITE table). I have this: PHP $gsite = mysql_query("SELECT site.site_id, feeds.feed_id, COUNT(articles.article_id) AS acont FROM site,feeds,articles WHERE feeds.site_id = site.site_id AND articles.feed_id = feeds.feed_id group by site.site_id", $connection) or die(mysql_error()); The query does not use JOIN, ON and all that good stuff. I just need the following variables to run through a loop: site_id the number of articles rows per site_id
Extending Simple Query With Specific COUNT() From Second Table
Currently i basically do following: ----------------------------------- SELECT cat_id, cat_name FROM categorytable WHERE blah=something ORDER BY pos ASC while(FetchRowAssoc()) { SELECT COUNT(*) as itemcount FROM itemtable WHERE category=cat_id echo cat_it, cat_name, itemcount, blah... etc. } ------------------------------------ I fetch some info from the category table and then I want to know how many items I have in the 2nd table which are associated to the categories in question from the first table. Can this be done with a single query statement, so i get from FetchRowAssoc the cat_id, the cat_name and the count of the items for this category? "SELECT t1.cat_id, t1.cat_name, COUNT(t2) as itemcount FROM categorytable as t1, itemtable as t2 WHERE t1.blah=something ORDER BY t1.pos ASC " <- like this but working, i really don't get how to properly write the syntax there... also didn't found a useful example with googling.
How Can I Make A Query Like Microsoft Access, And A Query From A Query
I am new to MYSQL and am trying to understand how to make queries... I am moving from Microsoft Access where it is GUI driven and easy! I can make a simple single query using MYSQL Query Browser, say: qry1: SELECT ID, Area FROM data GROUP BY Area How can I store this as a query inside MYSQL, rather than having to code it each time? In Microsoft Access I could enter a variable ($VARIABLE) and then pass by code to the query: qry2: SELECT ID, $VARIABLE FROM data GROUP BY $VARIABLE How can I store this as a query and then pass the variable from code? In Microsoft Access I could base a query on the results of another query, so following example above: qry3: SELECT qry1.Area, data.ID FROM qry1 INNER JOIN data ON qry1.Area = data.Area; How can I store this as a query in MYSQL.
Count Query Causing "excessive Processor Usage"
For the second time in the past 6 months my hosts have shut down my site because it was apparently causing a shared mySQL 5 server to max out at 100% usage. I'm not much a programmer, know even less about mySQL and obviously don't have access to the server from the back end but I'd appreciate any advice as to whether it is my query that is causing the problem: Set rsMail = objConnC.Execute("SELECT * ,(SELECT COUNT(*) FROM comments WHERE comments.submissionID = submissions.submissionID AND comments.commentInclude = 1) as CommentCount FROM submissions WHERE PigeonHole = 'mailbag' AND Status <> 'hold' ORDER BY submissionID DESC LIMIT 20") Apart from two instances, this query has been working fine on a page that receives between 10,000 and 30,000 visits a day. The site is running on Windows and coded in ASP but is calling a separate mySQL server.
Count User Comments In Each Category (was "help On A Query")
I would really appreciate if someone could help me out on this one. this are my tables: comments Comment_ID Video_ID Comment_Text Comment_UserID .... video Video_ID Category_ID ..... category Category_ID Category_Name I'm trying to get a user's count of comments for each category, ordered by the number of comments DESC. basically, I want to show the top categories where the user has posted the highest number of comments. so far this query gets me the category name of where the user has posted: PHP $SQLTemp = "SELECT Category_Name FROM category WHERE Category_ID IN ( SELECT DISTINCT ( Category_ID ) FROM video WHERE Video_ID IN ( SELECT DISTINCT ( Video_ID ) FROM comments WHERE Comment_UserID = $id ) )"; THANKS!!
Count Of *unique* Visits By Page For Each Day (was "Need Help With Query")
Using MySQL 4.1 I have a table 'orders' with the following fields: ipaddress pagenum xtime (which is just the yearmonthday) this is the query: SELECT distinct pagenum,ipaddress,xtime,count(pagenum) AS pagenumtotal FROM `orders` group by pagenum,xtime ORDER by xtime, pagenum asc What I am trying to do is return a count of *unique* visits by page for each day. This query returns a count of *all* visits by page for each day, including repeats. So if a visitors hits a certain page on the site 10 times and adds a record 10 times, the count result will include all 10 records in the count. I want this to only count as 1.
Add COUNT(*) To Already Existing Query (was "How To Do This")
I have this query: PHP SELECT entries.*, categories.*, uploads.*, entries.blog_id FROM entriesINNER JOIN categories ON (categories.category_id = entries.category_id) LEFT JOIN uploads ON(uploads.blog_id = entries.blog_id) LEFT JOIN images ON (images.blog_id = entries.blog_id) ORDER BY entries.date_submitted DESC LIMIT 3 Now i want to add this PHP SELECT COUNT(*) FROM comments but i dont know how to add it into the already existing query. The messages table has a column blog_id column in it so i could do a join like thisd LEFT JOIN comments ON PHP (comments.blog_id = entries.blog_id) but i dont know how to get the first bit in there
How To Create Efficient MySQL Query From A Pseudo Query
I'm trying to build a webapplication where users can search for a person having a particular preference for color and material. To store this information I use the following structure (a MySQL dump can be found at the end of this post): *table person with fields: -persid: autoincrement id -name: name of the person *table material with fields: -materialid: autoincrement id -material: name of the material eg "wood" *table color with fields: -colorid: autoincrement id -color: name of the color eg "green" *table persmaterial with fields: -persmatid: autoincrement id -persid: link to table person -materialid: link to table material *table perscolor with fields: -perscolorid: autoincrement id -persid: link to table person -colorid: link to table color In the webapplication the search can be entered by the users as a kind of pseudo query: (color=red OR color=blue) AND color=green AND material=iron My question is: how can I automatically transform this pseudo query into an efficient MySQL query? I have tried out some different options: Option 1: (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) UNION (SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING (count(DISTINCT pc.colorid)=2 AND count(DISTINCT pm.materialid)=1)) Remarks: *I do not see how to turn a general pseudo query into a query like the one in option 1, except for turning the pseudo query into a sum of products form where the sulms would correspond to the UNIONs. IS there a clever way to obtain such a sum of products form from an arbitrary pseudo query? Option 2: SELECT persid FROM person p WHERE (EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=1 AND p.persid=pc.persid) OR EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=3 AND p.persid=pc.persid)) AND EXISTS(SELECT * FROM perscolor pc WHERE pc.colorid=2 AND p.persid=pc.persid) AND EXISTS(SELECT * FROM persmaterial pm WHERE pm.materialid=2 AND p.persid=pm.persid) Remarks: *very easy to get from pseudo query to MySQL query but what about performance? Option 3: SELECT p.persid FROM person p, perscolor pc, persmaterial pm WHERE p.persid=pc.persid AND (pc.colorid=1 OR pc.colorid=2 OR pc.colorid=3) AND p.persid=pm.persid AND pm.materialid=2 GROUP BY p.persid HAVING sum(case when pc.colorid in (Ƈ',Ɖ') then 1 else 0 end) >= 1 AND sum(case when pc.colorid=ƈ' then 1 else 0 end)>=1 AND sum(case when pm.materialid=ƈ' then 1 else 0 end)>=1 Remarks: *this option requires the pseudo query to be turned into a product of sums form; again is their a clever way to obtain such a form; Option 4 SELECT DISTINCT pc1.persid FROM perscolor pc1 INNER JOIN perscolor pc2 ON pc1.persid=pc2.persid AND pc2.colorid=2 INNER JOIN persmaterial pm1 ON pc1.persid=pm1.persid AND pm1.materialid=2 LEFT OUTER JOIN perscolor pc3 ON pc1.persid=pc3.persid AND pc3.colorid=1 LEFT OUTER JOIN perscolor pc4 ON pc1.persid=pc4.persid AND pc4.colorid=3 WHERE COALESCE(pc3.persid,pc4.persid) IS NOT NULL Remarks: *this option requires the pseudo query to be turned into a product of sums form Option 5: SELECT p.persid FROM person p, persmaterial pm,perscolor pc1,perscolor pc2,perscolor pc3 WHERE p.persid=pm.persid AND p.persid=pc1.persid AND p.persid=pc2.persid AND p.persid=pc3.persid AND (pc1.colorid=1 OR pc2.colorid=3) AND pc3.colorid=2 AND pm.materialid=2 GROUP BY p.persid Remarks: *very easy to get from pseudo query to MySQL query but what about performance? -- phpMyAdmin SQL Dump -- version 2.6.1 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Oct 19, 2006 at 01:13 PM -- Server version: 4.1.9 -- PHP Version: 4.3.10 -- -- Database: `aston` -- -- -------------------------------------------------------- -- -- Table structure for table `color` -- CREATE TABLE `color` ( `colorid` int(11) NOT NULL auto_increment, `color` varchar(30) NOT NULL default '', PRIMARY KEY (`colorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `color` -- INSERT INTO `color` VALUES (1, 'red'); INSERT INTO `color` VALUES (2, 'green'); INSERT INTO `color` VALUES (3, 'blue'); INSERT INTO `color` VALUES (4, 'yellow'); -- -------------------------------------------------------- -- -- Table structure for table `material` -- CREATE TABLE `material` ( `materialid` int(11) NOT NULL auto_increment, `material` varchar(30) NOT NULL default '', PRIMARY KEY (`materialid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `material` -- INSERT INTO `material` VALUES (1, 'wood'); INSERT INTO `material` VALUES (2, 'iron'); -- -------------------------------------------------------- -- -- Table structure for table `perscolor` -- CREATE TABLE `perscolor` ( `perscolorid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `colorid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`perscolorid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Dumping data for table `perscolor` -- INSERT INTO `perscolor` VALUES (1, 1, 1); INSERT INTO `perscolor` VALUES (2, 1, 2); INSERT INTO `perscolor` VALUES (3, 2, 1); INSERT INTO `perscolor` VALUES (5, 3, 3); INSERT INTO `perscolor` VALUES (6, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `persmaterial` -- CREATE TABLE `persmaterial` ( `persmatid` int(11) NOT NULL auto_increment, `persid` int(11) NOT NULL default Ɔ', `materialid` int(11) NOT NULL default Ɔ', PRIMARY KEY (`persmatid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ; -- -- Dumping data for table `persmaterial` -- INSERT INTO `persmaterial` VALUES (1, 1, 1); INSERT INTO `persmaterial` VALUES (2, 1, 2); INSERT INTO `persmaterial` VALUES (3, 2, 1); INSERT INTO `persmaterial` VALUES (5, 3, 2); -- -------------------------------------------------------- -- -- Table structure for table `person` -- CREATE TABLE `person` ( `persid` int(11) NOT NULL auto_increment, `name` varchar(30) NOT NULL default '', PRIMARY KEY (`persid`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ; -- -- Dumping data for table `person` -- INSERT INTO `person` VALUES (1, 'john'); INSERT INTO `person` VALUES (2, 'emily'); INSERT INTO `person` VALUES (3, 'liz');
Simple Sql Question: Using A Query Result As A Query Variable
EDIT: it works now, I had an error in my code, not my method. I have a very simple question. I have 2 tables: 'users' and 'posts' with the following structure: users: id, username, email_address posts: id, user_id, post_title, post_text in a my own mind's mysql, I would like to: SELECT posts.id, posts.user_id, posts.post_title, posts.post_text users.username FROM users, posts WHERE posts.user_id = users.id I usually do one query for the post data, and then, based on the use_id record, do another of the users table, but today, I'm being forced to do them in one swoop.
Query Based On Results Of A Previous Query
So far I have managed to construct one query which gives me all individuals that have one of three titles. based on this I now want to find all the individuals that are affiliated to those listed in the first query ....
Big Query - Query Not Completely Stored In Memory
I have this query and when executed in mySQL query window throws error that "Big Query - Query not completely stored in memory". Also executed thru app, the program justs hangs. I have checked all indexes, they all look good.....
Pagination W/1 Query + How To Use Query With Indexes
i'm asking 2 questions in 1 thread because i don't wanna take up too much room, hopefully no one will mind. i have mysql 4.1.10 1) i want to find all the rows that were edited this month. the query i currently have ( MONTH(CURDATE()) = MONTH(date) ) doesn't use indexes. how can i manipulate it so i can take advantage of indexes. 2) this is something i've always wondered, but usually just assumed was not possible. if i am listing some results, say 20 per page, how can i get both the total number of results as well as the 20 items required for that specific page. say there are 2 million total results, so grabbing them all and showing just 20 is not an option. if this is not possible what is the most efficient way of making both queries?
Reusing A Query Output In The Same Query
I am guessing a basic question but not one I can find an obvious answer to. If I create a calculated or modified column in a query (such as a modified text string), and then want to reuse that in the same query as I need to do three or four operations on it in sequence, how do I do it in mySQL 4.1? Do I need to create a new column to store the interim result in an existing table (and then clear or alter it each time I run the query), or create a temporary table, or is there an easy way to reuse the query output in the same query (does the query have a name like a table name)? If it requires a new column or table, are there particular disciplines to ensure it is robust and self maintaining?
Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop. $sql = "SELECT * FROM mytable order by points desc limit 10"; $rec = mysql_query($sql) or die(mysql_error()); $datas = mysql_fetch_array($rec); do{ $sq = "Select * from secondtable where linkid = '$datas[id]'"; $rst = mysql_query($sq) or die(mysql_error()); $rows = mysql_fetch_array($rst); echo "$rows[somefield]"; }while($datas= mysql_fetch_array($rec)); This works perfectly but I want the second query to be out of the loop if there is a way and how.
Grab 'title' From The Table 'forum' Within The Same Query (was "Help With Query")
I have the following query for my vBulletin database: PHP $get_stats_newthreads = $db->query_read(" SELECT thread.forumid, thread.postuserid, thread.postusername, thread.threadid, thread.title, thread.lastpost, thread.forumid, thread.replycount, thread.lastposter, thread.dateline, thread.iconid, thread.views, IF(views<=replycount, replycount+1, views) AS views, thread.visible FROM " . TABLE_PREFIX . "thread AS thread WHERE NOT ISNULL(thread.threadid) AND $weekold<lastpost AND thread.visible!=0 AND (forumid=34 OR forumid=7 OR forumid=8 OR forumid=11 OR forumid=10) ORDER BY rand() DESC LIMIT 5"); and would like to grab 'title' from the table 'forum' where forum.forumid=thread.forumid
Using A Query Result In Another Query
Can I use the results of a SELECT query as a "table" in another query? I want to let my user pick a subset of the data, then refine it further. So ... do I have to repeat all the selection criteria at each step, or can I just do refer to the last query result? If so, what's the PHP syntax for this?
Run A Query On The Results Of A Query?
Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
Query Inside Query?
I would like to know if its practical to Insert a Select statement inside a previous select statements (array). With that said, the 'inner' select having a WHERE statement thats dependant upon the array results...
Rewriting A Query Without A Sub Query
I've recently changed hosts and found that some of my code broke. The new host is using mysql version 4.0.25 which does not support sub-queries (and they won't upgrade). I'm trying to figure out how to rewrite the following query so it will work on 4.0.25 but not getting anywhere.....
Is It Possible To Run A Query On The Results Of A Query?
I have been trying to figure this out but no luck. Lets say I have a query that searches for people living in Colorado. That results in a list on a "results.php" page. Now I want to query that result and search further for people who use Linux. I know I can do this from one query, but I would like to create several checkboxes on my results.php page where I do a further secondary query. Possible? I guess I want to query a query.
Query 2 Tables For Query
I have 3 tables, products, order_log, and groups. Products is a list of products available, groups are groups that products are put in, and order_log is a log of the current products in an order. I need to sort the order_log by the group the products are in. order_log does not have a group_id in it, however products does. So: SELECT * FROM order_log WHERE product's group_id = 1.
Query From Query Results
I have a report I'm working on that is sort of like google adsense, where it tracks ad clicks and views. I need to provide the option for the user to narrow down the results by date. I have my query worked out, but would it be better for me to run the query again and add the date information to the query, or should I cache the results and then query them? If I were to cache the results and then run queries off that, what would be the advantages or disadvantages? I also have no idea how to go about doing that. Should I create temporary tables to hold the queried information or is there another way?
|
TRACKER
