sql

using group_concat with other selection criteria - mySQL

I have two tables, arrc_PurchActivity and arrc_Voucher. The purchase activity table contains multiple records per purchase, tied together by the credit card authorization. I need to return a single row per purchase, plus pull in a field from the voucher table. If I just grab a few fields from the purchase activity table, like this: SEL...

Can I create a 'reserved word' for a commonly used string in an SQL statement?

This is not a calculation or a function, it is simply a command I have to add often which is difficult to remember. I am often running queries on data across two databases (one old, one new) so I have to add "collate SQL_Latin1_General_CP1_CI_AS" wherever values of one are being compared with values of the other. For example... ...wh...

Can you query two tables in one statement in Android?

I'm trying to reduce the number of queries I do to the Android's database. Basically I want to get any email or IM address that contains a user defined search term. Right now I'm doing two separate queries: Email: Cursor emails = cr.query( ContactsContract.CommonDataKinds.Email.CONTENT_URI, null, ContactsContract.Commo...

DB Table Optimization join vs repeat columns

This is more of a preference but I was wondering what people think would be the optimal option to perform. I have a Question, Answer, and Point (because I need to track which user made the point) Table Dump Question: id title Answer: id question_id user_id response Point_Answer: id answer_id user_id points So in...

Sharing rules between javascript and SQL

I'm mostly looking for ideas here. The odds of my group purchasing something to solve the problem is incredibly low, but go ahead and suggest any commercial products that would help. The basic problem stems from the following scenario: Users input data into a form, which auto-populates some values based on existing data. This data is...

relational algebra to SQL converter or vice versa

i would like to check if my relational algebra statements are correct, so is there a program out there that outputs relational algebra equivalent of an SQL query? ...

ms logparser export to sql statements

is it possible to use the logparser commandline utility to output sql statements as text? it seems bent on actually connecting to sqlserver and performing the inserts automatically. i just want a text file with the table creation and insert statements so that i can run them against sqlite instead of sqlserver. or if you know how to ge...

MySQL search for text in html text field?

There is a CKEditor field on our website which saves it's HTML to a text field in a MySQL database. What I'm wondering is, if there is a function (regex for example?) that could strip out HTML tags when doing a LIKE match, e.g. so that searching for: like '%this is a test%' would find 'this<strong>is</strong>a test I get the feel...

how can I add 100 years to a date in Advantage Database

I have a bunch of records in my Advantage Database that ended up with the year 1909, rather than 2009. How can run an update statement that will add 100 years to each of these dates? (Advantage is telling me that there is no "str()" function, and it won't let me concatenate month(mydate) with "/". ...

In an UPDATE trigger - get the Primary Key without the column's name

I need to update the DateModified Column without knowing the name of the Primary Key Column. Basically, I've got a plain-jane UPDATE trigger like this: CREATE TRIGGER updated_SCHEMA_TABLE ON [SCHEMA].[TABLE] AFTER UPDATE AS BEGIN SET NOCOUNT ON; UPDATE [SCHEMA].[TABLE] SET DateModified = getdate() ...

SQL query returning list of data that needs to be grouped

I am trying to pull some data out of our testing database with an sql query and i am stuck. Below is a representation of how the data looks when I run it and how it should look once it is run. What I get Sample_Number Centre_Code DateEntered AssayName --------------------------------------------------- 1 234 9...

is this stored procedure correct?

I have a stored procedure below, and I don't know if it's correct. I am inserting records in table PlanFinder.InvalidAwps, and deleting the same records from the PlanFinder.NdcAwp table. Also, can anybody help me with adding try catch in the same stored procedure? Alter procedure PlanFinder.InsertInvalidRecords as Truncate table [P...

mysql JOIN, how is this interpreted?

If I write a sql query for mysql and I just specify JOIN (no outer, inner, left, etc), what type of join does this default to? For example: SELECT count(*) FROM Students p JOIN Classes c ON p.studentId = c.studentId ...

sql help 2 inserts in one procedure

Thanks everyone for the feedback Hi All, I'm am trying to create a stored procedure that does two inserts into two different tables. DECLARE @New_Group1_Id DECLARE @New_Group2_Id INSERT INTO Group1 (Group1_Desc) VALUES (N'Indianapolis') SELECT @New_Group1_Id = Scope_Identity() INSERT INTO Group2 ...

Create a UDT that has SQL @tables as its members

I wonder whether it is possible to create a CLR user-defined type in SQL Server 2008 members of which would be table variables. You know, you can declare a table variable: declare @foo table (row_id int not null ... ); So I fancy a UDT with several members, each of which is a table defined that way (a different table, of course), so ...

SQL Statement (JOIN)

I have the following MySql Table: -------------------------------------------- Table 'category' -------------------------------------------- category_id name parent_id 1 animal NULL 2 vegetable NULL 3 mineral NULL 4 dog 1 6 cat 1 7 ...

SQL Query Assistance

This is not a homework question, just something at work that's bugging me. I'm trying to write a query against these tables below. The two tables, history and code are joined to the historyassignment table by the historyid and codeid columns. Table: HistoryAssignment | AssignmentID | HistoryID | CodeID | |--------------|-----------...

Selecting Multiple Distinct Values in my Database [pls Help]

I have this online quiz wherein it generates random questions but the only problem that's left is that it repeats the previous questions... i have this limited questions (i.e i have 10 questions in my table but my limited questions is set to be 5 ..the output would only display 5 random questions) which i named as RequiredRecords ques...

Database driven 'scheduled tasks' application in C# - need some advice re cron syntax

Hi all, For a work project I am undertaking a task that will see me create a C#.NET Windows Service that will run 24/7. The program will essentially simply move files (based on a given path and a regex) between various locations - from/to HTTP, Network Paths and FTP. The administrator will schedule these jobs via an administration page...

SQL Query to show 3 user names from userID's in one Bug record.

I would like to show all the usernames who have interacted with a bug in MS Access. I do not understand how to reference the same Users.UserName field 3 times in my select statement? Here are my tables -------------------------------------------- Table 'Bugs' -------------------------------------------- BugID OpendBy ClosedBy ...