query

Possible to compare COUNT(*) from multiple rows on MySQL?

Hi I am working on a query that is using the below schema to find out how often users are doing searches by company (the app basically allowed you to do a search by company) SEARCH_LOG ---------- date_of_search (DATETIME) company_id (INT) COMPANIES --------- id (INT) company_name (VARCHAR) (there are more columns but these are the re...

string + db table to populate values in gridview.

Hi, I've have a string with prodIDs like "3, 16, 12" is it possible to match these Ids with the product table in the db and display details like name, price in the gridview? PS: im new to c# and asp.net! thanks, ...

Use SELECT inside an UPDATE query.

How can I UPDATE a field of a table with the result of a SELECT query in Microsoft Access 2007. Here's the Select Query: SELECT Min(TAX.Tax_Code) AS MinOfTax_Code FROM TAX, FUNCTIONS WHERE (((FUNCTIONS.Func_Pure)<=[Tax_ToPrice]) AND ((FUNCTIONS.Func_Year)=[Tax_Year])) GROUP BY FUNCTIONS.Func_ID; And here's the Update Query: UPDATE...

How to combine 2 LINQ into one

I am trying to populate a treeview control with some info and for that I am using 2 separate LINQ as follows; Dim a = From i In CustomerTable _ Group By ShipToCustName = i.Item(6), BillToCustName = i.Item(4) Into Details = Group, Count() _ Order By ShipToCustName Dim b = From x In a _ Group...

RDBS when to use complex indexes for queries and when use simple?

Suppose I have table in my DB schema called TEST with fields (id, name, address, phone, comments). Now, I know that I'm going to perform a large set of different queries for that table, therefore my question is next, when and why I shall create indexes like ID_NAME_INDX (index for id and name) and when it's more efficient to create separ...

simple query with brackets - how to build?

I'm trying to build a query to use for a search engine, that would look like this one: SELECT * FROM sometable WHERE col1 = 1 AND col2 = 2 AND (col3a = 3 OR col3b = 3 OR col3c = 3) I though the code below would work: SubSonic.Query query = new SubSonic.Query("sometable"); query = query.WHERE("col1", 1); query = query.WHERE("col2...

Delete all records from a table

How can I delete all records from a table using SubSonic? The Delete method has three overloads, but each one expects some parameters. And how can I delete records using a query (e.g. delete all records where column1 > 100) ...

SubQueries in MS ACCESS: selecting only one record per "person" per date.

I am using a table called analyzed in Microsoft Access. It has many fields but the three ones being used for filtering in this case are analyzed.readings_miu_id, analyzed.ReadDate, analyzed.ReadTime. I need to pull the records from the "analyzed" table where readings_miu_id are grouped together then sorted by ReadDate but showing only th...

T-SQL grouping question

Every once and a while I have a scenario like this, and can never come up with the most efficient query to pull in the information: Let's say we have a table with three columns (A int, B int, C int). My query needs to answer a question like this: "Tell me what the value of column C is for the largest value of column B where A = 5." A ...

LINQ and Devexpress Grid Datasource

I have a DevExpress grid (DevExpress.XtraGrid.GridControl 8.2) with a datasource set at runtime like so: private DataContext db = new DataContext("connection string"); gridControl.DataSource = from t in db.sometable select new { Field1 = t.Name, ...

Specific Time Range Query in SQL Server

Hi All, I'm trying to query a specific range of time: i.e. 3/1/2009 - 3/31/2009 between 6AM-10PM each day Tues/Wed/Thurs only I've seen that you can get data for a particular range, but only for start to end and this is quite a bit more specific. I didn't see any SQL Server commands that would directly help me on this, so does an...

How would you optimize the following query.

I am using the following query to find out to top 6 viewed pages in my Drupal site: SELECT n.title, n.nid, c.daycount FROM node n JOIN node_counter c ON n.nid=c.nid WHERE n.type='page' AND n.status = 1 ORDER BY c.daycount DESC LIMIT 0,6; This is very natural and works well on most sites. However, on a site with many nodes (1.7m), ...

MySQL query indexing with timestamp structure

Hi all, I was wondering what the best way of storing user queries correlated with timestamps in MySQL was. Let's say I have just two inputs, a user's "query" and "timestamp"... I could create a MySQL table with fields (id, query, count, timestamp_list), where: id is unique identifier of the query, query is the literal query string...

How do I select a row that appears in a text list?

I have a SqlServer customer table customer (first_name, last_name, home_phone, cell_phone) and a text file list of phone numbers like 9876543210, 4564561234, 1231231234, 1234567890, The phone numbers in the customer table are stored in the format +1dddddddddd: where dddddddddd is the phone number. How can I find all the customer ...

MySQL to get the count of rows that fall on a date for each day of a month

I have a table that contains a list of community events with columns for the days the event starts and ends. If the end date is 0 then the event occurs only on the start day. I have a query that returns the number of events happening on any given day: SELECT COUNT(*) FROM p_community e WHERE (TO_DAYS(e.date_ends)=0 AND DATE(e.dat...

SQL Many-to-Many Query Problem

I have three tables: videos, videos_categories, and categories. The tables look like this: videos: video_id, title, etc... videos_categories: video_id, category_id categories: category_id, name, etc... In my app, I allow a user to multiselect categories. When they do so, I need to return all videos that are in every selected categor...

Performance-related: How does SQL Server process concurrent queries from multiple connections from a single .NET desktop application?

Single-threaded version description: Program gathers a list of questions. For each question, get model answers, and run each one through a scoring module. Scoring module makes a number of (read-only) database queries. Serial processing, single database connection. I decided to multi-thread the above described program by splittin...

SQL: grouping 2 tables as 1 with join, union, and then?

Here i am again :) I have 5 tables: customers id - name p_orders id - id_customer - code - date p_items id - id_order - description - price and h_orders and h_items, that are exactly the copy of p_orders and p_items. When the p_ tables reach a big amount of rows, i move the oldest to the h_ tables.. they due as history. So, my pro...

Using JXPath to Query a List

I have a simple class (for testing purposes) that I am trying to Query against using JXPath. I create a list of various animal objects, and I want to get an Iterator for: All Animals where type='CAT' All Animals where numLegs = 4 Here is the simple class: public class Animal { private UUID uuid; private int numLegs; ...

Select on 2 tables that contain same field name ?

Hi, I am developing a CMS that using database based on Joomla ! In Joomla db, we have 2 table : +----------+ |Categories| +----------+ id title ... +-------+ |Content| +-------+ id title catid ... I have a query below : SqlQuery q = new Select("*") //.Top("1") .From(JosContent.Schem...