How would i go about counting the number of rows that match two variables? I have a table called: users and fields called: username & referral I have another table called: comments and fields called: *comment_username* This is the situation, I need to fetch the number of referrals with at least 10 comments (rows in the comments' tab...
I am a newbie with Subsonic. I want to create a SQL connection using DSN; then I want to generate the DAL using Subsonic. Please can you give me some pointers on what to do? ...
I have managed finally to get Solr working, with the help of all you guys, so THANK YOU! And I have to say, I now understand why you recommended it, it's really powerful. Now, to the issue... I have indexed all "Searchable" information into Solr, and my plan is to query Solr, and then get the ID:s of the query-results (of all records t...
hi i have this query and i need to get the max value of number and the city how to do it ? select city,count(id) as number from men group by city order by number desc thank's in advance ...
I have the following MySQL-table Id | One | Two | Three ---------------------------- 1 | 10 | 30 | 20 2 | 50 | 60 | 20 3 | 60 | NULL | 40 Edit: Of course the table doesn't need to be NULL by default, but I don't want it to affect the average (so the average is calculated as 50 and not 33,33). I want tha...
I have a table which has data like this id test_val type_cd #------------------------- 101 TEST22 M 102 TEST23 M 103 TEST01 M 103 TEST01 H 104 TEST02 M 104 TEST02 H 105 TEST03 H I would like to fetch the max(id) for each type_cd and its corresponding test_val i...
I need to use natural sorting with a MySQL result since it currently sorts my INT values as follows: 1 123 1256 22 231 [etc] While I would need it to sort like 1 22 231 1256 I know of the "natsort" function, but it does not work for a MySQL query result. How can I sort (naturally), is it possible to do this within the query? Th...
I have a series of select statements in a text file and I need to extract the field names from each select query. This would be easy if some of the fields didn't use nested functions like to_char() etc. Given select statement fields that could have several nested parenthese like: ltrim(rtrim(to_char(base_field_name, format))) renamed_f...
How would I write a loop that has a select in the “in” clause of the loop that selects a col of type varchar(10), but then inserts those values in a col that wants them to be varchar(9)? Basically I’m trying to “typecast” from one precision to another, if that makes any sense. Example: FOR V_TEN IN (SELECT THIS_IS_VARCHAR_TEN FROM TABLE...
So I am trying to return only the records that are set to default from a mapping table but it is setting all languages to default related to that item from that market. Bellow is the exact sql I am using. SELECT `teams`.*, `markets`.`title` AS `market`, `markets`.`short_name`, `market_countries`.*, `count...
Hi, I was asked in an interview,a question from oracle sql.this seemed to be a simple question but i had no clue to answer.could anybody help? if there is string like "newyork is a beautiful city" in a colum. select column_name from table_name; will result newyork is a beautiful city what is the query required to give the output...
EDIT: Because my brain failed, I actually meant a one to many, not a many-to-many as I first wrote this. This makes it a bit easier :) I'm in the process of designing my database and one of the Tables (Tasks), needs to be able to have a one-to-many relationship with itself. This is because a task can have a number of sub-tasks that have...
I'm designing my first database for MySQL and the idea here is that we have orders that can contain multiple different items. So i decided to store order with all the relevant info in one table, items in another, and then create a third table where each ordered item is stored. Of course every time i will need to list an order i first wi...
In a little script I'm writing using SQLAlchemy and Elixir, I need to get all the distinct values for a particular column. In ordinary SQL it'd be a simple matter of SELECT DISTINCT `column` FROM `table`; and I know I could just run that query "manually," but I'd rather stick to the SQLAlchemy declarative syntax (and/or Elixir) if I c...
I came accross this statement for inserting an IP into a mysql table. INSERT INTO `best` VALUES (132+256*(172+256*(109+256*(115)) I would like to know why an IP is being inserted this way, and how to actually work out what IP is being inserted ...
I have a Django application and I'm using postgres. I try to execute the bollowing line in one of my tests: print BillingUser.objects.all() And I get the following error: "current transaction is aborted, commands ignored until end of transaction block." My postresql log: ERROR: duplicate key value violates unique constraint "...
I'm writing a data tree structure that is combined from a Tree and a TreeNode. Tree will contain the root and the top level actions on the data. I'm using a UI library to present the tree in a windows form where I can bind the tree to the TreeView. I will need to save this tree and nodes in the DB. What will be the best way to save the ...
i have got two table create table t1(cid int, isnews int) create table t2(nid int,cid int, isnews int) situations is like this: if t2 contain t2.cid = t1.cid then the t2.isnews = t1.news and if t2 not contain cid of t1 then new record should be inserted in t2 and that t1.cid, t1.isnews should be inserted in t2.. and complete ta...
Hi, I have a database which contains 5 fields, which are: name, address, idcard, town, street I then have a PHP query which accepts one parameter. In this parameter, you can enter whatever fields you like and the results must still be accurate. For example, if I enter john doe skate street in the search box you will get all the record...
I have recipes table and ingredients table and table that connect ingredients to the recipes. I have a list of ingredients, how to write a SELECT statement (or store procedure) that will return a recipes that have ALL given ingredients? How to write this query for MySQL? ...