Search Results

Search found 27337 results on 1094 pages for 'sql and the like'.

Page 657/1094 | < Previous Page | 653 654 655 656 657 658 659 660 661 662 663 664  | Next Page >

• MySQL Rating system (calculating average from two tables).

  - by MussuR

  I have two tables, videos and videos_ratings. The videos table has an int videoid field (and many others but those fields are not important I think) and many records. The videos_ratings table has 3 int fields: videoid, rating, rated_by which has many records (multiple records for each fields from the videos table) but not for all records from the videos table. Currently I have the following mysql query: SELECT `videos`.*, avg(`videos_ratings`.`vote`) FROM `videos`, `videos_ratings` WHERE `videos_ratings`.`videoid` = `videos`.`videoid` GROUP BY `videos_ratings`.`videoid` ORDER BY RAND() LIMIT 0, 12 It selects all the records from table videos that have a rating in table video_ratings and calculates the average correctly. But what I need is to select all records from the videos table, no matter if there is a rating for that record or not. And if there aren't any records in the videos_ratings table for that particular videos record, the average function should show 0. Hope someone could understand what I want... :) Thanks!

  Read the article

• How to create unique user key

  - by Grayson Mitchell

  Scenario: I have a fairly generic table (Data), that has an identity column. The data in this table is grouped (lets say by city). The users need an identifier in order for printing on paper forms, etc. The users can only access their cites data, so if they use the identity column for this purpose they will see odd numbers (e.g. a 'New York' user might see 1,37,2028... as the listed keys. Idealy they would see 1,2,3... (or something similar) The problem of course is concurrency, this being a web application you can't just have something like: UserId = Select Count(*)+1 from Data Where City='New York' Has anyone come up with any cunning ways around this problem?

  Read the article

• MySQL subquery and bracketing

  - by text

  Here are my tables respondents: field sample value respondentid : 1 age : 2 gender : male survey_questions: id : 1 question : Q1 answer : sample answer answers: respondentid : 1 question : Q1 answer : 1 --id of survey question I want to display all respondents who answered the certain survey, display all answers and total all the answer and group them according to the age bracket. I tried using this query: SELECT res.Age, res.Gender, answer.id, answer.respondentid, SUM(CASE WHEN res.Gender='Male' THEN 1 else 0 END) AS males, SUM(CASE WHEN res.Gender='Female' THEN 1 else 0 END) AS females, CASE WHEN res.Age < 1 THEN 'age1' WHEN res.Age BETWEEN 1 AND 4 THEN 'age2' WHEN res.Age BETWEEN 4 AND 9 THEN 'age3' WHEN res.Age BETWEEN 10 AND 14 THEN 'age4' WHEN res.Age BETWEEN 15 AND 19 THEN 'age5' WHEN res.Age BETWEEN 20 AND 29 THEN 'age6' WHEN res.Age BETWEEN 30 AND 39 THEN 'age7' WHEN res.Age BETWEEN 40 AND 49 THEN 'age8' ELSE 'age9' END AS ageband FROM Respondents AS res INNER JOIN Answers as answer ON answer.respondentid=res.respondentid INNER JOIN Questions as question ON answer.Answer=question.id WHERE answer.Question='Q1' GROUP BY ageband ORDER BY res.Age ASC I was able to get the data but the listing of all answers are not present. Do I have to subquery SELECT into my current SELECT statement to show the answers? I want to produce something like this: ex: # of Respondents is 3 ages: 2,3 and 6 Question: what are your favorite subjects? Ages 1-4: subject 1: 1 subject 2: 2 subject 3: 2 total respondents for ages 1-4 : 2 Ages 5-10: subject 1: 1 subject 2: 1 subject 3: 0 total respondents for ages 5-10 : 1

  Read the article

• linq .net with dynamically generated controls

  - by Stuart

  This is a very strange problem and i really dont have a clue whats causing it. What is supposed to happen is that a call to the BLL then DAL returns some data via a linq SPROC call. The retunred IMultipleResults object is processed and all results stored in a hashtable. The hashtable is stored in session and then the UI layer uses these results to dynamically generate some gridviews. Easy you would think. But if i run the code i dont get any gridviews. If i take out the call to the BLL and DAL the gridviews appear but with nothing in them? Why is it the page renders correctly when i take out the call to get the data? Thanks.

  Read the article

• Access to Oracle Database with sqlapi C++

  - by Meloun

  Hi, I need to write some data in several database. I choose sqlapi.com I have made it for mysql and mssql. Now I have Problem with Oracle database. I have installed server and client on Ubuntu. In browser it works, but sqlapi says: libnnz10.so: cannot open shared object file: No such file or directory DBMS API Library 'libclntsh.so' loading fails This library is a part of DBMS client installation, not SQLAPI++ Make sure DBMS client is installed and this required library is available for dynamic loading Linux/Unix: 1) The directories in the user's LD_LIBRARY_PATH environment variable 2) The list of libraries cached in /etc/ld.so.cache 3) /usr/lib, followed by /lib There are both of these files depp inside /usr/lib. I have tried a lot of ways to say eclipse path to this folder, but nothing works. Thanks for help.

  Read the article

• How do I get every nth row in a table, or how do I break up a subset of a table into sets or rows of

  - by Jherico

  I have a table of heterogeneous pieces of data identified by a primary key (ID) and a type identifier (TYPE_ID). I would like to be able to perform a query that returns me a set of ranges for a given type broken into even page sizes. For instance, if there are 10,000 records of type '1' and I specify a page size of 1000, I want 10 pairs of numbers back representing values I can use in a BETWEEN clause in subsequent queries to query the DB 1000 records at a time. My initial attempt was something like this select id, rownum from CONTENT_TABLE where type_id = ? and mod(rownum, ?) = 0 But this doesn't work.

  Read the article

• How to join two query in SQL (Oracle)

  - by MAHESH A SONI

  How can I join these queries? SELECT RCTDT, SUM(RCTAMOUNT), COUNT(RCTAMOUNT) FROM RECEIPTS4 WHERE RCTDT BETWEEN '01-nov-2009' AND '30-nov-2009' AND RCTTYPE='CA' AND RCTAMOUNT>0 GROUP BY RCTDT --- SELECT RCTDT, SUM(RCTAMOUNT), COUNT(RCTAMOUNT) FROM RECEIPTS4 WHERE RCTDT BETWEEN '01-nov-2009' AND '30-nov-2009' AND RCTTYPE='CQ' AND RCTAMOUNT>0 GROUP BY RCTDT

  Read the article

• ms-access: difference between control source and row source

  - by every_answer_gets_a_point

  i have learning reports in access and i don't understand the difference between these two concepts. i understand that the control source is the column? but then what is the row source?

  Read the article

• how to reverse the order of words in query or c#

  - by Ranjana

  i have stored in the database as location India,Tamilnadu,Chennai,Annanagar while i bind in the grid view it ll be displaying as 'India,Tamilnadu,Chennai,Annanagar' this format. but i need to be displayed as 'Annanagar,Chennai,Tamilnadu,India' in this format. how to perform this reverse order in query or in c#

  Read the article

• hibernate restrictions.in with and, how to use?

  - by cometta

  I have table like below id, employee_no, survey_no, name 1 test 1 test_name 2 test2 1 test_name2 3 test3 1 test_name3 4 test4 2 test_name4 how to query with Restriction.in by combining below AND into one IN statement? IN[ (if(survey_no==1) && employee_no== 'test') , (if(survey_no==1) && employee_no== 'test2') , ... ]

  Read the article

• Concurrent usage of table causing issues

  - by Sven

  Hello In our current project we are interfacing with a third party data provider. They need to insert data in a table of ours. This inserting can be frequent every 1 min, every 5min, every 30, depends on the amount of new data they need to provide. The use the isolation level read committed. On our end we have an application, windows service, that calls a webservice every 2 minutes to see if there is new data in this table. Our isolation level is repeatable read. We retrieve the records and update a column on these rows. Now the problem is that sometimes this third party provider needs to insert a lot of data, let's say 5000 records. They do this per transaction (5rows per transaction), but they don't close the connection. They do one transaction and then the next untill all records are inserted. This caused issues for our process, we receive a timeout. If this goes on for a long time the database get's completely unstable. For instance, they maybe stopped, but the table somehow still stays unavailable. When I try to do a select on the table, I get several records but at a certain moment I don't get any response anymore. It just says retrieving data but nothing comes anymore until I get a timeout exception. Only solution is to restart the database and then I see the other records. How can we solve this. What is the ideal isolation level setting in this scenario?

  Read the article

• Entity Framework EntityKey / Foreign Key problem.

  - by Ronny176

  Hi, I keep getting the same error: Entities in 'VlaamseOverheidMeterEntities.ObjectMeter' participate in the 'FK_ObjectMeter_Meter' relationship. 0 related 'Meter' were found. 1 'Meter' is expected. I have the following table structure: Meter 1 <- * ObjectMeter * - 1 VO_Object It is always the same scenario: The first meter is added to the database, the second meter gives the error above. I have the following code in my manager: public List<string> addTemporary(string username, string meterNaam, string readingType, string parentID) { Meter meter = new Meter(); VO_Object voObject = objectManager.getObjectByID(parentID); ObjectMeter objMeter = new ObjectMeter(); meter.readingType = (int)Enum.Parse(typeof(ReadingType), readingType); meter.isActive = true; meter.name = meterNaam; meter.startDate = DateTime.Now; meter.endDate = DateTime.Now.AddYears(6000); meter.uniqueIdentifier = "N/A"; meter.meterType = (int)Enum.Parse(typeof(MeterType), "NA"); meter.meterCategory = (int)Enum.Parse(typeof(MeterCategory), "NA"); meter.energyType = (int)Enum.Parse(typeof(EnergyType), "NA"); meter.utilityType = (int)Enum.Parse(typeof(UtilityType), "NA"); meter.unitOfMeasure = (int)Enum.Parse(typeof(UnitOfMeasure), "NA"); objMeter.valid_from = meter.startDate; objMeter.valid_until = meter.endDate; objMeter.Meter = meter; objMeter.VO_Object = voObject; createMeter(meter); List<String> str = new List<string>(); str.Add("" + meter.meterID); str.Add(meter.name); return str; } and this in my Dao Class which links to the database: internal void CreateMeter(Meter _meter) { _entities.AddToMeter(_meter); _entities.SaveChanges(); } Can someone please explain this error? Ronald

  Read the article

• Use count or have a field that tallies?

  - by Dan LaManna

  Fairly simple concept, making an extremely basic message board system and I want users to have a post count. Now I was debating on whether or not to have a tally in their row that is added each time a post by them is created, or subtracted by one each time a post of theirs is deleted. However I'm sure that performing a count query when the post count is requested would be more accurate due to unforseen circumstances (say a thread gets deleted and it doesn't lower their tally properly), however this seems like it would be less efficient to run a query EVERY time their post count is loaded, especially in the case of them having 10 posts on the same page and it lists their post count each post. Thoughts/Advice? Thanks

  Read the article

• Performing Inner Join for Multiple Columns in the Same Table

  - by frankiefrank

  I have a scenario which I'm a bit stuck on. Let's say I have a survey about colors, and I have one table for the color data, and another for people's answers. tbColors color_code , color_name 1 , 'blue' 2 , 'green' 3 , 'yellow' 4 , 'red' tbAnswers answer_id , favorite_color , least_favorite_color , color_im_allergic_to 1 , 1 , 2 3 2 , 3 , 1 4 3 , 1 , 1 2 4 , 2 , 3 4 For display I want to write a SELECT that presents the answers table but using the color_name column from tbColors. I understand the "most stupid" way to do it naming tbColors three times in the FROM section, using a different alias for each column to replace. How would a non-stupid way look?

  Read the article

• Hibernate Native Query problem with named parameters

  - by dime

  I have a problem with Hibernate Native Query. I have one SELECT that selects array slice (PostgreSQL database). The problem is that hibernate recognizes the following part: ":300" from "SELECT my_array[1:300]..." as named parameter and I get the following exception: Not all named parameters have been set. I tried to escape the colon (:) with '\:' , '::' but with no success. The Hibernate version is 3.2

  Read the article

• Oracle: Finding Columns with only null values

  - by Jorge Valois

  I have a table with a lot of columns and a type column. Some columns seem to be always empty for a specific type. I want to create a view for each type and only show the relevant columns for each type. Working under the assumption that if a column has ONLY null values for a specific type, then that columns should not be part of the view, how can you find that out with queries? Is there a SELECT [columnName] FROM [table] WHERE [columnValues] ARE ALL [null] I know I COMPLETELY made it all up above... I'm just trying to get the idea across. Thanks in advance!

  Read the article

• MySQL - Find entries that refer to a specified index.

  - by Conor H

  Hi, So I have a booking system where I have a 'lesson_type' table with 'lesson_type_id' as PK. I have a constraint in place here so I can't delete a lesson_type if there are bookings made for that lesson_type. I would like to be able to determine if this lesson_type_id is being referred to by any entries in the bookings table (or any other table for that matter) so I can notify the user gracefully. i.e. not have a mysql error be thrown when they try and delete a record. What kind of query would I use for this? Thanks.

  Read the article

• JSON - Php/SQL iPhone APP Questions/Help

  - by chance

  Hey group, first time posting here I am somewhat new to the JSON/PHP/mySQL world, but been around iPhone designing for the past few years, though this topic of conversation is a while new area I am entering. I have done JSON iPhone examples that allow me to create a UITableView and display the JSON data into the TableViewCells (CustomCells) and displays the data (NSDictionary)into UILabels The problem I am having now, is that I want an APP that displays this information from the JSON into just a couple of UILabel's on a regular UIViewController and not a UITableView any help would greatly be appreciated, the example I used and learned for JSON and UITABLE was from http://tempered.mobi/%20 I used that example from my app, but incorporated a few other things like CUSTOM cells however now when the USER selects the specific CELL I want it to load specific data from another JSON file, and cannot get it to load in a UILabel or UITextView in a UIViewController HELP :-)

  Read the article

• How can i design a DB where the user can define the fields and types of a detail table in a M-D rela

  - by Simon

  My application has one table called 'events' and each event has approx 30 standard fields, but also user defined fields that could be any name or type, in an 'eventdata' table. Users can define these event data tables, by specifying x number of fields (either text/double/datetime/boolean) and the names of these fields. This 'eventdata' (table) can be different for each 'event'. My current approach is to create a lookup table for the definitions. So if i need to query all 'event' and 'eventdata' per record, i do so in a M-D relaitionship using two queries (i.e. select * from events, then for each record in 'events', select * from 'some table'). Is there a better approach to doing this? I have implemented this so far, but most of my queries require two distinct calls to the DB - i cannot simply join my master 'events' table with different 'eventdata' tables for each record in in 'events'. I guess my main question is: can i join my master table with different detail tables for each record? E.g. SELECT E.*, E.Tablename FROM events E LEFT JOIN 'E.tablename' T ON E._ID = T.ID If not, is there a better way to design my database considering i have no idea on how many user defined fields there may be and what type they will be.

  Read the article

• LINQ2SQL: How to let a column accept null values as zero (0) in Self-Relation table

  - by Remon

  As described in the img, I got a parent-Children relation and since the ParentID not accepting null values (and I can't change to nullabel due to some restriction in the UI I have), how can I remove an existence relation between ReportDataSources in order to change the parent for them (here i want to set the parentId for one of them = 0) how could i do that since i cant change the ParentID directly and setting Parent = null is not valid public void SetReportDataSourceAsMaster(ReportDataSource reportDataSource) { //Some logic - not necessarily for this scenario //Reset Master this.ReportDataSources.ToList().ForEach(rds => rds.IsMaster = false); //Set Master reportDataSource.IsMaster = true; //Set Parent ID for the rest of the Reports data sources this.ReportDataSources.Where(rds => rds.ID != reportDataSource.ID).ToList().ForEach(rds => { //Change Parent ID rds.Parent = reportDataSource; //Remove filttering data rds.FilteringDataMembers.Clear(); //Remove Grouping Data rds.GroupingDataMembers.Clear(); }); //Delete parent HERE THE EXCEPTION THROWN AFTER CALLING SUBMITCHANGES() reportDataSource.Parent = null; //Other logic } Exception thrown after calling submitChanges An attempt was made to remove a relationship between a ReportDataSource and a ReportDataSource. However, one of the relationship's foreign keys (ReportDataSource.ParentID) cannot be set to null.

  Read the article

• SELECT table name that is inside UNION

  - by LexRema

  I have two same tables. I need to union them in such way: SELECT f1,f2, xxx FROM (SELECT * FROM tbl1 UNION ALL SELECT * FROM tbl2) where xxx would query for a table name, where f1 and f2 fields are taken from. Example output: 123 345 'tbl1' -- this rows are from the first table 121 345 'tbl1' 121 345 'tbl1' 123 345 'tbl1' 124 345 'tbl1' 125 345 'tbl2' -- this rows are from the second table 127 345 'tbl2' Thank you in advance.

  Read the article

• MySQL COUNT() multiple columns

  - by liam

  Hello, I'm trying to fetch the most popular tags from all videos in my database (ignoring blank tags). I also need the 'flv' for each tag. I have this working as I want if each video has one tag: SELECT tag_1, flv, COUNT(tag_1) AS tagcount FROM videos WHERE NOT tag_1='' GROUP BY tag_1 ORDER BY tagcount DESC LIMIT 0, 10 However in my database, each video is allowed three tags - tag_1, tag_2 and tag_3. Is there a way to get the most popular tags reading from multiple columns? The record structure is: +-----------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | flv | varchar(150) | YES | | NULL | | | tag_1 | varchar(75) | YES | | NULL | | | tag_2 | varchar(75) | YES | | NULL | | | tag_3 | varchar(75) | YES | | NULL | | +-----------------+--------------+------+-----+---------+----------------+

  Read the article

• .Net 3.5 - SQL : Compatibility issues. Changing attributes automatic

  - by krishnanunni

  I would like to make notice of some wierd thing. I designed an entire package application using VB.Net in Visual Studio 2008. I changed the normal backgrounds to Black, to make a diffrerence. but the problem is i1mm getting changing back ground colors when i install it on other systems.which does have all supports required. What should i do to supress this problem.

  Read the article

• Sum of distinc rows after a 1-many table join

  - by Lock

  I have 2 tables that I am joining. Table 1 has 1-many relationship with table 2. That is, table 2 can return multiple rows for a single row of table 1. Because of this, the records of table 1 is duplicated for as many rows as are on table 2.. which is expected. Now, I have a sum on one of the columns from table 1, but because of the multiple rows that get returned on the join, the sum is obviously multiplying. Is there a way to get this number back to its original number? I tried dividing by the count of rows from table 2 but this didnt quite give me the expected result. Are there any analytical functions that could do this? I almost want something like "if this row has not yet been counted in the sum, add it to the sum"

  Read the article

• how to revert non-corrupted mssql database with stopat

  - by Yisman

  i have a good, working valid non-corrupted database in mssql that i want to revert to a point in time how is that done? the standard RESTORE command requires a full backup as a starting point, and then log backups thereafter. i cant understand why this must be done from a backup. if my db is good and the logs are OK, why cant i just revert with a STOPAT from the live logs in the db? one dba suggested that whenever i want to restore i should THEN make a log backup and then RESTORE with STOPAT. i believe it would work but sounds a little backwards any better ideas? thank you very much

  Read the article

< Previous Page | 653 654 655 656 657 658 659 660 661 662 663 664  | Next Page >