where-clause

What is wrong with this sql where-clause?

I am selecting records based on two dates in a same column but my where condition fails to select records on the StartDate and EndDate... where CreatedDate between @StartDate and @EndDate I get only the records inbetween the dates and not the records on the StartDate and EndDate... Consider if I pass the same date as StartDate and EndD...

CodeIgniter: How to use WHERE clause and OR clause

Hey! I am using the following code to select from a MySQL database with a Code Igniter webapp: $query = $this->db->get_where('mytable',array('id'=>10)); This works great! But I want to write the following MySQL statement using the CI library? SELECT * FROM `mytable` WHERE `id`='10' OR `field`='value' Any ideas? Thanks! ...

Linq/C#: Selecting and summing items from the result of a group by?

Hi, I have a list like this: City Total Sydney 11 Dublin 9 London 12 Dublin 3 London 9 Sydney 12 I first of all need to Group By City & Sum Total so I have Sydney 23 Dublin 12 London 21 Next I need to filter for those that entries where the total is > 20 Sydney 23 London 21 And what I finally need is the total of these entrie...

array contains in where

hi, i've to get rows from a table CBR_MissioniGruppi that have id in gruppi list. I've tried with this, but doesn't work. List<int> gruppi = new List<int>() { 1, 2 }; foreach (CBR_MissioniGruppi mg in db.CBR_MissioniGruppi.Where(p => gruppi.Contains(p.GruppoID))) How can i do it? thanks ...

Help with Oracle SQL Count function! =)

Hi guys.. The question I'm struggling with is this: I have a list of helicopter names in different charters and I need to find out WHICH helicopter has the least amount of charters booked. Once I find that out I need to ONLY display the one that has the least. I so far have this: SELECT Helicopter_Name , COUNT (Distinct Chart...

Multiple conditions with CASE statements

I need to query some data. here is the query that i have constructed but which isn't workig fine for me. For this example I am using AdventureWorks database. SELECT * FROM [Purchasing].[Vendor] WHERE PurchasingWebServiceURL LIKE case // In this case I need all rows to be returned if @url is '' or 'ALL' or NULL when (@url IS null OR @u...

MYSQL JOIN WHERE ISSUES - need some kind of if condition

Hi Well this will be hard to explain but ill do my best The thing is i have 4 tables all with a specific column to relate to eachother. 1 table with users(agent_users) , 1 with working hours(agent_pers), 1 with sold items(agent_stat),1 with project(agent_pro) the user and the project table is irrelevant in the issue at hand but to give ...

Mysql optimization for select query with IN() clause inside where clause (explain output given)

I have this query:- SELECT SUM(DISTINCT( ttagrels.id_tag IN ( 1816, 2642, 1906, 1398, 2436, 2940, 1973, 2791, 1389 ) )) AS key_1_total_matches, IF(( od.id_od > 0 ), COUNT(DISTINCT( od.id_od )), 0) AS tutor_popularity, td.*, u.* FROM tutor_deta...

Sql select null and non null values in WHERE clause

I am trying to run a query that selects values from a table using a WHERE clause , the query only returns the rows where all of the conditions have values, hoe do I go about returning the values that are also null? Language Table Students Table ID Language ID Student L...

How do I get a SQL function to return a list to be used by the IN statement in a WHERE clause?

Hey guys I have a complex SQL Query, that needs to be filtered further. Part of the WHERE clause looks like this: Where P.PeriodID in (36, 37) I need to get it to look more like this: Where P.PeriodID in dbo.GetPeriodsInRange(@startDate, @endDate) The function must return a list of PeriodIDs to be used by the IN statement. I reall...

What does this SQL WHERE clause mean?

Hi. I'm having some difficulty in understanding the following WHERE clause in a T-SQL (SQL Server 2000/2005) query: update #tempTable SET Total_Avg=isnull(TerminationReason,'terminated'), Individual_Subscriptions=null, Business_Subscriptions=null, Other_subscriptions=null, -- snip. 10 more fields set to NULL. PMI...

CASE statement to switch WHERE conditions

I would love to have a t-sql statement like the following... SELECT [field] FROM [table] WHERE CASE @flag WHEN 1 THEN col1 = @value ELSE col2 = @different_value END The point being I want to put a flag in my function to use different where clauses in the same query. I just can't get it to work. Is this possible? ...

MySQLi using an IN in a where

Hi there i've been having some trouble using an IN in a where clause using MySQLi this is my query: SELECT * FROM core_tags WHERE tag_id IN (1,2,3,4,5) GROUP BY tag_id ORDER BY tag_popularity ASC If I run this in PHP My Admin then I get 5 results as I would expect. However if I run it in PHP with the following code I only get one res...

SQLite query where clause with floating point numbers fails?

I'm putting a float in an Android based SQLite database, like so: private static final String DATABASE_CREATE = "create table " + DATABASE_TABLE + " (" + KEY_ID + " integer primary key autoincrement, " + KEY_FLOAT + " REAL, " + ... ... content.put(KEY_FLOAT, 37.3f); db.insert(DATABASE_TAB...

how to add WHERE clause to Query on android

I would like to limit the results to those whose KEY_HOMEID is equal to journalId. I've been on this for a couple days any help would be appreciated. public Cursor fetchAllNotes(String journalId) { return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_HEIGHT, KEY_BODY, KEY_HOMEID},"FROM DATA...

Using SQL Server 2005 I'm trying to do a HAVING in a WHERE clause sound crazy?

Ok, I have over a million records and I need to select all information from the database where the count of a column is greater or equal to 10. I know that using having I must use group by and using where I cannot use aggregate functions with operators so what I want in pseudo code is Select column1,column2,column3 From MYdatabase Wher...

Select query on a partitioned table

Hi, I have partitioned a table and it has been partitioned according to an account id. ie also rows that have accountid =1 will be in A partition and all rows that have accountid = 2 will be in B partition. so now if i execute a query with where condition accountid =1 sql server will search only in A partition. Suppose if i add anothe...

Order by Maximum condition match

Please help me to create a select query which contains 10 'where' clause and the order should be like that: the results should be displayed in order of most keywords(where conditions) matched down to least matched. NOTE: all 10 condition are with "OR". Please help me to create this query. i am using ms-sql server 2005 Like: Select * ...

If statements within a Linq where clause

Hello all. Struggling a bit today. I have the following method that returns a list of products..lovely. public static List<tblWeight> GetProductInfo(string memberid, string locationid, string basematerial, string source) { MyEntities getproductinfo = new MyEntities (); return (from p in getproductinfo...

Complex Linq-to-SQL where condition help

I have a perplexing SQL select statement (ugly) that I'm trying to write in LINQ. I'm working to get a single statment that will execute, not pre-selected data into lists that I have to send back to the server. DECLARE @StartDate DATETIME ; DECLARE @EndDate DATETIME ; SET @pStartDate = '20100501' SET @pEndDate = '20100531' SELECT r.com...