Trying To Pull Id, Count And Title But Lose Rows When I Add Extra Join
i'm trying to extract some information from my database, the query being
PHP
SELECT grps_c.catid, grps_c.title, COUNT(grps.groupid) AS COUNT
FROM grps
RIGHT JOIN grps_category grps_c ON (grps_c.catid = grps.catid)
GROUP BY grps_c.catid
ORDER BY grps_c.title
which works fine. however some of the groups (grps.groupid) are hidden and i don't want to count them, so my thinking was add
PHP
LEFT JOIN grps_setting grps_s ON (grps_s.groupid = grps.groupid AND grps_s.hidden_group != Ƈ')
however adding that removes the rows that have a 'count' or NULL or Zero.
View Complete Forum Thread with Replies
Related Forum Messages:
LEFT JOIN Produces Extra Rows
The `suggestions` table contains 2,265 rows. The `ALL_PHS_SCHEDULES` table contains over 22,000 rows. I have done my homework and I read that the Left Join return ALL rows from the first table and the matching ones from the other table. I am expecting exactly 2,265 but the query is returning 2,734 rows. Quote: SELECT * FROM `suggestions` LEFT JOIN `ALL_PHS_SCHEDULES` ON `suggestions`.course_number = `ALL_PHS_SCHEDULES`.course_number AND `suggestions`.section = `ALL_PHS_SCHEDULES`.section AND `suggestions`.id = `ALL_PHS_SCHEDULES`.id ORDER BY `ALL_PHS_SCHEDULES`.`course_number` , `ALL_PHS_SCHEDULES`.section ASC
View Replies !
OUTER JOIN With Extra Conditions?
I need to get a list of products in a certain category, along with the quantity of each item already added to the shopping cart for a given session number. I'm having a heck of a time satisfying the latter condition. shoppingCart table: +--------+-----------+-----------+-----+ | cartID | sessionID | productID | qty | +--------+-----------+-----------+-----+ product table: +-----------+--------------+------+--------+ | ProductID | ProductCatID | name | Active | +-----------+--------------+------+--------+ Here's my base query, which just gets all the products in a given category. SELECT product.* FROM product WHERE `ProductCatID`='{$id}' AND `Active`=Ƈ' Here's the query I've got so far: SELECT product.*, shoppingCart.qty FROM product LEFT OUTER JOIN shoppingCart ON shoppingCart.productID = product.ProductID WHERE `ProductCatID` = Ɗ' AND shoppingCart.sessionID = '{$sessionID}' AND `Active` = Ƈ' Obviously, this does NOT work, because it limits the query to ONLY products that have the specified sessionID. I need it to return ALL the products in the category, but give me the quanity for items in the shoppingCart table, ONLY IF the sessionID matches (otherwise it should return NULL)!
View Replies !
Any Way To Optimize Join To Find Rows Without Conditional Foreign Rows?
I've got a table of keywords that I regularly refresh against a remote search API, and I have another table that gets a row each each time I refresh one of the keywords. I use this table to block multiple processes from stepping on each other and refreshing the same keyword, as well as stat collection. So when I spin up my program, it queries for all the keywords that don't have a request currently in process, and don't have a successful one within the last 15 mins, or whatever the interval is. All was working fine for awhile, but now the keywords_requests table has almost 2 million rows in it and things are bogging down badly. I've got indexes on almost every column in the keywords_requests table, but to no avail. I'm logging slow queries and this one is taking forever, as you can see. What can I do? # Query_time: 20 Lock_time: 0 Rows_sent: 568 Rows_examined: 1826718 .....
View Replies !
COUNT With JOIN
How can I combine these two quries into one query? These queries both work - so there is no problem with getting data from the database. I would simply like to combine this into one query with out doing a sub-query. When I try to combine it, I must use a GROUP and this effort is something I can not do and retain the results to be correct. SELECT DISTINCT A.EX_ID,B.TITLE,B.SUMARY FROM EX_KEYWORD AS A LEFT JOIN EX_EXAMPLE AS B ON A.EX_ID=B.EX_ID WHERE A.KEYWORD LIKE '%something%' ORDER BY A.EX_ID DESC LIMIT 45,10 SELECT COUNT(DISTINCT EX_ID) AS count FROM EX_KEYWORD WHERE KEYWORD LIKE '%something%'
View Replies !
Using MAX() And COUNT() With Join
I'm a bit unclear on joins still and would like to figure out a way to merge two queries into one, if it's possible. I'm wondering how to use the function count() and max() in the same query. I can currently do the queries seperately like this: A) The first is a frequency count which shows the frequency count() of each value of the column dc_subject:
View Replies !
Count With Inner Join
I have 2 tables that I need to Inner Join and then count how many duplicates of a record I have. Here is the set up: Table 1 Username / Post ------------------ user1 / POST1 user1 / POST2 user4 / POST3 user5 / POST4 user4 / POST5 Table 2 Username / Reply ------------------ user2 / REPLY1 user2 / REPLY2 user3 / REPLY3 user1 / REPLY4 user1 / REPLY5 I am trying to count how many posts and replies each username has. Such as: user1 = 4 user2 = 2 user3 = 1 etc... I am stuck on this. I have researched using google and such, found some example, but cant seem to get them implemented correctly. Here is was I have so far, but it is coming out totally wrong. There are at least 3 usernames listed between the 2 tables and it is only displaying 1 with a wrong count. What am I doing wrong? Code:
View Replies !
Count Rows For Each Hour The Last 24 Hours, Including The Hours With Zero Rows
I have a table for logging log-in's with these columns: id, datetime, name. I want to see all the logins the last 24 hours for a specific name. I want mysql to return one row per hour (24 rows) and how many logins this name has done in these hours. Example result: 2009-02-13 00:00 2 2009-02-13 01:00 0 2009-02-13 02:00 0 2009-02-13 03:00 1 2009-02-13 04:00 0 2009-02-13 05:00 3 2009-02-13 06:00 5 2009-02-13 07:00 0 etc... 2009-02-13 23:00 7 I made this query: ......
View Replies !
Left Join With Count
I'm just getting a big mental block when trying to write this query. DB SCHEMA SUPPLIERS supplierID, name, website PRODUCTS code, supplierID, name, photo Products belong to suppliers, supplier may have none, one or many products. (1:M) I need a query to produce ALL suppliers, and a count of products where the photo field is empty (it's varchar). I can do the LEFT JOIN to list all suppliers and a product, but I don't know how to do the count WHERE p.photo = '' Each supplier should appear exactly once in the result set.
View Replies !
Count The Rows
I've built quite a few large join statements, which cross-reference 6 or 7 tables each. I'm using LIMIT to allow for pagination, which works fine. However, I need to find out how many rows were returned in total, and don't fancy throwing in another join to count() the results with some where statements. Anyway I can quickly find out what would've been returned without the LIMIT?
View Replies !
Count(*) Sum Of Rows
i have several columns, with hundreds of rows of numerical data in the db. i'm wanting to set it up to where when i SELECT the data from the data base it doesn't show me hundreds of rows of numbers. I want it to add up the data for each column and display their totals only. I've tried using count(*) and * , count(*) as times, and i keep getting errors. what do I need to do here's the code: MySQL Code: $info_query = mysql_query("SELECT * FROM Report_Listing_Daily WHERE listing_id = '$listing_id' AND MONTH(day) = '$month' AND YEAR(day) = '$year'");while ($row = mysql_fetch_array($info_query)) {echo "Summary: ".$row['times'];echo "Detail: ".$row['detail_view'];echo "Click Through: ".$row['click_thru'];echo "Email: ".$row['email_sent'];echo "Phone: ".$row['phone_view'];echo "Fax: ".$row['fax_view'];}
View Replies !
For Efficiency: Increment Count Or JOIN?
Question of efficiency: I have a table named 'articles' which is listings of written articles or stories, and a 'comments' table which hold all the comments posted for the articles. I want to have PHP pages showing articles sorted by most commented in descending order. Should I have a comment_count field in the 'articles' table which increments +1 each time a comment is posted for a particular article (requiring UPDATE and INSERT query each time a comment is posted), OR should I do some sort of JOIN or UNION of the two tables?
View Replies !
Outer Join Count Query
I want to create an outer join along with usage of group by & count... So, I want the rows with count=0 to be part of the result... Select a.area, count(l.id) as no_listing from area a left join listing l on a.area=l.area group by a.area order by a.area; Note that an area can have multiple listings... Now, this query returns me only the areas with some positive count of listings, in spite of using a left join...
View Replies !
LEFT JOIN And COUNT When There Is A WHERE Clause
I have two tables categories id category_name 1 books 2 movies items id category_id item_name in_stock 1 1 da vinci code 1 1 1 blink 1 1 2 changeling 0 I want a query that returns the total number of books and movies that are in stock (stock = 1). so I would like the query to return c.id c.category_name cnt 1 books 2 2 movies 0 The query: SELECT c.id, c.category_name, count(i.id) AS cnt FROM categories AS c LEFT JOIN items as i ON (c.id = i.category_id) WHERE in_stock > 0 GROUP BY c.id; doesn't return the category 'movies'. it returns: c.id c.category_name cnt 1 books 2 How do I modify the query so that it returns rows with a count of zero?
View Replies !
Where Title IN
In my where condition i am using Code: where topic = 12 and (title like '%dubai%' OR title like '%uae%') but i know that using OR is somethign which is not good for performance becouse it willl scan all the records in the table irrespetive of other conditions specified. so instead of (title like '%dubai%' OR title like '%uae%') i want to use title IN('%dubai%','%uae%') type feature, is it possible ...?
View Replies !
Count Identical Rows
I've got a log table looking like this:Code: user | action | time | | a | click | 2005-05-05 a | login | 2005-04-05 b | logout | 2005-03-02 b | click | 2005-01-25 And now I wanna display the statistics in a neat interface. But I'm having problems... is there a way to select from the above table and get rows like this:Code: user | click | login | logout | | | a | 56 | 23 | 45 b | 45 | 2 | 0 c | 12 | 32 | 32 Perhaps this is more than I could expect from MySQL?
View Replies !
To Count All Rows With The Same Value On One Column
I have a table that contains a few thousand rows and what I want to pull back is the number of rows that each have the same number in one column. Basically, pull back all rows and then display a total for each seminar happening. I can join this table to tx_seminars_seminars that has a list of all seminars available but I can't work out how to get a total for each different seminar happening... My SQL is: SELECT COUNT(*) FROM tx_seminars_attendances AS seatCount WHERE seatCount.seminar=NEEDS TO PULL A LIST OFALL SEMINARS IN ONE BY ONE AND seatCount.deleted<>1 AND seatCount.user<>3991 AND seatCount.user<>3992 AND seatCount.user<>4006 AND seatCount.dateDeclined IS NULL AND seatCount.dateRemovalApproved IS NULL AND seatCount.dateCancelled IS NULL AND seatCount.dateAuthAbsence IS NULL AND dateSubmitted IS NOT NULL
View Replies !
Count Same Field In Rows
i need to calculate the number of the same IP address that appear in the database, how can i do that? example of the database: IP Address Date and Time 127.0.0.1 5/23/2007 127.0.0.1 5/24/2007 192.168.1.2 5/25/2007 192.168.1.5 5/26/2007 127.0.0.1 5/27/2007
View Replies !
Efficient Way To Count Rows
I'm trying to get the number of rows in a table with a very large number of records in it (~9 million). When I run a select count(*) for some criteria (where name='something', etc) it takes around 6-8 secs for the query to return the value. I tried by using SQL_CALC_FOUND_ROWS with a very small LIMIT but then the query was taking even longer. I'm using InnoDB, with query caching enabled. I could look at the information_schema and get the approximate row count but whenever I use a where clause it'll be way off mark.
View Replies !
Count Multiple Rows
I have a table that tracks dealer transactions. The fields are Date, Dealership, Amount, A, D, W, F. A=Approved D=Denied, W=Withdrawn and F=Funded. I have made it where if a loan has been approved then A would =1 and D W F would be null. Same goes if the record was withdrawn W would =1 and the other would be null. So here is my problem: I want to group by dealership and then count how many were approved, denied, withdrawn, and funded. i am running version 3.23.58. After doing much research I either need to do unions or subqueries. However, both are not availble until 4.x. Is it possible to do what I am looking to do without upgrading?
View Replies !
Count() Rows With 0 Matches
I am using the code below to select all the 'centres' in the database that have 'sessions' today. SELECT c.*, r.region, COUNT(*) AS count FROM centres c, regions r, sessions st WHERE c.c_id=st.c_id AND st.session_date=CURDATE() AND c.region_id=r.region_id This works and the results might look like: centre / count centre1 / 20 centre2 / 10 centre3 / 3 However, is there a way to get MySQL to also select centres from the database that have no matches in the sessions table? So the results will look like this: centre / count centre1 / 20 centre2 / 10 centre3 / 3 centre4 / 0 centre5 / 0 centre6 / 0
View Replies !
Count Similar Rows
I have a table where every search made on a site is logged. The table columns are id, text, uid, time and ip, text being the string of text that was searched. I need to take the text column and somehow conclude what the most popular searches are. Does anyone know an efficient way to do this? Obviously, I could just select all the rows and use php to figure out the most popular searches, but this doesn't seem very efficient.
View Replies !
Count Distinct Rows
If I have a table like this: col1 col2 1 100 2 100 3 300 4 200 5 100 And run a query like this (dont know if it is right syntax): SELECT DISTINCT(col2) * FROM Table I would get something like this: 1 100 3 300 4 200 But I would like to get the count of each distinct row like this: 1 100 3 3 300 1 4 200 1
View Replies !
Join Tables To Count Number Of Records?
I've two tables: responses ========== response_id schema_id timestamp answers ========== answer_id response_id answer (that's a cut down version, but will do for this question) I need to get all the responses where there is at least one answer in the answers table. But I do not want the answer data. It's literally a quick check for an export to say "get me all the responses where there's at least one question answered". I have this: Code:
View Replies !
Title Filed
I have a "news" table with 300,000 Records in Arabic (UTF-8 format). i also have a searching mechanism in my website . so my question is i presently applied indexing in the Title field. is there is any problem on the processing time of the query if number of records is more and if that have index . My table size is 1.6 GB, is this size ok or not..? I have plans for archiving and keeping less records in the main table but still i wanted to know that 1.6 GB Size have any problem in MySQL query processing time
View Replies !
Count Fields Over Multiple Rows
I have a database like this id, field1,field2,field3,field4,field5 Database contains 100 rows, some rows have no fields filled, some 1field , some 2 fields etc. How would i count the number of fields filled in total? So the outcome is (number of fields filled in row1)+(number of fields filled in row2)+(number of fields filled in row3)....................+(number of fields filled in row100)
View Replies !
Count How Many Matching Rows Exsist
I am new to SQL Querries, can someone help me figure out how to get a result? I have a column in my db called; emails some of the email addresses in this column may contain similar domains: person1@site.com person2@site.com person3@someothersite.net I need the result to count and end up being 2 not 3 Not sure how to make the sql statement: COUNT (*) FROM database_tabe WHERE email='$email' LIKE '%@%.%' Not sure how to get this result...
View Replies !
Slow Count(*) On Many Indexed Rows
we have a 10 million rows table. One field 'stamped' is either 'yes' or 'no'. About half the rows are 'yes' and half are 'no'. Table is indexed on 'stamped'. We just need the count on 'yes'. SELECT count(*) from T where stamped='yes' or <>'no' or >'n'... is very slow. Is there a better approach / query to get a fast result?
View Replies !
Select Count(*) Showing -1 Rows.
In one of my preliminary tests with mysql, the below statement is showing the following way. What does -1 rows signify in the table with the select count(*) query. Is this (-1 rows in the query result) an error condition to be taken care ?. bash-2.03# ./mysql -u test Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 8 to server version: 5.0.18-max Type 'help;' or 'h' for help. Type 'c' to clear the buffer. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Code:
View Replies !
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?
View Replies !
Count Number Of Rows Returned?
this is my connect and everything code: <?php // connect to the mysql database server. mysql_connect ($dbhost, $dbusername, $dbuserpass); mysql_select_db($dbname) or die(mysql_error()); $query = "SELECT DISTINCT category FROM $tuttable"; $result = mysql_query($query) or die(mysql_error()); while ($row = mysql_fetch_array($result)) { echo ($row['category']." | "); } echo count($row); ?> Notice "echo count($row);" How do i get this to work properly. It only returns one, because there is only 1 that it is working on. I want to return the count of all the rows that fit that query.
View Replies !
How To Get Count Of Rows Returned While Using Group & Having?
i'm using the having keyword to find certain set of rows and they are working properly. but what i want is , i want to count the total number of rows returned by this query using mysql. select name,count(date) from emp group by date having count(date)>3; this returned returns all the names that have more that 3 entries in the same date. it returned 5 rows and how can i get the count of rows(5) the query returned .
View Replies !
" Or Im Gonna Lose It"
Actually I have lost it 20 hours ago. Some how i changed my load Template to this name while trying to add a hyperlink to my main forums index on IPB . now I cant get into the forums at all. I have phpmyadmin but am totally lost and need to know how to change the problem text to this: forums/cache/skin_cache/cacheid_2 so it will use a different skin/template to open my forums. I need to due this because this error happens and cant access any part of my forums. And i dont want to lose all the work done. {And no i didnt back it up soon enough) IPB WARNING [2] load_template(http://surfsand.net/forums/index.php?act=home [Portal]cache/skin_cache/cacheid_3/skin_global.php): failed to open stream: HTTP request failed! HTTP/1.1 400 Bad Request (Line: 1331 of /sources/ipsclass.php) and i actually played around a bit and go this message: mySQL query error: SELECT * FROM ibf_ http://surfsand.net/forums/index.php?act=home [Portal]cache_store WHERE cs_key IN ( 'portal','attachtypes','bbcode','multimod','ranks','profilefields','rss_calendar','rss_export','components','banfilters','settings','group_cache','systemvars','skin_id_cache','forum_cache','moderators','stats','languages' ) SQL error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '://surfsand.net/forums/index.php?act=home [Portal]cache_store W SQL error code: Date: Sunday 14th of May 2006 06:38:26 PM but i changed it back to the orig error. I put this string "http://surfsand.net/forums/index.php?act=home [Portal]" in the admin cp/tools and settings/ general config/ other URL's and Paths/ Caching Path. Never knowing what a headache it would cause.
View Replies !
Title Column Names
Is there a way for me to title a colum "Some_Column_Name", but then be able to list it as "Some Column Name" in a web site? Perhaps with column comments?
View Replies !
Count Difference Between Dates In Table Rows
There's a table with event logs. Every record in this table is a code a an event and time when the event occured. What I need is to find event which occured in a longer time before following event than a specified interval of time. Example table: Event Time 3 2008-08-08 14:00:00 3 2008-08-08 14:00:20 3 2008-08-08 14:00:40 3 2008-08-08 14:21:10 Interval: 20 s Let's say it's now 14:21:20, then the event I'm looking for is on the row 3 because there's more than 20s gap between times in row 3 and 4 and row 3 is the last such row. If it was 14:22:50, then the event I'm looking for is on the row 4 because there's more than 20s gap between now and the last row and row 4 is the row with the highest date.
View Replies !
The Fastest Way To Count Rows In Innodb Tables
As count(*) doesn´t work so fast with Innodb like it works with Myisam what is the best and fastest way to count rows in Innodb? I use indexes in my Innodb tables so maybe select indexed column query with mysql_num_rows could be the fastest way or at least faster than count(*)!?
View Replies !
Limit Results And Count Total Rows?
I have my database set up with 3 tables: POSTS, TAGS, and TAGMAP. Here is an example: TABLE "posts" id title content time -- -------- ----------- ---- 12 my blog title 1 bla bla bla... 1218826136 13 my blog title 2 bla bla bla... 1218826136 14 my blog title 3 bla bla bla... 1218826136 TABLE "tags" id name -- ----- 45 cars 46 trucks 47 motorcycles TABLE "tagmap" id tag_id post_id -- ------ ------- 156 46 12 157 46 14 158 45 12 159 47 13
View Replies !
COUNT And GROUP BY Display All Counted Rows
have a table as follows ID | ORG | Subject ----------------------- 100 | 14 | Some text 101 | 18 | Text1 102 | 18 | Text3 103 | 14 | Text4 104 | 18 | More text What query would I need to get the following output? (when I use COUNT(ID) and GROUP BY ORG it only returns 1 ID per ORG) ORG 14 - 2 results 100 103 ORG 18 - 3 results 101 102 104
View Replies !
Return A Count Of Number Of Rows Before Desired Row.
Anyone know how? find_in_set doesn't seem to be able to do what I want since I am not searching through an array but am actually running a query. What I really want is to be able to select all until something = something. I've searched the realm of google but cannot come up with anything useful, and so I am wondering if maybe I don't know the right words to be looking for.
View Replies !
|
TRACKER
