Hello i have table sitizen(id_citizen,name,sname,age,id_city) i try do query select a.name_city,b.name,b.age from city a,citizen b where a.id_city = b.id_city and b.name = 'DAVE' order by b.age desc expect result like this - NY | DAVE | 65 - NY | DAVE | 12 - NY | DAVE | 3 but see result what i can't anderstand - NY | DA...
Will Mssql be even fast when I query on a view opposed to one query? example When I have this view: create view ViewInvoicesWithCustomersName Select * from Invoices left join Customer on Customer.ID=Invoices.CustomerID What will be faster or will it be even fast? a) select * from ViewInvoicesWithCustomersName where customerName="B...
Hi, I would like to know whether I can test some value against a condition in each returned row somehow in the query? e.g.: I have columns: X Y Z -1 1 2 2 2 -1 3 -1 3 I want to use avg() for all values except for -1. I CANNOT use where<> -1 as each row contains it once. ...
Suppose that we have the following partial ER diagram: Notice that the attachments table will subsequently be used for the messages', submissions', assignments', and lectures' attachments. The problem is with the 3 one-to-one relationships between attachments and messages, submissions, and assignments. According to CakePHP's conventi...
Hi, SELECT user.login, book.name FROM user LEFT JOIN book ON user.login = book.author WHERE user.login = 'peter' Now i get: peter book1 peter book2 peter book2 Bu i wish get: peter book1 book2 book2 Database: MySQL Thanks ...
I am trying to find if a certain column requires TRIM function on it. How can I find out if this column in a table has records that have white space either before or after the actual data. ...
Even with the advanced technologies and available database tools (even free alternatives) are available today, it seems that huge number of users are still very comfortable in using Excel IN EVERYTHING! That's why, as a database developer working as one of these users, I am forced to let them use Excel simply because they are very comfor...
DECLARE @xVar XML SET @xVar = '<book genre="security" publicationdate="2002" ISBN="0-7356-1588-2"> <title>Writing Secure Code</title> <author> <firstname>Michael</firstname> <lastname>Howard</lastname> <age>25</age> <birthday>2010-05-17T00:00:00</birthday> </author> <author> <firstname></first...
I am writing my queries in 'standard' / 'generic' SQL so that it runs on any database (MySQL, Postgre etc). I'd like to use DATE_ADD() of MySQL. In Postgre, it would be done with something like '2001-01-01'::timestamp + '1 year'::interval;. Is there a more 'general' way of writing it so that it runs on both MySQL and Postgre (or other...
Hello to everyone, I have 2 different table which is tbl_meter and tbl_machines. I am entering data using tbl_meter every day. I am finding dailiy results with following sql syntax. But I have problem with tbl_machines. There is relation between 2 tables with local_no and machine_no fields. How can I fetch fileds from tbl_machines table...
Is there any performance hit from querying over two (or more) databases on the same MySQL server, compared to if those databases had been merged into one? Background, I have inherited maintenance over a web application that splits its data into three different databases that runs on the same server, one for content, one for users and gr...
I am trying to create a system which would be able to find users with similar favourite movies/books/interests/etc., much like neighbours on last.fm. Users sharing the most mutual interests would have the highest match and would be displayed in user profiles (5 best matches or so). Is there any reasonably fast way to do this? The obviou...
I'm trying to query a table which has a column that contains straight xml data. Within the query I'm querying columns that hold straight data (int, vchar etc) but I'm also querying the xml column. In the xml column i want to grab a value within the xml and return null if it doesn't exist. I have the following query that almost works b...
I have a recursive trigger, that seems to do exactly what I want it to with no recursion, however when I turn recursion on I get the error: "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)" This should not happen, as I expect 2 or maybe 3 levels of nesting, so I need to debug the trigger and work...
Hello I'm using c++ with embedded SQL trying to receive command line arguments as SQL statements. For instance I want to be able to do: ./a.out proceedings(foo#bar) and tokenize the argument into: proceedings, foo, and bar with #, (, and ) as delimiters. Anyways, I was just wondering if there is a way to use brackets in the argument ...
Hi I am using Hibernate / Spring / Maven / MySQL and unit tests with JUnit. Up to yesterday, my testdata persisted in the database even after the test run was completed. I configured the hell out of this day and all of the sudden all data are being deleted after every test run. Quite sure, this is no bug, but a config issue. Nevertheles...
query: SELECT A.USER_ID, A.ROLE_ID, C.SUBGROUP, MAX(A.STATUS_ID) FROM USER_ROLE A, USER B, ROLE C WHERE A.ROLE_ID = C.ROLE_ID AND C.GROUP_ID = 3 AND A.USER_ID = B.USER_ID AND B.TEMPLATE_IND = 'N' AND B.ONAP_PARTCODE IS NULL ...
When I run query number 1: SELECT id, name, COUNT(name) AS count FROM markers WHERE state like 'OR' group by named I get the following results: id name count 14 Alsea 2 76 Applegate Valley 1 3 Ashland 9 64 Beaver 1 26 Bend 1 10 Carlton ...
Lets say I have a table e.g Request No. Type Status --------------------------- 1 New Renewed and then another table Action ID Request No LastUpdated ------------------------------------ 1 1 06-10-2010 2 1 07-14-2010 3 1 09-30-2010 How can I join the secon...
Hello all I am trying to optimize a sql query which is using order by clause. When I use EXPLAIN the query always displays "using filesort". I am applying this query for a group discussion forum where there are tags attached to posts by users. Here are the 3 tables I am using: users, user_tag, tags user_tag is the association mapping ...