I have the following query: DECLARE @IsStocked bit SELECT * FROM Products p WHERE p.LastSeen > GETDATE() - 30 This returns all Products that have been seen within the last 30 days. My question is, I would like the p.LastSeen > GETDATE() - 30 clause to only apply when @IsStocked = true. This is part of a larger query, and I'd like...
Hello. I have two tables. USER user_id password FRIEND_LIST user_id friend_id If user 1 is friend of user 2 then in friend_list there will be 2 records: 1 2 2 1 Thats how I'm controlling friend list. My question is how can I create an efficient query that validates if a user is a friend of a friend. For example user 1 has in his...
I have a datalist that receives values from a MySQL database. This datalist can be sorted by various column values such 'Title', 'Author', 'Published Date', etc. To determine what value to sort by, the value is inserted into the query string. I.e. www.web.com/default.aspx?order_by=title I also determine wheter to order in ascendi...
Assume, we have two tables: "Items" and "Types". The relationships are: item belongs_to type type has_many items Also, the Item table have a column, let's call it "mark". What would be a query (in a Rails secure way if it's possible) to extract all the types from them Types table, wich have connected items in Items table with a "mark"...
I have this query which returns all rows from a table: select Cost, Name, Id from #Table It returns a result set that looks like this: Cost Name Id ---- ---- ---- -2.00 Item1 1 4.00 Item2 1 6.00 Item3 1 3.00 Item1 2 9.00 Item4 2 What I want to do is grab a row from each ID with the highest value, so the 5 ...
I have a table containing access logs. I want to know how many accesses to resource_id '123' occured in each hour in a 24 hour day. My first thought for retrieving this info is just looping through each hour and querying the table in each loop with something like... and time like '$hour:%', given that the time field holds data in the f...
Are the following queries effective in MySQL: SELECT * FROM table WHERE field & number = number; # to find values with superset of number's bits SELECT * FROM table WHERE field | number = number; # to find values with subset of number's bits ...if an index for the field has been created? If not, is there a way to make it run faste...
Hi, Sorry about the title. That was the best I could come up with. Anyways, here is my question - I have 2 tables, a Customer table and Order table as shown below: Customer { Long id; String name; String address; Timestamp createdOn; } Order { Long id; String productName; Long customerId; Timestamp createdOn; } Ther...
Okay so this is another question about a previous question I asked: http://stackoverflow.com/questions/1435802/rookie-sql-inside-of-vb-question-msaccess-2003 I am pretty new at this so I appreciate the help guys! So lets say I have a form and I want the SQL string in the VB to fill out a form based on parameters selected by the user o...
I have a Drupal site that currently displays comments in threaded, reverse chronological order. I am trying to change it to work like Reddit/Hacker News, where the threads and comments within each thread are instead ordered by their current score, based on a voting system I've added. I've found the query used to render the comments rig...
This is Sql Server 2008. I've got a set of data that looks something like this: Table UserAchievement id userId achievementId completedDate When a user earns an award, the award and user is logged along with a date. What I'd like is a query that finds a set of 3 achievements that were earned within 5 minutes of each other ...
I want to know how to limit the MySQL result. I can use "select * from students" to show all the students, but it is too many. I can add where condition, but it still many students after I use where to filter the result. Is it possible to return first 100 students, and order by first name. And next time, I want the first 101-200 ... ... ...
I have the following query that looks for transactions that cancel themselves out from the same customer (Some transactions are negative). SELECT c, ABS(r) magnitude, SUM(r) total, COUNT(*) num FROM table GROUP BY c, magnitude HAVING num > 1 AND total = 0 ORDER BY total The result of this query is the customer id, the magnitude of the...
Is this efficient? How could it be improved? I was trying to get data from one set of columns into two sets of columns according to a condition. All the methods I tried ended up with multiple rows. This is very similar to this other question but a bit more complicated. Here's what I did: (Because this is for iReport/JasperReports, it...
Hi all, I have a query which gives back an array, I'd like to be able to merge the array values which have the same "clientid" Here's my query: SELECT * FROM #__db_clients_trip WHERE clientid IN (1999, 2984, 1681) AND companyid IN (1,2) Here's my array: stdClass Object ( [id] => 61 [trip_code] => EUR0600 [clientid] => 19...
I have a customer table: id name 1 customer1 2 customer2 3 customer3 and a transaction table: id customer amount type 1 1 10 type1 2 1 15 type1 3 1 15 type2 4 2 60 type2 5 3 23 type1 What I want my query to return is the follo...
Hi everybody, I have a tree / ancestor / query problem I'm not able to solve: I have a table holding menu data and a table containing all the ancestors of the menu: table menu table ancestors +-----+------------+--------+ +---------+--------------+-------+ | id | title | active | | menu_id | ancestor_id | l...
Hi, is there a simple way to get the number of the current item in a simple SELECT? I need to deliver a column based on a calculation that involves the number of the current index in the select. I am simplifing my problem to an extreme, but roughly speaking, here is an example: SELECT column1 * ITEMINDEX FROM table I hope I am being c...
Let me try to explain this to the best of my ability: I have a table in which there is a userID column and a program column (along with other columns of non importance). I need to find the users within this table that have multiple instances within this table where that user has a program of X associated with it. Can anyone help me ple...
here is the whole picture. There is a table (table programparticipants) that stores all participants of all programs, there is another table (table programs) that stores all the programs. What I need to accomplish is the following: Acquire the programs where a user has attended more than one program (done) Acquire the other programs (f...