sql

MYSQL - Combining Two Results in One Query

I have a query I need to perform to show search results for a project. What needs to happen, I need to sort the results by the "horsesActiveDate" and this applies to all of them except for any ad with the adtypesID=7. Those results are sorted by date but they must always result after all other ads. So I will have all my ads in the res...

ANDing in join condition???

Is it possible to use AND in a join condition? For example: SELECT * FROM items LEFT JOIN products as prod ON prod.prod_id = items.prod_id LEFT JOIN partial as p ON p.prod_model = prod.prod_model AND p.order_id=$order_id WHERE order_id = $order_id ORDER BY prod.prod_weight ASC I am guessin...

MYSQL Update with 2 INNER JOINs hangs

I am trying to update a field in my table based on if it exists in two join tables. update quotes inner join collection_records ON quotes.id <> collection_records.record_id inner join subcollection_records ON quotes.id <> subcollection_records.record_id set quotes.status_id = 1 where collection_records.type = 'Quote' or subcollectio...

access sql SELECT question

here is my code: select column1, column2 from table1; here is a preview of column1: 1.1 Specimen Mislabeled 1.9 QNS- Specimen Spilled in transit 1.3 QNS-Quantity Not Sufficient 1.6 Test Requisition Missing 1.11 Other - Validity only 1.11 Other-reject per practice 1.5 Patient Info. entered Incorrectly 1.11 Other-Validity 1.11 Other-va...

Tricky SQL for a smart search

I am using a form through a PHP CMS system, that defines custom fields and such. One of the custom fields allows for an input field which does a smart search. What this means is that when you start typing, it shows the records that match, quite similar to google suggest. The smartsearch input field relies on a stored mysql search, and ...

SQL: How to refactor this multi-table delete statement

Can anyone suggest a cleaner method to delete rows with a one-to-many relationship in one query? This works, but I'm not very familiar with the using clause or delete, so I don't fully understand how it works. DELETE FROM ip_record, entry using ip_record inner join entry where ip_record.site_id = ? ...

replace inner join with select from, perhaps?

I am trying to understand and modify an SQL query used by an input field to do a smart search. The original code is here: ((`clients` INNER JOIN `addresstorecord` ON `clients`.`uuid` = `addresstorecord`.`recordid` AND `addresstorecord`. `tabledefid`='tbld:6d290174-8b73-e199-fe6c-bcf3d4b61083' AND addresstorecord.primary='1')...

SQL - Does the order of WHERE conditions matter?

Assume that category_id is an index key (not primary key) of table books. Is there any difference between the following two SQL statements? SELECT * FROM books WHERE author='Bill' AND category_id=1 SELECT * FROM books WHERE category_id=1 AND author='Bill' I guess filtering records first by category_id and then by author is faster tha...

SQL query with duplicate records

Hi. I am trying to write a query in SQL server to find out if there are any multiple rows for each customer by customerID. Please let me know. Here is the table structure Customer table ----------------------------------------- orderID CustName CustomerID --------------------------------------- 100 test ...

access: IIF statement and COUNT

here's my code that works thanks to Jim b: SELECT IIf([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)] Like '*1.11 Other*','1.11 Other',[Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) AS [Occurrence Code], Count([Lab Occurrence Form].[1 0 Preanalytical (Before Testing)]) FROM [Lab Occurrence Form] WHERE ((([Lab Occ...

How to insert the contents of a text file into a table in SQL Server

I have several files (they are XML but that's not important) that need to be inserted into an existing SQL table (i.e. I didn't design it.) The table looks like this. ReportType ID (int) <- identity Name (varchar(32)) TransformXSLT (nvarchar(max)) Normally I would do: INSERT INTO ReportType (Name, TransformXSLT) VALUES ('templa...

ms-access: how to comment sql code out?

is it possible to comment code out in the sql window in access? ...

Oracle date time

I have four fields: Start_Date = 16-Jun-10 Start_Time = 14:37 End_Date = 16-Jun-10 End_Time = 14:38 I need to calculate the difference from start to end. I tried using the to_date(Start_date || Start_Time) but keep getting this error message: ORA-01830: date format picture ends before converting entire input string 01830.00000 ...

Syntax error in automatically formed SQL query

I am trying to generate a query for use with a smart search field, ala google suggest. I can not form the query directly, although I can see what query was made via the logs. I have posted the query that gets sent, which has a syntax error but I am unsure why. SELECT DISTINCT IF(guests.passport != '', Concat(guests.passport, ...

LINQ 2 SQL : Loading more than one result sets to a variable when executing a stored procedure from LIN2 SQL.

In LINQ 2 SQL,I am executing a stored procedure like datacontext.StoredProcedureName(parameter1,paramter2) My sotored procedure is returning 2 different resultsets.How can i catch it and parse over it to grab the values from it VB.NET am working with ...

SQL query to return multiple records

Possible Duplicate: SQL query with duplicate records Hi. I am trying to write a query to return rows from Customer table for multiple orders. Currently I am writing 2 queries to solve this problem. Is there anyway I can put it in one query? //Get all customers select customerID from Customer //For each customer select * f...

Creating a SQL Query

I'm having trouble creating a query. I need to create a SQL query where ProductID = prodSelect (user choice) and EndTime = Null and StartTime < 3 hours from the current time. This is what I have so far. Could someone help me figure it out? Set rs = CurrentDb.OpenRecordset("SELECT TimeID " & _ "FROM tblLunchTime " & _ "WHERE Pr...

SSRS question on calculating dates from a grouped field

I have a SSRS field called dates. I calculated the difference between the last and beginning date using the following code: =Code.MinutesToHoursMinutes(DateDiff("n",FIRST(Fields!Date.Value), LAST(Fields!Date.Value, "grp_Order"))) The Code.MinutesToHoursMinutes is jsut a function that makes it Hours Minutes format. How do I now average...

How does one secure SQL Server Reporting Services (SSRS)?

I've installed SSRS on my development machine and everything works as expected - except anyone else who is logged on to my domain can view all of the reports by browsing to http://mymachine/reports. I find it a bit strange that the default behaviour is for everyone on the domain to have access to the reports. Am I doing anything wrong...

trouble with automatically generated sql query

I am having trouble with an automatically generated SQL query, that is based on several smaller queries stored in a table, and combined with what the user inputs. I will show the inputs for a query that works, and then the query itself. I will then show my inputs, and the query that fails. If someone could point me what input I have u...