Question About A Specific Query
imagine I've got user that can have visual preference (enabled or disabled) saved on a table.
Before a user make change of a preference, there is no row associate to that user and by default, the preference is enabled.
Now, is it possible to do a query that will output user that have preference set to enabled (those who have update their preference) while also outputing those who have no row in the table preference?
I know it may be not super clear, but I need to know before working on that script.
So here a sample to make sure it is all clear:
TABLE USER (id - name - email)
1 - NameA - a@a.com
2 - NameB - b@b.com
3 - NameC - c@c.com
TABLE PREFERENCE (id - label)
1 - Show warning
2 - Show help
TABLE USER_PREFERENCE (user_id - preference_id - status)
1 - 1 - Disable
2 - 1 - Enable
So if I want to output user email that show warning, it should give me:
b@b.com, c@c.com
My problem so far is, how to retreive user that have a user_preference row enabled, and user that have no user_preference row in the same query!
View Complete Forum Thread with Replies
Related Forum Messages:
Query A Specific Day, And GROUP BY
The query below, counts how many records each agent has entered in the previous 7 days and groups them by agent... Then it also counts a total for all the agents for the previous 7 days as well, not grouped. How can i query a specific day of the week, say 4 days back, only count that days records and group them by agent?
View Replies !
Increment The Specific Field By 1 In A Query
I still do the process of incrementing a specific field in a stand alone apps by query 1: get its value query 2: update value +1 But, i realize that this is really a bad idea when this would be used in web applications... specially the database is in another webhost....(very slow response) i tried (using php function) Expand|Select|Wrap|Line Numbers
View Replies !
Query Result In A Specific Format
I need a query in which will give me a single column result ----------------------------------- | [any column Name] | ---------------------------------- 8:00 AM ---------------------------------- 9:00 AM ---------------------------------- 10:00 AM ----------------------------------- 11:00 AM ---------------------------------- I want the query to return a single column name with multiple rows in return ...
View Replies !
Sql Query To Exclude Specific Data From Resultset
I have got two tables,one customer with fields cust_id,email and another with field cust_id,domname,expirydate. i have this query to combine these two tables to give me email and the expirydate as $sql = "SELECT l.email,s.expirydate from customer l, domain s where l.cust_id = s.cust_id "; I need to exclude those expirydates whose domname contains .np in the text.
View Replies !
MySQL Admin - Lost Thread On Specific Like Query?
mySQL win32 (4.0.22) I am using mySQL Control Admin and seeing a lockup on a thread specific to a like query. select * from table1 where field1 like 'http://h%' limit 100 This request will sit (thread) forever and never return any records. (status : sending...) However : select * from table1 where field1 like 'http://h%' limit 100 returns records immediately, no problems... I have already run : /mysql/bin/myisamchk --sort-recover dbtable1, no errors, no problems... How can I tell what this thread is really doing or if any additional problems exist with this table using myisamchk?
View Replies !
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.
View Replies !
1 Line Query To Delete Specific Records From Multiple Tables
On clients machine, currently to delete on trainee record it runs 10 queries to delete records from 10 tables. At the time of running all queries, server shows (104) Connection reset by peer. An error condition occurred while reading data from the network. I think it because of running 10 queries at a same time. Is there any possibility that through one line of query we can delete record from 10 tables. I've tried following query DELETE FROM table1, table2, table3, table4, table5, table6, table7, table8, table9, table10 WHERE empID = 11; But it gives ' error in query.
View Replies !
How To Log Specific Queries
I've got this DB. Every few minutes new data comes in and is inserted. Now and then for some reason duplicate data comes through - not a big problem - unique key. However I'd like to log the insert failures, due to dupes, but I don't want to log all of the good inserts also.
View Replies !
Specific Table
Hello MySQL gurus, This is the command : mysqldump -u$user -p$pass $database >$backup Can you exclude a specific table, say table UnNecessaryAndHugeTable, from the dumping process? that is mysqldump all tables except table UnNecessaryAndHugeTable
View Replies !
Changing The Value In A Specific Row
i have a forum that i am trying to add a username kind of thing to. what i have done so far is to set up a table that has three coloumns in it, USERNAME, PASSWORD, and STAT, of which stat is a boolean value that i use to determine if they are logged in or not (what i intend for it to be), and the other two are pretty self explanatory. my question is is there a way for me to get into this specific row and change the value stored inside STAT, or is my only choice to completely delete this row then re-enter it into the table everytime stat has to change? thanks guys. btw good forum you have here
View Replies !
Copy Specific Row
I have a database that host a "user login data," and i have built a new script to expanse the service in other computer's database. I want to know are there any methods to copy the user data row from the "user login data" to the new computer's database that i have just built? I am not talking doing this manually by hand, but doing this like replication a specific of row.
View Replies !
Getting Specific Results
my db looks like this: username - skill1 - skill2 - skill3 John - warrior - thief - warrior How can i only output the fields that are warrior? Ive tried this but it dident work: SELECT skillsM1, skillsM2, skillsM3 FROM ugd HAVING skillsM1 AND skillsM2 AND skillsM3 = 'warrior' Or is it necesary for me to change my db structure?
View Replies !
Specific Data
Ive been struggling with this for some time, wonder if someone can help, I have a database table column that has some information within it that I do not want, however there are over 100,000 records and it'll take some time to update them all - I'm trying to find the sql command to update the info currently I have: PHP Code: REPLACE * from table WHERE `column` LIKE '%data_not_needed%' I want to replace it with nothing so it simply takes the not require word / data out from that column - what else can I add into that sql command to show this?
View Replies !
Specific Rows
When I was creating a database I misassigned some rows and now I just want to change those 4 rows but can't figure out how to do it. I tried using this command Code: update variables set bl_id=16 where bl_id in (SELECT bl_id from variables where var_id between 379 and 382)
View Replies !
Specific Search
I'm creating a PHP application in which I need to do a specific search that I'm not being able to formulate it. On my database, I have a table that register different organizations. Each organization has a primary key called org_id, which is an integer auto_increment. Well let's say I want to create a search in which I want only specific organizations to show up. Let's say I have 100 organizations registered, and I want to search for organizations with org_id 1, 4, 7, 15, 33, 45 and 85. Well, how do I write a MySQL code that will return these organizations, only these organizations, nothing else?
View Replies !
Specific Join
I have a table with roughly 50 million records that contains user data for my company's sites. Two of the columns in the table are varchar columns that show the url a user is coming from (referurl) and the url they're going to (url). So if you look at user's session, you can see the progression they made while navigating through our site. Each user's session is identified by a number, which is indexed. I recently created a companion table that assigns a number to both the url (url_id) and the referring url (ref_id) for each one of the records in the main table (a table matching those two values is elsewhere in the database. These two tables have the same auto-incrementing unique id number (uid), and the same number of records. The two non-unique numerical fields in the companion table have a multiple index on them. Previously, I have been using the mysqldb module for python to do a very particular kind of search. Basically what I want to do is search for every record matching a url that I input, then find the url of the first site that user came in on. What I would do is SELECT the records that matched my search (I was matching them on the url field I mentioned above), then have python submit a query to get the sessions from each one of those results. Python would then find the url from those results that had the highest id number and matched one of our 15 or so landing pages in the url field (which would make it the most recent landing that that user made to our site). It always seemed to me that this is something that could be accomplished more quickly by a more complicated MySQL query that uses the newly created companion table instead of matching the string of the url. I've come up with this query to just get the results I want using the companion table (table names changed to protect the innocent): ......
View Replies !
Specific Date
My registration_date column has to be a DATETIME type and I have to zero in on a specific date. SELECT registration_date FROM reserved WHERE ( registration_date >= '2005-18-06 00:00:00' ) AND ( registration_date <= '2005-18-06 23:59:59' ) Is there a more streamlined way I can extract just the date from a DATETIME type, or is this the best or only approach
View Replies !
Updating A Specific Row
I have a question about updating a row in a table. This table keeps a record of when a user logs in/out with username, time of login/logout and ip-adress. When a user logs in, the logouttime is of course left blank. When that user logs out I need to update that row with the logout time. I know I need to use "UPDATE log SET timeLoggedOut = '$timeLoggedOut'" I have a session variable with the identity of the user currently logged in. But how do I update only the last row in the table containing the username of the user logging out? If I only use WHERE username='$userloggedin' it will update every row containing that username..... EDIT: I guess I could use $sql = "SELECT MAX(loginID) from log WHERE username = '$userloggedin'" to identify the row I need to change, right? But how do I return that information from the db so that I can put it in a php variable?
View Replies !
Retrieving Specific Records
I am trying to establish (using a straight MySQL query in version 4.0.18) the level of qualifications someone has, i.e. degree, postgraduate degree or other. To simplify things, I need to use only two tables, namely: master table, containing: staffno (Primary Key); forename; surname; psnquals table, containing: psnqualid (Primary Key); staffno (Foreign Key); levelid (where 1 = degree, 2 = postgraduate degree, 3 = other) In the followning query, staffno 1 has three qualification levels, 1, 2, 3. The query finds the first matching record (where cdelevelid = 1) and then stops, making it appear as if the employee had no postgraduate qualification. One person can have one or all of the above qualifications. I need to write a query that shows if a person has one, two or all of the above. I tried using the following query, but it seems only to look at the first psnqualid record it finds and then stops: SELECT m1.staffno, m1.forename, m1.surname, CASE WHEN p1.cdelevelid = 1 THEN "D" WHEN p1.cdelevelid = 2 THEN "P" ELSE "O" FROM `master` m1 INNER JOIN psnquals p1 ON m1.staffno = p1.staffno I also tried using an IF statement, but this also seemed to stop after finding the first matching record in the psnquals table.
View Replies !
Privileges On Specific Fields In A DB
How do I make a table with fields again, say, name, age, address, wife etc. that I want people to be able to look at (with any client like MysqlCC etc). but only be able to modify certain fields, eg. wife (this includes deleting the field from the database, maybe) - the name, age etc. must be left intact, ie. not changeable by anyone.
View Replies !
Order By Specific Values
Is it possible to order by specific values in a column. For example a column may contain values like N, N1, N2, S, S1, S2, R, T. I want to order by in such a way that all the records with N, N1, or N2 in that specific column will show up on the top. All the records with S1, S2 values will show up next and finally all records with R and T values in that specific column will show up. Please give me the select syntax.
View Replies !
Get Specific Table Names
I have a couple of tables and I want to make a select statement that will show them to me. I think with an example is easier to explain. I have some tables users members clients cdhonda cdhonda_values hrtoyota hrtoyota_values emails prchevy prchevy_values I would like to make a query to look at all the tables, and the ones that have the "_values" to give me the part before the "_". For this database for example, the query would give me the resutl "cdhonda hrtoyota prchevy"
View Replies !
Customers At A Specific Time
I have a table called "HISTORY" that contains historyid = integer that holds the autoinc custid = integer that holds the customer id startdate = a datetime that customer came to the shop stopdate = a datetime that customer leave the shop What I want to know is how many customers where at the shop at a specific time, example 2005-10-10 12:00:00 I want the number of people that were in the shop at this specific time. PS: Take for granted that day may change, for example someone can come (Startdate) at 2005-10-10 23:58:55 and leave at 2005-10-11 02:11:49
View Replies !
Rated By A Specific User
I'll ask in this thread since the problem is somewhat similar. What I have is one table named products with fields product_id and product_name one table named ratings with product_id, user_id, rating and one table named users with user_id and username. Ratings will contain entries added by users and what I need to do is select all products from the first table which have not been rated yet by a certain users identified by user_id.
View Replies !
Syncronising Specific Tables
I would like to ask you a question regarding a problem that I experience. I have to syncronize two databases, one MySQL and one MS-SQL. Unfortunately, I am not aware of MS-SQL databse but a friend of mine is. So, he told me that he can export data to a mySQL database, which will contain three diverse tables: one with new products added, one with the deleted products, and one with an update of the current products. Is there a way the initial (web-based) MySQL Database to read the exported database and make the necessary adjustments related to the each table (table 1: adding, table 2: deleting, table 3: updating). How can this procedure be done?
View Replies !
Updating On A Specific Count
I tried searching for this, but to no avail. I need to update a "valid" bit in my table (set it to 0), when the total number of rows that have been grouped by column bin_id is less than 5. Ie: bin_id valid 0 1 0 1 1 1 1 1 1 1 2 1 2 1 2 1 2 1 2 1 3 1 here, all rows where the number of rows contained in the respective bin_id is less than 5 are set to invalid. (in this case, there are 5 rows which have bin_id = 2, so valid remains 1. for the rest, there are too few rows of the respective bin_id, so i need to set valid to 0.
View Replies !
WHERE :: Specific Letter Of The Alphabet
How would I formulate a SQL statement where I want the first letter of a field to be equal to a specific letter of the alphabet? IE: I have the field lastName and I want to select all the records that begin with the letter 'a'.
View Replies !
Retrieving The Last Value Of A Specific Column
I need help in retrieving the last value that is entered in my table for a specific column An example to clarify what I want to do: I am collecting data automatically and the values of which are being inserted into a database. I want to retrieve for example the last row entered for a column named e.g. current_number. I am not sure how this can be done in SQL. One idea I had is to use the last_insert_id(), but I am not so sure how to use it as an sql query. Is there another way to get the the last value of a specific column in a table?
View Replies !
Selecting And Displaying Specific Row
I wonder if there is a way to select a specific row in a table and than displaying that row. For example i would like to dispaly results starting from the second oldest entry in the table ... thus omiting the latest entry. Can anyone point me in the right direction?
View Replies !
Selecting Specific Records
I have two tables. I want to select the last record for a specific type from one table and look up info from the other table. Is there a command that will select go to the last record of a query?
View Replies !
Only Selecting Specific Rows
Fields: studentID, reviewID, date I want to get all rows in a certain date range where there are at least 3 rows for a given studentID. In other words... Case 1: student has written 2 or fewer reviews during the date range. Don't retrieve these rows. Case 2: student has written 3 or more reviews during the date range. Retrieve ALL of their reviews during that time. So if they wrote 5 reviews, the query will have 5 rows for their studentID. Any ideas for this query? So far I got the date range to work using this. I'm struggling with the 3+ reviews part. SELECT `studentID`, `reviewID`, `date` FROM `review` WHERE `date` BETWEEN '2007-10-21' AND '2007-11-09' ORDER by studentID, `date` Any ideas?
View Replies !
Creating Indexes For Specific SQL
I'm trying to optimize a SQL statement that shows up in the slow query log several thousand times. The statement is the same but the values of 'XXX' differ. SELECT group_member.id, <many more fields here>... FROM group_member left outer join group_node on group_member.group_node_id=group_node.id left outer join domain on group_member.domain_id=domain.id WHERE group_member.domain_id = XXX and group_member.object_id = XXX and group_member.class_type = 'XXX'; To optimize this, should I create one compound index like this? ALTER TABLE group_member add index group_member_idx (`domain_id`,`object_id`,`class_type`); If I'm reading various articles correctly, adding indexes to domain_id, object_id, or class_type separately won't benefit this sql statement.
View Replies !
Can You Run MySQL On A Specific Cpu Core?
I'm running Windows 2003 sp2 web edition MySQL 5.0.51b I have a dual core machine but would like to restrict the MySQL server to run on only one of the cores. I've search the help, the mqsql.exe --verbose --help details Checked the entries in the my.ini googled (found one page but the site is down) I have the FireDaemon service tool installed so I could try removing all history of the existing service and create a fire daemon version (where I can specify the core). But I was hoping thre would be an easier way?
View Replies !
Generate ID Of A Specific Format
I am writing a Java web app, where I need to auto generate & increment the primary key. It should be of the format ABC<auto-increment-serial><month><year>, so it should look like ABC-001-072008. I know how use auto-increment id in MySql, but I am stumped on where to look for a similiar occurance. A search on this forum did not return a solution.
View Replies !
Do Not Group By A Specific Column
I got a question how to set up a SQL-Statement... I got 2 fields. One has ids saved (not unique!) and one got answers. |uid|answer| |1 |yes | |2 |yes | |1 |no | |1 |no | How do I count how often EACH uid got which answer? SELECT *, count(*) as c FROM table GROUP BY answer That is what I got till now - but the output here is: |uid|answer|c| |1 |yes |2| |1 |no |2| but I want: |uid|answer|c| |1 |yes |1| |2 |yes |1| |1 |no |2|
View Replies !
Populating A Specific Column
Is there a method of populating a specific column only? I've created the table and since then I've had to add another column. The information for this column is ready, but I don't want to enter 170 lines individually. I can't seem to find the proper code for doing it. I'm familiar with loading text files, and I've queried the one column I need, but I can't populate it.
View Replies !
Creating A Foreign Key With Specific Name
In my java program I want to programmatically convert some data from xml to a MySQL database. A couple of my tables have circular foreign key dependencies, so I'm unable to simply empty existing tables sequentially before the insertion of new data. I would like to drop certain foreign keys, then after data conversion, reinstate them. I'm able to drop the foreign keys. But later, when I try: alter table mytable add foreign key foreignkeyHello (mykey) references myothertable (myotherkey) it creates the foreign key properly, but the new foreign key does not have the name "foreignkeyHello". The foreign key's name is something like mytable_fk_1. I'm sure the constraint would work fine but since I need to reference that foreign key in code later, I need for the name to be "foreignkeyHello". How can I do that?
View Replies !
Deleting A Specific Record
Im working on a site witha Mysql backend for news and stuff and am trying to make it so that I can delete specific news entrys if need be. I have it set up so that in the admin section it generates a link next to each record like the following for its own idhttp://localhost/includes/deletenews.php?id=1 which points to the deletenews.php that looks like this Code:
View Replies !
Get Cell At *specific* Location?
How do you get a value from a specific row, without fetch_array'ing until it has reached the row I want? I have a randomized MIDI player, I have a random number that tells which MIDI to play, but how do I get that from the database?
View Replies !
Finding Around An Specific Number?
I have a page in php and mySql and in my query I look for a number that's been input from a dropdown in a form. Let's say I pick number 5. So I put WHERE number LIKE $number to get what I picked. But now I wanna get the number picked and also the 3 lower numbers as well. So if I pick 5 I would get 2, 3, 4 and 5. If I pick 20 I would get 17, 18, 19 and 20. Is there a simple way to do this in the query? Is there something I could use for this, just as I can use = or < and >.
View Replies !
How To Get Records Between Two Specific Dates?
i'm a newbee to mysql. i'm having a table called transaction which stores transaction details with dates. i'm having another table callled company in which the company info stored, The company table has the fiscal month details. what i wnt to do is get the records from the transaction which is between two specific dates(i.e) in the fiscal year eg: 04-01-2006 to 03-31-2007 as far as now i'm having the query, SELECT t.* FROM transaction t,company c where t.companyid=c.id AND (MONTH(t.transdate)>MONTH(str_to_date(c.fiscalmonth,'%M')) AND YEAR(t.transdate)=YEAR(now())) || (MONTH(t.transdate)<MONTH(str_to_date(c.fiscalmonth,'%M')) AND YEAR(t.transdate)=YEAR(now())+1) and c.id=2 order by transdate Note: the company table stores the fical months as month names(i.e) March etc., i think this is far complex, and is there any easy function to get the records b/w two dates??
View Replies !
Duplicate Specific Set Of Rows
I'm receiving a query error and im not sure why. It is supposed to duplicate a specific set of rows in a table. Which rows is determined by a different table. Is therea clear syntax error? MySQL INSERT INTO listings VALUES (SELECT * FROM listings WHERE id = (SELECT list_id FROM groupings WHERE group_id IN(2) ) ) The MySQL error said it was Quote: near ''(SELECT * FROM listings WHERE id = '(SELECT list_id FROM g' e39m5
View Replies !
|
TRACKER
