Goal: to get a list of N random questions that have been answered correctly the least amount of times, per user. following sql will provide me a list of having a count of correctly answered questions for user_id 4. select q.id, temp.Count from questions as q left join (select q.id, count(a.id) as count from questions as q left join att...
I want to make an AUTO_INCREMENT column in a database table,here is the syntax i write: create table comments ( name varchar(20), mail varchar(30), comment varchar(100), com_no int auto_increment ); and i get the following error: ERROR 1075 (42000): Incorrect table definition; there can be only one auto column ...
i have two tables tableA in databaseA on ServerA and tableB in databaseB on ServerB. i just want to perform fullouter join to these tables based on common fieldname of it ...
I am supposed to remove the following from the address field. Replace all words named as 'Flat' to empty space, also remove the number that comes along with it. eg. I have word called as 'Flat 234 5th Street'. It should be replaced as 5th Street. I gave the query as select ltrim(rtrim( substring ('Flat 123 5th Street', cha...
How would you implement a custom search engine? What do you think about something like this: SELECT * FROM jobs WHERE job_id IN ( SELECT job_id FROM job_words WHERE word_id IN (SELECT word_id FROM words w WHERE text = 'carpenter')) AND job_id IN ( SELECT job_id FROM job_words ...
I've got tables like the following (Django model definition, with a postgres database underlying it): class Person(models.Model): id = models.IntegerField(primary_key=True) name = models.CharField(max_length=300) class Owner(models.Model): id = models.IntegerField() person = models.ForeignKey(Person) I use a Pyth...
Hello excerpts, I have a Database table UserTable CREATE TABLE [UserTable]( [ID] [int] NULL, [Name] [nvarchar](50) NULL, [City] [nvarchar](50) NULL ) ON [PRIMARY] this table have following data ID Name City ----------- -------------------------------- 1 Vijendra ...
I'm trying to preform a distance calculation to return a listing of places within a certain distance. This is based on using a zip code database and determining the distance from the origin to each location. What I want to do is limit the results to be within a certain distance from the origin, but I'm having trouble with my MySQL query....
I'm looking for some (more than intro-level) database optimizations, not just for Rails but for SQL in general. In Rails, how/when do you start using these? cache_counter caching ids in a table (user has_many roles, so the user table gets a role_ids serialized column, since every request requires fetching the roles). Are these types...
hi people i am new at php and wondering what does int(4) mean in creating this table. thanks for helping. CREATE TABLE mytable( name VARCHAR (50), age INT(4), email VARCHAR (50) ); ...
I've got 3 fields (grader 1, grader 2, grader 3) and I'm coming in with a grader ID and I want to make sure that grader id isn't one of the three (if they're not null). Did I do this right? WHERE @fycuserid <> ISNULL(grader1_fyc,0) AND @fycuserid <> ISNULL(grader2_fyc,0) AND @fycuserid <> ISNULL(grader3_fyc,0) ...
How do you look for a delete ID number that has been used, but deleted as is no longer used, before you just append on top of the last ID number that has been added For example: ID Name 1 Orange 5 Apple I have auto increment on, I want to add Banana in to number 2, but auto increment is adding it as ID 6 I'm ...
I need to change the server paths that were stored in my database (Wordpress), so I am searching for the string "/home/". Is there some kind of command such as str_replace($search, $replace, $subject) equivalent in SQL? Edit: What if I don't know what the field name is? Well, I do, but there is more than one field name. I was just hopi...
How to INSERT INTO a MySQL table using ON DUPLICATE KEY UPDATE so that if the row already exits one of the columns of the row gets it value plus 1? So, if I have two columns titled ip and count, the count column contains 1 in the first place and increases its value on every next UPDATE. Can we do that in one single statement? ...
Hi I've a table (hosted in a database SQL Server) where i've products stored. The person who inserted a few products last week, instead of making new lines with "Enter" (tinyMCE would created a </br> tag) in description, she had typed "Space" creating white spaces (she though that typing white spaces, creates new line when it goes to th...
How best store year, month, and day in a MySQL database so that it would be easily retrieved by year, by year-month, by year-month-day combinations? ...
I have Group and each group has contacts associated with it. When a user deletes a group, if the group is not empty then it will alert them that all contacts in that group will be deleted if they continue. Well anyways, so my problem is setting up that feature. I have tried to figure out how I can delete all contacts that belong to that...
If I have a table of fruits: FruitId ColorId NumofPurchased. I want to return a row for each of the colorId's where NumOfPurchased is max for that colorId. I am coming up with approaches like looping through the colorId's or manually write 10 queries for 10 colors ... I don't know, is there a better/more optimized way to do this? I ...
I want to store an HTML code in an SQL database. It stores everything fine except when there are attributes defined such as border="0". I think single quotation mark is not an issue. How do i avoid this from happening. The error: Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version ...
I have a table house with fields price and area, now I want to add one more column named 'average_price'. How could I set the average_price according to the price and area in MYSQL? ...