I have contacts that can be in more than one group and have more than one request. I need to simply get contacts for a specific group that have no specific requests. How do I improve the performance of this query: SELECT top 1 con_name , con_id FROM tbl_group_to_contact gc INNER JOIN tbl_contact c ON gc.con_id = c.i...
Hey my first question on SO! Anywho... Still relatively a newb at SQL so I think I might be missing something here. My question is I currently have a table full of phone numbers. I want to have a query where I search for phone numbers that are similar to a list I have. So for example, I want to find phone numbers that begin with '555123...
I'm trying to write a sql function to do something like: SELECT person.id, person.phonenumber FROM person INNER JOIN activity ON person.id = activity.personid WHERE MAX(activity.activitydate) < DATE_SUB(CURDATE(), INTERAVAL 180 DAY); Each time a person is contacted, we create an activity record for them with notes and...
I need to be able to dump my user table inside Drupal to an XML document that I can hit from a path in a browser. e.g. mysite.com/users.php Looking for an easy way to do this, an existing module would be ideal. Not sure if QueryPath does this. Thanks. ...
I'm joining 2 tables. ACCOUNTS account_number region product 1234 100 AB 2345 0 AB 3456 300 CD 4567 0 CD PRODUCTS product region price AB 100 $1.50 AB 0 $1.25 ...
How do you assign the result of an exec call to a variable in sql? I have a stored proc called up_GetBusinessDay, which returns a single date. Can you do something like this: exec @PreviousBusinessDay = dbo.up_GetBusinessDay @Date, -1 Thanks. ...
Can I do this in SQL 2005? SELECT 'C'+inserted.exhid AS ExhId,inserted.exhname AS ExhName,inserted.exhid AS RefID INTO mytable FROM inserted WHERE inserted.altname IS NOT NULL It won't work if the table exists, but will create the table if it is non-existent. How do I get it to insert into an existing table? ...
How do I create a view only if it doesn't exist. If it does exist, I want to drop the view and redefine it. I also want no warnings or errors. ...
Considering this MySQL query: SELECT someColumns FROM someTable WHERE someColumn IN ( value1, value2, value3 ) ... how can I guarantee that the rowset comes out ordered in the exact order of the values given to the IN () clause? I presume this is not guaranteed without giving it an ORDER BY clause, is it? PS.: The values to the...
SELECT Id, QId, UName, Ans, Date, COUNT(*) * 10 as Total FROM question WHERE COUNT(*) DESC GROUP BY UName doesn't work :( ...
I've got a database table with a very large amount of rows. This table represents messages that are logged by a system. Each message has a message type and this is stored it it's own field in the table. I'm writing a website for querying this message log. If I want to search by message type then ideally I would want to have a drop down b...
I'm trying to implement a front end for a reporting solution which is security dependent. The user has 12 levels of nested criteria to select from, the value of each affects all of the values below. So the criteria selection on the page (each is a dropdown) looks something like this: Criteria 1 Criteria 2 ... Criteria 12 There is a...
Consider the following table: People FirstName nvarchar(50) LastName nvarchar(50) Let's assume for the moment that this table has a full-text index on it for both columns. Let's suppose that I wanted to find all of the people named "John Smith" in this table. The following query seems like a perfectly rational way to accomplish thi...
Hi How do i get the year(2009) and month(12) from the string datetime, following give me correct full date but wrong year (1905-07-03 00:00:00.000) and month (1900-01-13 00:00:00.000). I have tried changing YYYY to year and MM to month. Declare @date dateTime; Declare @CurrentYear datetime; Declare @CurrentMonth datetime; Select @date ...
I need to union several tables with the different structure in Microsoft Access. For example I have table: table1 (column_a,column_b,column_c), table2 (column_a,column_c), table3 (column_d) SQL looks like follows: SELECT table1 column_a,column_b,column_c, Null as column_d FROM Table1 UNION SELECT table2 column_a,Null as column_b, co...
Hello, When I select from SQL Server, I want to get a date, but omit the millisecond value, and I want it to be as a date type. So if I have a value 1/1/2009 1:23:11.923, I want to omit the millisecond but retain the date type, so that it will be the value 1/1/2009 1:23:11.000 (I know you really can't omit the millisecond value with a ...
I need to fetch all the rows where the 'zip' field is less than 5 characters. How can I achieve this using only SQL? I google'd first, but only found info on CHAR_LENGTH(). ie, psudeo code: SELECT * FROM users WHERE STRLEN(zip_code) < 5 Thanks! ...
We have a field that contains HTML markup for formatting on the website, but we need to query just the text that should render on screen, not things like CSS tags, tag names, property names, etc. Is there a way to ignore the markup right in the SQL query or Stored Procedure? If there are ways to do this, will we have performance issues ...
It seems to me that you can do the same thing in a SQL query using either NOT EXISTS, NOT IN, or LEFT JOIN WHERE IS NULL. For example: SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2) SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a) SELECT a FROM table1 LEFT JOIN table2 ON table1.a = ta...
How can i append a whitespace character- for example   /   (non-breaking space) to a field value querying oracle server? I tried SELECT myfield || ' ' FROM mytable but this is interpreted as a variable with the name #160. ...