Hi, I hope someone can help me. I need to solve a problem with a view that get me the correct result based in the following scenario. I have two tables: Language and ReportDescription Language Id Tag Description ---- ---- ---------------- 1 en English 2 de German 3 fr French 4 it Italian ReportDe...
One of my friend told me about Statement 2, but thus far I have used Statement 1. Is there any difference between these two SELECT statements? If so, what is it? Statement 1: SELECT Col1 FROM Table1; Statement 2: SELECT Table1.Col1 FROM Table1; ...
I have the following tables: Products product, idcurrency, value Prod1 1 5000 Prod2 2 3000 Prod3 3 2000 Currrency idcurrency, currencyID 1 EUR 2 USD 3 DKK ForexExchange idforexExchange providername, idbaseCurrency, isActive 1 ...
Possible Duplicate: How to empty a SQL database? What is the fastest way to delete all records from all tables in the db supposing they do not have too much data(may be a few records in some tables but no more)? I believe that recreate the database from structure dump is much longer variant. ...
How to get the sql generated by LinqToSql for update method? I use the following code to show the sql generated by LinqToSql in VS2008's debug output window, but it only gets the sql select method generated, how can I find the sql update method that was generated by LinqToSql? I know the Sql Server Profiler and LinqPad can get it(th...
I have Table Students with columns Student ID and Student Name. I also have Table Events with columns ID, Student ID and Event. Now, i want to write a query that will return only rows from Students Table that has its Student ID present in Events Table. I am running MySql 5+. ...
I need a db to store, i.e. user records. Regular stuff: name, e-mail, address, phone, fax and so on. The problem is, in this case there can be more than one phone number per user. And more than one e-mail. Even more than one address. And a lot more of more-than-one stuff. One approach is to store everything in one table, e.g. serialize...
How can I return from float/decimal value with the following: SELECT 210 FROM DUAL ...to get: 210.00 ...instead of 210? Using pl/sql oracle 10g ...
Newbie SQL question here --> I've got an Occurrences table that contains a row for each time a user did something. A user can do the thing multiple times per day. It looks like this: Date Username ------ -------- 1/1/9 User1 1/1/9 User1 1/1/9 User2 1/2/9 User1 1/2/9 User3 1/3/9 User1 1/3/9 User1 1/...
Users send messages to each others message boxes, how to find the user who knows the most other users (receive AND send??). I have SQL Server. Table: USER_ID USER_NAME USER_GROUP MESSAGE_ID MESSAGE_MESSAGE MESSAGE_FROM_USER M_ESSAGE_TO_USER A user can send and receive, so who knows the most other users (by send AND receive??) ...
I have another quick question about SQLAlchemy. If I have added a query_property [1] field in my SQLAlchemy Table class, is it possible to narrow down the SELECTed fields? Here is what I mean. Suppose my class Comments has this: class Comments: query = Session.query_property() ... Then if I do the following: >>> print Ses...
I have a query like this: SELECT `*` FROM (`threads` t, `members` m) WHERE `m`.`id` = t.author AND `t`.`type` = '0' AND `t`.`category` = '1' And basically what happens is that there is an ID field in both tables (members and threads) so what's happening is that the results array is getting messed up. IE: There is only one...
I was wondering if I could get some feedback on the relational algebra I have done and any advice on improving it also if you see anything wrong with my SQL could you possibly point it out. SELECT CourseName FROM Course WHERE CourseNo = 6; ∏CourseName(σCourseNo=6(Course)) SELECT CU.UnitNo FROM C.Course, CU CourseUnit WHERE C.CourseNa...
I need to delete a row based upon the value in a column while executing an update query. Here is the code: UPDATE tag SET tag_count = tag_count - 1 WHERE tag_id = 1 IF tag_count < 1 delete from tag where tag_id = 1 This query here gives me an error. EDIT I am using inline sql with C# and sql server ...
The following query: SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) GROUP BY id, year ORDER BY id, rate DESC yields: year id rate 2006 p01 8 2003 p01 7.4 2008 p01 6.8 2001 p01 5.9 2007 p01 5.3 2009 p01 4.4 2002 p01 3.9 2004 p01 3.5 2005 p01 2.1 2000 p...
Basically we have one table (original table) and it is backed up into another table (backup table); thus the two tables have exactly the same schema. At the beginning both tables (original table and backup table) contains exactly the same set of data. After sometime for some reason I need to verify whether dataset in the original table...
Hey guys, I'm programming in PHP5 & MySQL5 and I'd like to know how to figure out the index of an entry. To be clear, here's an example: I have 500 users in a table, I want to select the user johndoe and the index of that user, so if johndoe is the 100th user in my table, I want to be able to select the information for him in a similar w...
Hi, I have written an SQL query that gets unread messages but I think I can improve the code (speed and readability). The first select is for COUNT function, the second is for grouping messages according to conversation_id, and the final nested select is for selecting last messages. Please give me suggestions. Thanks in advance. SELEC...
In a scenario where I have a table like so: int id (PK) int staff_id int skill_id bit mainskill I want to select only ONE record for each staff member (represented by staff_id) listing their main skill as represented by a (1) in mainskill. If no main skill is present, I want to return any of the skill records for that staff member. Fo...
I've created an application that finds service providers based on multiple services passed to it. This is done via has many_through so there is a join object. Basically, if a user asks for service providers with Service A and Service B my scope returns service providers who offer either BOTH Service A and Service B or other service provi...