Search Results

Search found 31931 results on 1278 pages for 'sql statement'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 700/1278 | < Previous Page | 696 697 698 699 700 701 702 703 704 705 706 707  | Next Page >

  • “Function” calling inside store procedure

    - by idimba
    Hi, I have a big store procedure, that contains a lot of INSERTs. There're many INSERTS that almost identical - they're different by some parameter(s) (all INSERTs to the same table) Is there a way to create a function/method, to which I'll pass the above parameter(s) and the function/method will generate concrete INSERT's? Thanks

    Read the article

  • MySQL COUNT() total posts within a specific criteria?

    - by newbtophp
    Hey, I've been losing my hair trying to figure out what I'm doing wrong, let me explain abit about my MySQL structure (so you get a better understanding) before I go straight to the question. I have a simple PHP forum and I have a column in both tables (for posts and topics) named 'deleted' if it equals 0 that means its displayed (considered not deleted/exists) or if it equals 1 it hidden (considered deleted/doesn't exist) - bool/lean. Now, the 'specific criteria' I'm on about...I'm wanting to get a total post count within a specific forum using its id (forum_id), ensuring it only counts posts which are not deleted (deleted = 0) and their parent topics are not deleted either (deleted = 0). The column/table names are self explanatory (see my efforts below for them - if needed). I've tried the following (using a 'simple' JOIN): SELECT COUNT(t1.post_id) FROM forum_posts AS t1, forum_topics AS t2 WHERE t1.forum_id = '{$forum_id}' AND t1.deleted = 0 AND t1.topic_id = t2.topic_id AND t2.deleted = 0 LIMIT 1 I've also tried this (using a Subquery): SELECT COUNT(t1.post_id) FROM forum_posts AS t1 WHERE t1.forum_id = '{$forum_id}' AND t1.deleted = 0 AND (SELECT deleted FROM forum_topics WHERE topic_id = t1.topic_id) = 0 LIMIT 1 But both don't comply with the specific criteria. Appreciate all help! :)

    Read the article

  • Replace always replacing null values

    - by Mike
    Why does left(FIELD, replace(nullif(charindex('-', FIELD), 0), null, len(FIELD))) always return null? The idea behind the query is that if charindex() returns 0, then convert the results into null, then convert the null into the length of the field. So if '-' does not exist, show the whole string. For some reason it makes every row equal null. Thank you.

    Read the article

  • Tree structured resource Authorization

    - by user323883
    I have portfolio table with portoflio_id and parent_portfolio_id and I have user table now some users may have access to all portfolios, or selective portfolios or depending on group, everything under a portfolio tree. can someone suggest a good schema or any existing framework

    Read the article

  • How do I do a table join on two fields in my second table?

    - by Cannonade
    I have two tables: Messages - Amongst other things, has a to_id and a from_id field. People - Has a corresponding person_id I am trying to figure out how to do the following in a single linq query: Give me all messages that have been sent to and from person x (idself). I had a couple of cracks at this. Not quite right MsgPeople = (from p in db.people join m in db.messages on p.person_id equals m.from_id where (m.from_id == idself || m.to_id == idself) orderby p.name descending select p).Distinct(); This almost works, except I think it misses one case: "people who have never received a message, just sent one to me" How this works in my head So what I really need is something like: join m in db.messages on (p.people_id equals m.from_id or p.people_id equals m.to_id) Gets me a subset of the people I am after It seems you can't do that. I have tried a few other options, like doing two joins: MsgPeople = (from p in db.people join m in AllMessages on p.person_id equals m.from_id join m2 in AllMessages on p.person_id equals m2.to_id where (m2.from_id == idself || m.to_id == idself) orderby p.name descending select p).Distinct(); but this gives me a subset of the results I need, I guess something to do with the order the joins are resolved. My understanding of LINQ (and perhaps even database theory) is embarrassingly superficial and I look forward to having some light shed on my problem.

    Read the article

  • Is there a better way to do updates in LinqToSQL?

    - by Vaccano
    I have a list (that comes to my middleware app from the client) that I need to put in my database. Some items in the list may already be in the db (just need an update). Others are new inserts. This turns out to be much harder than I thought I would be. Here is my code to do that. I am hoping there is a better way: public void InsertClients(List<Client> clients) { var comparer = new LambdaComparer<Client>((x, y) => x.Id == y.Id); // Get a listing of all the ones we will be updating var alreadyInDB = ctx.Clients .Where(client => clients.Contains(client, comparer)); // Update the changes for those already in the db foreach (Client clientDB in alreadyInDB) { var clientDBClosure = clientDB; Client clientParam = clients.Find(x => x.Id == clientDBClosure.Id); clientDB.ArrivalTime = clientParam.ArrivalTime; clientDB.ClientId = clientParam.ClientId; clientDB.ClientName = clientParam.ClientName; clientDB.ClientEventTime = clientParam.ClientEventTime; clientDB.EmployeeCount = clientParam.EmployeeCount; clientDB.ManagerId = clientParam.ManagerId; } // Get a list of all clients that are not in my the database. var notInDB = clients.Where(x => alreadyInDB.Contains(x, comparer) == false); ctx.Clients.InsertAllOnSubmit(notInDB); ctx.SubmitChanges(); } This seems like a lot of work to do a simple update. But maybe I am just spoiled. Anyway, if there is a easier way to do this please let me know. Note: If you are curious the code to the LambdaComparer is here: http://gist.github.com/335780#file_lambda_comparer.cs

    Read the article

  • Changing newid() to newsequentialid() on an existing table

    - by cbp
    Hi, At the moment we have a number of tables that are using newid() on the primary key. This is causing large amounts of fragmentation. So I would like to change the column to use newsequentialid() instead. I imagine that the existing data will remain quite fragmented but the new data will be less fragmented. This would imply that I should perhaps wait some time before changing the PK index from non-clustered to clustered. My question is, does anyone have experience doing this? Is there anything I have overlooked that I should be careful of?

    Read the article

  • How to get only one record for each duplicate rows of the id in oracle?

    - by Psychocryo
    suppose i have this table: group_id | image | image_id | ----------------------------- 23 blob 1 23 blob 2 23 blob 3 21 blob 4 21 blob 5 25 blob 6 25 blob 7 how to get results of only 1 of each group id? in this case,there may be multiple images for one group id, i just want one result of each group_id i tried distinct but i will only get group_id. max for image also would not work.

    Read the article

  • it is possible to "group by" without losing the original rows?

    - by toPeerOrNotToPeer
    i have a query like this: ID | name | commentsCount 1 | mysql for dummies | 33 2 | mysql beginners guide | 22 SELECT ..., commentsCount // will return 33 for first row, 22 for second one FROM mycontents WHERE name LIKE "%mysql%" also i want to know the total of comments, of all rows: SELECT ..., SUM(commentsCount) AS commentsCountAggregate // should return 55 FROM mycontents WHERE name LIKE "%mysql%" but this one obviously returns a single row with the total. now i want to merge these two queries in one single only, because my actual query is very heavy to execute (it uses boolean full text search, substring offset search, and sadly lot more), then i don't want to execute it twice is there a way to get the total of comments without making the SELECT twice? !! custom functions are welcome !! also variable usage is welcome, i never used them...

    Read the article

  • What is your reporting tool of choice?

    - by jms
    Every project invariably needs some type of reporting functionality. From a foreach loop in your language of choice to a full blow BI platform. To get the job done what tools, widgets, platforms has the group used with success, frustration and failure?

    Read the article

  • Does UNIQ constraint mean also an index on that field(s)?

    - by Gremo
    As title, should i defined a separate index on email column (for searching purposes) or the index is "automatically" added along with UNIQ_EMAIL_USER constraint? CREATE TABLE IF NOT EXISTS `customer` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `first` varchar(255) NOT NULL, `last` varchar(255) NOT NULL, `slug` varchar(255) NOT NULL, `email` varchar(255) NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `UNIQ_SLUG` (`slug`), UNIQUE KEY `UNIQ_EMAIL_USER` (`email`,`user_id`), KEY `IDX_USER` (`user_id`) ) ENGINE=InnoDB;

    Read the article

  • ASP.NET Template Selector/Builder - Dynamic CMS

    - by Ugene
    I am currently building my own CMS for various reasons that could take a long to explain... However i am looking for a dynamic solution to create templates for pages within the CMS and all areas must be editable via the administration area, maybe large text areas broken into multiple areas, text and image area on a page etc. Following on from the above i would like to create the following: Create a new page (selecting a pre-defined template like below) http://img525.imageshack.us/img525/9872/nestedpages.png and then upon editing the page it would have created as many text editors required for each editable region or a file upload control for an image area for example. i am thinking of using nested masterpages for the design elements, just unsure the best-practice way to achieve the above (db structure etc) I somehow hope this provides enough information but are happy to answer any questions you may have. Thanks

    Read the article

  • Data storage advice needed: Best way to store location + time data?

    - by sobedai
    I have a project in mind that will require the majority of queries to be keyed off of lat/long as well as date + time. Initially, I was thinking of a standard RDBMS where lat, long, and the datetime field are properly indexed. Then, I began thinking of a document based system where the document was essentially a timestamp and each document has lat/long with in it. Each document could have n objects associated with it. I'm looking for advice on what would be the best type of storage engine for this sort of thing is - which of the above idea would be better or if there is something else completely that is the ideal solution. Thanks

    Read the article

  • Conditional sorting in MySQL?

    - by serg555
    I have "tasks" table with 3 fields: date priority (0,1,2) done (0,1) What I am trying to achieve is with the whole table sorted by done flag, tasks that are not done should be sorted by priority, while tasks that are done should be sorted by date: Select * from tasks order by done asc If done=0 additionally order by priority desc If done=1 additionally order by date desc Is it possible to do this in MySQL without unions? Thanks.

    Read the article

  • Voting Script, Possiblity of Simplifying Database Queries

    - by Sev
    I have a voting script which stores the post_id and the user_id in a table, to determine whether a particular user has already voted on a post and disallow them in the future. To do that, I am doing the following 3 queries. SELECT user_id, post_id from votes_table where postid=? AND user_id=? If that returns no rows, then: UPDATE post_table set votecount = votecount-1 where post_id = ? Then SELECT votecount from post where post_id=? To display the new votecount on the web page Any better way to do this? 3 queries are seriously slowing down the user's voting experience

    Read the article

  • Multiple LIKE, OR MySql Queries Match

    - by Codex73
    Search for: 'chemist' Problem: query which will match a string like 'onechemist' but not 'chemist'. SELECT id,name FROM `records` WHERE name LIKE '%". mysql_real_escape_string($q) ."%' This alternate try won't work: SELECT id,name FROM `records` WHERE name LIKE '%". mysql_real_escape_string($q) ."%' OR name LIKE '". mysql_real_escape_string($q) ."%' OR name LIKE '%". mysql_real_escape_string($q) ."' How could I compile the above into one single query that will match any field which has the string or optimize the query into a better expression?

    Read the article

  • custom sorting or ordering a table without resorting the whole shebang

    - by fuugus
    for ten years we've been using the same custom sorting on our tables, i'm wondering if there is another solution which involves fewer updates, especially since today we'd like to have a replication/publication date and would'nt like to have our replication replicate unnecessary entries. i had a look into nested sets, but it does'nt seem to do the job for us. base table: id | a_sort ---+------- 1 10 2 20 3 30 after inserting insert into table (a_sort) values(15) an entry at the second position. id | a_sort ---+------- 1 10 2 20 3 30 4 15 ordering the table with select * from table order by a_sort and resorting all the a_sort entries, updating at least id=(2,3,4) will of course produce the desired output id | a_sort ---+------- 1 10 4 20 2 30 3 40 the column names, the column count, datatypes, a possible join, possible triggers or the way the resorting is done is/are irrelevant to the problem. also we've found some pretty neat ways to do this task fast. only; how the heck can we reduce the updates in the db to 1 or 2 max. seems like an awfully common problem. the captain obvious in me thougth once "use an a_sort float(53), insert using a fixed value of ordervaluefirstentry+abs(ordervaluefirstentry-ordervaluenextentry)/2".. but this would only allow around 1040 "in between" entries - so never resorting seems a bit problematic ;)

    Read the article

  • Design pattern for data entry forms with LINQ2SQL

    - by petebob796
    I am about to start a new winforms data entry application, it already has the database designed which I am comfortable with. I was going to use LINQ2SQL to access the tables to keep things type safe but am now wondering about design patterns, something I am just getting into. Since LINQ is giving me objects to use should I still create classes in between to hold the validation code and helper methods or should these just go in with the UI? It just seems I will end up with classes sat in between with little code which will cause the UI classes to have code just getting and setting values in the intermediate class and returning from validation to flag errors... Any good reading on this? Should I consider the entity framework (or similar) instead?

    Read the article

  • Broken count(*) after adding LEFT JOIN

    - by Iain Urquhart
    Since adding the LEFT JOIN to the query below, the count(*) has been returning some strange values, it seems to have added the total rows returned in the query to the 'level': SELECT `n`.*, exp_channel_titles.*, round((`n`.`rgt` - `n`.`lft` - 1) / 2, 0) AS childs, count(*) - 1 + (`n`.`lft` > 1) + 1 AS level, ((min(`p`.`rgt`) - `n`.`rgt` - (`n`.`lft` > 1)) / 2) > 0 AS lower, (((`n`.`lft` - max(`p`.`lft`) > 1))) AS upper FROM `exp_node_tree_6` `n` LEFT JOIN `exp_channel_titles` ON (`n`.`entry_id`=`exp_channel_titles`.`entry_id`), `exp_node_tree_6` `p`, `exp_node_tree_6` WHERE `n`.`lft` BETWEEN `p`.`lft` AND `p`.`rgt` AND ( `p`.`node_id` != `n`.`node_id` OR `n`.`lft` = 1 ) GROUP BY `n`.`node_id` ORDER BY `n`.`lft` I'm totally stumped... Thank you!

    Read the article

  • Merging rows with uniqueness constraints

    - by Flambino
    I've got a little time-tracking web app (implemented in Rails 3.2.8 & MySQL). The app has several users who add their time to specific tasks, on a given date. The system is set up so a user can only have 1 time entry (i.e. row) per task per date. I.e. if you add time twice on the same task and date, it'll add time to the existing row, rather than create a new one. Now I'm looking to merge 2 tasks. In the simplest terms, merging task ID 2 into task ID 1 would take this time | user_id | task_id | date ------+----------+----------+----------- 10 | 1 | 1 | 2012-10-29 15 | 2 | 1 | 2012-10-29 10 | 1 | 2 | 2012-10-29 5 | 3 | 2 | 2012-10-29 and change it into this time | user_id | task_id | date ------+----------+----------+----------- 20 | 1 | 1 | 2012-10-29 <-- time values merged (summed) 15 | 2 | 1 | 2012-10-29 <-- no change 5 | 3 | 1 | 2012-10-29 <-- task_id changed (no merging necessary) I.e. merge by summing the time values, where the given user_id/date/task combo would conflict. I figure I can use a unique constraint to do a ON DUPLICATE KEY UPDATE ... if I do an insert for every task_id=2 entry. But that seems pretty inelegant. I've also tried to figure a way to first update all the rows in task 1 with the summed-up times, but I can't quite figure that one out. Any ideas?

    Read the article

  • select row from table and substitute a field with one from another column if it exists

    - by EarthMind
    I'm trying construct a PostgreSQL query that does the following but so far my efforts have been in vain. Problem: There are two tables: A and B. I'd like to select all columns from table A (having columns: id, name, description) and substitute the "A.name" column with the value of the column "B.title" from table B (having columns: id, table_A_id title, langcode) where B.table_A_id is 5 and B.langcode is "nl" (if there are any rows). My attempts: SELECT A.name, case when exists(select title from B where table_A_id = 5 and langcode= 'nl') then B.title else A.name END FROM A, B WHERE A.id = 5 and B.table_A_id = 5 and B.langcode = 'nl' -- second try: SELECT COALESCE(B.title, A.name) as name from A, B where A.id = 5 and B.table_A_id = 5 and exists(select title from B where table_A_id = 5 and langcode= 'nl') I've tried using a CASE and COALESCE() but failed due to my inexperience with both concepts. Thanks in advance.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 696 697 698 699 700 701 702 703 704 705 706 707  | Next Page >