Query To Select Only Groups That Have More Than 3 Members
I need one query that selects items from table that have 3 or more associated rows in another table.
Eg. You may have a table of products and a table of product reviews. How would you select only the products that have 3 or more reviews?
View Complete Forum Thread with Replies
Related Forum Messages:
Groups For Members
My client wants GROUPS for his members, what I am thinking is creating a table for each group - since each group has a leader ID number and member list. Would this be a good way to do it?.
View Replies !
Select First X -groups- Of Rows
What I've got is 3 tables: news, category, newscategory. newscategory is just a linker table, so the relationship is like this: [news.newsid] <--> [newscategory.newsid][newscategory.categoryid] <--> [category.categoryid] One entry in 'news' can be attached to many different categories via 'newscategory'. What I want to do is return the first X news posts from 'news', along with all the categories that each post belongs to. I can't figure out how to do this: using an INNER JOIN there will be multiple rows for each news post as there will be multiple categories associated with each post, (one row per category per post) but the LIMIT 0, X clause will apply to all the rows,
View Replies !
Groups
I have: col1col2 item13 item15 item27 item23 item14 item34 I'm looking for the result: col1col2 item15 item27 item34 --the highest col2 value for data paired with col1 --no duplicates in col1 --order does not matter for either col1 or col2 in the result The SQL I have now is: select col1, col2 from table1 group by col1 I'm not sure how to force it to group col1 using the highest value from col2. Also, I am on MySQL 4.0 so subqueries are out. I'm not sure if this is relevant, but table1 is made by joining two other tables together (one of which is a heap).
View Replies !
Group Of Groups
Basically, I have a simple group query... SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID` The query returns the number of records for each student. What I want is a query that produces a count of the number of students with a distinct number of records, that is, I want to group by COUNT(*) and return the COUNT(*) of the new group. My nieve atmysqlt... SELECT `StudentID`, COUNT(*) FROM `StudentHistoryT` GROUP BY `StudentID` GROUP BY COUNT(*)
View Replies !
Find Members
I have 3 tables: members, groups, and group_converge. Members contains a userid and some information I need, groups contains the groupid and name, and group_converge tells me which members are in which groups using a memberid and a groupid. I'm trying to generate a list of people who are able to be put in X group. I can do this using a loop, by first getting a list of members, then checking which ones are in the group and eliminating them, but that involves more queries than I want to use. I know there's a way to narrow this down to one query.
View Replies !
Tables For Members
Should i use a table for each of the first letter of there username 27 for alphas and 1 table for numeric or just use one table for the usernames?
View Replies !
Trying To Create A Top Ten Members
I have created my own forum but wish to have a top ten list of members who have posted the most on my home page. i have a field called 'username' which i want to count and then work out which member shows the most and then have it shown first then each in turn to the one who has posted the least till i have either all members shown if less then ten or just the first ten. can this be done using mysql coding or would i have to use the 'username' from the members signup database table and find each member in turn and count the number of posts made and then sort this list to get the list i need?
View Replies !
X Members Online
i am making a dynamic site which shows .................. x guests online y members online total no: i have made x guests online by tracking the distinct ip address but i am unable to make the how many members online.
View Replies !
IPB Members Combining
I have an old forum that I want to transfer the users over to my new forum which has 140 users theres about 200 more in th eold database, how would I go about combining the 2....so that it updates the user id etc
View Replies !
Group With Most Members?
The members of my site can create groups. I have sored them in the DB with the members id and the groups id. How can I find the group with the most members? In the case below I would like to return group_id 1. id |userid |group_id |status |joindate ------------------------------------ 1***|1****|1*******|1*****|1180472138 2***|1****|34******|1*****|1180710824 3***|59***|1*******|1*****|1180661614 4***|1****|33******|1*****|1180661614 5***|1****|25******|1*****|1180627931 6***|1****|24******|1*****|1180627804 CREATE TABLE `my_group` ( `id` int(10) unsigned NOT NULL auto_increment, `userid` int(10) unsigned NOT NULL default Ɔ', `group_id` smallint(6) NOT NULL default Ɔ', `status` tinyint(1) unsigned NOT NULL default Ɔ', `joindate` int(10) unsigned NOT NULL default Ɔ', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ; -- -- Dumping data for table `my_group` -- INSERT INTO `my_group` VALUES (1, 1, 1, 1, 1180472138); INSERT INTO `my_group` VALUES (25, 1, 34, 1, 1180710824); INSERT INTO `my_group` VALUES (26, 59, 1, 1, 1180661614); INSERT INTO `my_group` VALUES (24, 1, 33, 1, 1180661614); INSERT INTO `my_group` VALUES (16, 1, 25, 1, 1180627931); INSERT INTO `my_group` VALUES (15, 1, 24, 1, 1180627804);
View Replies !
Order By Giving Two Groups
I have a new database table which contains name and url field. When I order by name I get two groups, the first being those without an entry in the url field, the second with an entry in the url field, each grou seperately ordered correctly. In a possibly related problem with the same table displaying in a web browser through php, there are again two groups diplaying when ordered by name. If a member of the first group is displayed and resubmitted without change, it then appears in the second group. I tried doing an export / import to refresh the data, but it didn't help. Any ideas for causes and or solutions?
View Replies !
Selecting Members, Mysql
I want to select the users from 'userTBL' which does not have any property in 'live_properties' table, Both table has a same column called 'username' I have 2 tables called 'usersTBL' and 'live_properties' user info stored in 'usersTBL' and their properties stored in 'live_properties'
View Replies !
Two Servers On Windows And My.cnf/my.ini Groups
I run two MySQL servers on Windows with the two services. I read in the docs that it exists a way to identify the servers in my.cnf by groups and about a tool from Linux to manage the groups. How can I start a server on Windows through the my.cnf/my.ini 's groups?
View Replies !
Return Groups For Each Month
I would like to return monthly reports with a single query.For example: January 15 Purchases 5 Refunds 6 Exchanges February 20 Purchases 4 Refunds 2 Exchanges The above data has about 60 records. The query has to group by month and then again by transaction type. Here is what the record would look like TransactionID - primary key TransactionDate - Date Transactiontype - integer
View Replies !
Counting And Ranking Within Groups
(commas are in place to show data seperation and are not needed in the result set) Name Won Julio, 2 julio, 3 julio, 0 Ron, 4 Ron, 2 and the results should be based upon wins sorted descending Name Won Rank Julio, 3, 1 julio, 2, 2 julio, 0, 3 Ron, 4, 1 Ron, 2, 2
View Replies !
How To Find Out Values Shared By Some Members
I have a table contains words extracted from documents which are belonging to a number of categories. Each category contains a number of documents. For each category, I want to find out those words which occur at least in N documents belonging to this category. Code:....
View Replies !
Linking Two MySql Members Tables To = One
I have set up a forum as well as user auth into the heart of my site - however - I am still new to creating systems such as I have been working on and did not consider a repetitive user authentication system - I have two login forms - one for the forum on one database (in its own table) and one for the site itself on another database (in its own table) - Is there a way I can link the main site's user auth - to that of the forum auth and use only one members login and registration database for the whole site - instead of having multiple logins through out the site???
View Replies !
Using MySQL For A Simple List Of Members
I have a website which has an admin section which can easily be customised to insert things into the mySQL database, but i can't really use mySQL in the pages What i need is a php script that can see how many entries there are for 'name', 'picture_url' and 'comment' (i have phpmyadmin but dont know what to insert so taht i can hold these values) then the php script needs to insert each value for 'name', 'picture_url' and 'comment' into a seperate arrays by that i mean the array name_list may contan ('John', 'Fred')
View Replies !
Using A Single Sql Statement To Group And Count The Groups
I'm trying to count the total number of items in a table that are grouped e.g. my table is a shopping basket and is like this: basketID||orderID||productID||quantity 1||1||2||1 2||1||3||1 3||1||4||1 4||2||2||1 5||2||4||1 6||3||4||1 so product 4 appears 3 times, product 2 appears twice and product 3 appears just once how do use an sql statement (if it is possible) to group the products then list them in the order of which appears most so i could say SELECT * FROM basket GROUP BY productID; and that would group them for me, but i want them listed like productID 4 2 3 (as 4 has the most occurances, then 2, then 3)
View Replies !
Invision - Moving Members To Validating Table
I am using Invision 2.0.3 and i need help with a query which will move all the members from the members table to the validating table in the database. Reason i want to do it is so i can resend the activation email to all members so they have to reactivate their account. I tried using this query: UPDATE ibf_members SET mgroup = 1 WHERE mgroup = 3 This only moves their group and changes their permissions, it doesnt actaully put them into the validating table. The validating group uses different fields than the members group so i dont know how to insert all my members into it.
View Replies !
Organizing Members By Votes (seperate Table)
Got another DB question for you... still getting the hang of more complex DB queries. I have a table of: MEMBERS: userid, username, password, etc. VOTES: userid, voterid Please note that the votes table can have multiple votes per user, etc. So what I want to do is, list my members in order of how many votes they have in the VOTES table.
View Replies !
Counting Members With 0 Posts And 0 Comments In My Blog
I really need help with a counting query, as my sql is a bit rusty... I have a user table with thousands of users, most of which are spam signups that I want to get rid of. So I have decided that I will delete all users that have been registered for at least 3 months and have not posted or commented on anything in the blog. Users can have posts, since the blog accepts submissions, and you don't need to be a registered user to post comments either..
View Replies !
Total Members Joined Today, Yesterday, Etc
I have a field called regtime in my user table, when a user joins it enters time() into that field. I want to code a page which loops the performance of new user registrations for the last 30 days. I already coded this to show the people logged on in the last 24h: PHP Code: $24honQ = mysql_query("SELECT COUNT(id) FROM usertable WHERE lastactive>='".(time()-86400)."'"); What would be the best way to query this table for the "regtime" field, and code an output to show how many people registered on each day, for say 30 days
View Replies !
How To Use Previous Select Query Results In New Select Query?
I have tested everything and it works OK in its current form. However, what I need help with is the part of the query highlighted in red. As it currently stands, the PHP while statement loops through the rows and echos out the html. The issue I have is that it echos out the same speaker name and subject type for each iteration. Now I know this is happening because I have set subject.event_id = 1, so what I need is to use the event_id of the current iteration as the clause and that is what I need help with....
View Replies !
Select Within Select Needed To Get This Query Going?
I'm not sure how to go about this, but I am thinking I need some sort of a select query within a select query to get this properly displayed... My table structure is as follows (simplified, of course): payment_id | member_id | completed | amount | trans_added and I am trying to get the following data out of the table - a daily sum of all the transactions that were 1) completed, 2) by a member who already has an existing completed transaction (completed = 1) on a different date (or members with a count of all completed transactions > 1 will be included only). so far I've got this query, but I don't know where/how to plug in the condition for the members/completed/etc: .....
View Replies !
Order Per "Group By" Groups
a table example: Code: ID | USER | VALUE | 1 | mike | 8 2 | mike | 10 3 |mike | 12 4 | john | 10 5 | john | 12 6 | john | 15 Desired result grouped by USER: ID | USER | VALUE | 6 | john | 15 3 | mike | 12 as you can see, I'm ordering the group result by the last value of ID column for each user, then order by user name. In other words, I need to retrieve the max ID value for each user. also order the result by user name. How can I do that ? Myabe there's no need of grouping,
View Replies !
SELECT Query.
Table 1 3 #1,#2 2 5 #2,#3 3 3 #1,#2,#3 4 4 #4 5 5 #1,#2,#3 6 5 #1,#2,#3 7 4 All 8 3 #1,#2,#3 Need to compare the value of field2 with the occurrences of "#" in field3. Tried this: $query_plcResultsSet = "SELECT * FROM Table WHERE RteType IN ('Mainline', 'Connector') AND StartDateD = '$strchvarval' AND (field2 = 'All' OR " . substr_count(field3, '#') . " = field2)";
View Replies !
SELECT Query With IN(...)
I have a query SELECT id, name, text FROM text WHERE id IN (11,22,1,34,56,13,21,19,3,6,9) I need result sorted as in 'IN ()'. But it is always sorted by id. How can I achieve it
View Replies !
Can't Use Select...IN Inner Query
I'm trying to make the following SQL query and keep getting errors: select * from mytbl where reportid in (select reportid from mytbl where level>60) I get this 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 'select reportid from mytbl )' at line 1 When I'm running the inner query (select reportid from mytbl where level>60) it is running OK. Also, when I'm running without inner query (select * from mytbl where reportid in (1,2,3)) itis running Ok. But Somehow I can't run it together. I'm using MySQL 4.0.14.
View Replies !
Help With SELECT Query
I am having trouble coming up with the proper SQL that will get me a certain result. i have a table that looks something like this: request_id | member_id | Club_id 1 | 10 | 12 2 | 10 | 12 3 | 10 | 142 4 | 10 | 142 5 | 10 | 72 I am trying to come up with a query that will give me the number of times that member #10 chose each different club. ex: club #12 - 2 times club #142 - 2 times club #72 - 1 time i have tried a few things but everything i have tried with DISTINCT, or COUNT gives me 5 (total requests). And it is late in the day and my brain is a bit fried.
View Replies !
Using LIKE In A Select Query -
I am trying to query 2 tables. One has a field, County_name which includes a list of counties and the state they are associated with ie. MT-Missoula The other table is a list of sites and has a State field. I would like to bring up a list from the County_names field that begin with a State that matches the value in the Site State field. I can't figure this out, can anyone help a new mysql user? $countyresults=mysql_query("SELECT ct.*, s.State, s.Site_ID FROM county_tbl AS ct, site_tbl AS s WHERE s.Site_ID='$ID' AND ct.County_name LIKE 's.State%' ORDER BY County_name");
View Replies !
Need Help With A SELECT Query
I have two tables. Table 1 = ContentWords Table 2 = TitleWords The tables have both each two fields named wordId and articleId. The wordId field references a wordId in a table named Words which contains the fields wordId and word. As you might understand I use these tables (ContentWords and TitleWords) to index words that appear in articles to use with a search function. It's easy to construct a search query to have it select a articleId from either ContentWords or TitleWord (SELECT articleId FROM ContentWords/TitleWords WHERE wordId = $var). But if I want to search if a word exists in both the Content and Title? I've constructed a SELECT query that kind off works: SELECT ContentWords.articleId AS ContentArticleId, TitleWords.articleId AS TitleArticleId FROM `ContentWords` , `TitleWords` WHERE ContentWords.wordId = TitleWords.wordId AND ContentWords.wordId = $var AND TitleWords.wordId = $var This will output something like: ContentArticleId TitleArticleId 53 76 77 76 That means the word exists in the content in article 53 and 77. The word also exists in the Title in article 76. What I actually want is only ONE field named ArticleId with the result of 3 rows (53,76 and 77). Is this possible? Thanks in advance!
View Replies !
Select Query Help Please
The below query works well for returning 1 result however I now need to expand it to return multiple results. At the moment I feed it 1 car type and 1 company id and it spits out one result, I then loop the query to get the number of results I need - but this is very inefficiant so I want to change it so I pass all my variables at once. So where I have cars.type =Ƈ' I want to have car.type =Ƈ' or cars.type=ƈ' which in itself works but how to I extend the company section as when I add the extra 'Or's' in there the query goes wacky and spits out hundreds of results which are all the same - I think its something to do with the inner join but im not sure. MySQL SELECT prices.single_f, prices.return_f, prices.vehicle, resort.company_id, resort.resort, cars.type, cars.description, cars.long_description, cars.image, surcharges.type, surcharges.adult, surcharges.kids, prices.timefrom, prices.timeto, prices.resort_id, cars.min, cars.max, company.is_enabled FROM prices INNER JOIN resort ON resort.id = prices.resort_id AND resort.company_id = Ƈ' AND resort.resort = 'Acoteias' INNER JOIN company ON company.id = Ƈ' INNER JOIN cars ON cars.type = prices.vehicle AND cars.type = ƈ' LEFT JOIN surcharges ON surcharges.resort_id = prices.resort_id WHERE prices.break <= ďb' AND company.is_enabled = 'Yes' AND ( ( prices.timefrom <= prices.timeto AND prices.timefrom <= ཁ:00:00' AND prices.timeto >= ཁ:00:00' ) OR ( prices.timefrom >= prices.timeto AND prices.timefrom <= ཁ:00:00' AND ན:59:59' >= ཁ:00:00' ) OR ( prices.timefrom >= prices.timeto AND ༼:00:00' <= ཁ:00:00' AND prices.timeto >= ཁ:00:00' ) ) LIMIT 0 , 30
View Replies !
Mysqldump Select Query
I am trying to do a dump for a 2 gig table... I would like to dump the tables in portions so that it's a little easier to work with... Can somebody suggest a way to do a mysqldump table query ie.. mysqldump -u user -p database="MYDATABASE" Query="SELECT * FROM TABLE WHERE State='NV';" I'm pretty sure this is possible... I have no idea how to do it.
View Replies !
IN Clause For Select Query
Can someone help with query syntax regarding IN/EXISTS.. I'm trying to do this: insert into table2 (field1) select field1 from table1 where field1 not in (select field1 from table2) delete from table1 where field1 in (select field1 from table2) 1. Insert field1 from table1 into table2 if it doesn't already exist there... 2. Once moved, delete from table1.
View Replies !
Select Last_insert_id Query...
I have a site with various parts which allow users to enter info via forms which gets entered into various tables in a MySQL db (I'm using ASP, rather than PHP). I have an ecards bit, and when users send a card, their data is entered into the db via something like: INSERT vars into ecard_tbl.... I just read that I can use this to get the most recent id from the autoincrement field in the table: SELECT LAST_INSERT_ID However, it doesn't seem to need a table name for that. What if exactly at the same time another users enters something into my guestbook. That will submit data to the same MySQL db, so won't the 'SELECT LAST_INSERT_ID' give me that ID, and not the ID from the card submission? Am I missing something very obvious here? I checked the MySQL docs but can't see anything obvious.
View Replies !
Add Time Using A Select Query
Hello, how can I add, say, 2 hrs from a time in a database using a select query. I have searched the forums but cannot find an answer. Is it possible PHP Code: select date, `time` as timetime , time_format(`time','%l:%i %p') as Printtime, client, address from..........
View Replies !
Double Select Query
i've got 2 tables tb_orders --------------- id_orders description 1 client1 2 client2 tb_reminders --------------- id_reminders id_orders reminders_time 1 1 2005-02-18 18:30 2 2 2005-02-18 15:30 the user inserts the reminders time for the specified order. i have to select the orders that have the last reminder < today ordered by reminders_time desc. thanks
View Replies !
INSERT SELECT In One Query
I keep getting an error with this query: Insert into new_voter (salutation, firstname, lastname, electoral_district, poll_number, birthdate, gender, occupation, party_affiliation, ph_home, email) values select (txtSalutation, firstname, lastname, txtElectDistrict, txtVotingArea, dtBirth, txtGender, txtOccupation, nPartyId, txtPhoneDay, email) from person Can someone spot the error (presumably with the syntax).
View Replies !
SELECT Query Optimisation
I have the a query - rather large - as show in the attached JPG. As you can see, the optimisation is not ideal as it is "using where, using temporary, using filesort" for the first row of the EXPLAIN. The query takes around 8 seconds to execute.
View Replies !
Select Query Using Limit
i have the basic sql query as follows: $sql = "select * from photo where CategoryId='$idc' order by Id limit $start, 9"; this will select all rows from db photo based on CategoryId, then stop after it gets the 9 beginning at $start right? start is the value of the ID i want to start the select from. in this example, i have valid db values ranging from id: 11-20. the 1st page that runs this query has start=11, and displays for 11-19. when i goto the next page setting start=20, i get nothing. Limit returns 9 rows starting at $start right? or am i misunderstanding how all this works?
View Replies !
IF Statement Within A Select Query?
If there a way to do an IF statement within a MySQL select query? I have this query that gets a vendor's code and name from the database and CONCAT it to put the code into "()" and add the vendor name to the end. Is there a way to write it so the "()" will not be included if the vendor does not have a code set.
View Replies !
MYSQL Select Query
I am new to MYSQL.I am stuck with building a MYSQL Query. I have two tables X and Y Table x column1 Id Table Y Column1 column2 ID Monday-Sunday i need the ids which has saturday and sunday in the table Y.
View Replies !
Select Distinct Query
I have a table in my db with two fields, one called link and the other shop. I have another table called link_partners. This latter table has an id column that matches the link column in the former table. I am trying to run a sql statement on the two tables and am able to do so until I try to just show the records in the link_partners table with distinct category column value. Here is my sql Code: mySQL="select DISTINCT category from link_partners inner join link_partners on link_partners.id = links.link where links.shop = 77"
View Replies !
|
TRACKER
