sql

Using JOINS in MySQL

I have this query which works perfectly: SELECT * FROM Customer WHERE SacCode IN ( SELECT SacCode FROM SacCode WHERE ResellerCorporateID = 392 ORDER BY SacCode ) AND CustomerID IN ( SELECT CxID FROM CustAppointments WHERE AppRoomID IN ( SELECT AppRoomID FROM ClinicRooms WHERE ClinI...

How best to do a partial text match in SQL Server 2008

I've a large (1TB) table in SQL Server 2008 that looks something like this: ID int | Flag BIT | Notes NTEXT I need to search every row and set the Flag bit to 1 where Notes contains the word flip Is UPDATE Table SET Flag = 1 WHERE Notes LIKE '%flip%' the 'best' way to do it? I'm thinking that this could take days to run on such a...

Is it possible to update an "order" column from within a trigger in MySQL?

We have a table in our system that would benefit from a numeric column so we can easily grab the 1st, 2nd, 3rd records for a job. We could, of course, update this column from the application itself, but I was hoping to do it in the database. The final method must handle cases where users insert data that belongs in the "middle" of the r...

Access/jet equivalent of Oracle's decode

Is there an equivalent for Oracle's decode() in Access (or Jet, for that matter). The problem I am facing is: I should sort (order) a resultset based basically upon a status and a date (with all records having status = 2) at the end. In Oracle I'd go something like select ... from ... where .. order by decode(status, 2, 0, 1)...

Nested Set Model, count items in categories

I've got a Nested Set Model working for my site with items in subcategories and so on. It's working great except for one problem I can't come around. +---------+-----------------------------+ | item_id | item_name | +---------+-----------------------------+ | 1 | Laptop | | 2 | iPod Cla...

Adding text to this line

I'm a novice so please excuse the simplicity of this. I am trying to get a total ammount, trim the excess decimal places than append some text.. it all works until I try ROUND it ROUND(CAST(ServiceFee * COUNT(UserID) AS VARCHAR(20)),0) + CAST(' yen' as VARCHAR(20)) Thanks in advance ...

SELECT command to calculate percentage

I'm trying to get the percentage of each video I have in my database based on its view count against all other videos. I'm then trying to display all the videos from highest view count to lowest, displaying its percentage on its side inside a nice HTML page. Obviously the percentage would range from 0 - 100% (and not over) and the mos...

Issues with Chr(0) in SQL INSERT script

We currently use the SQL Publishing Wizard to back up our database schemas and data, however we have some database tables with hashed passwords that contain the null character (chr(0)). When SQL Publishing Wizard generates the insert data scripts, the null character causes errors when we try and run the resulting SQL - it appears to ign...

Conditional Statements Inside A Where Clause

I have a sproc that runs on many different servers. In the sproc I have a select statement that looks like the following. select * from my_table where id = @my_id and status in (1,3) However, for one of the databases I need the second part of that where clause to only be for statuses equal to 1. So essentially... select * from m...

Parsing excel sheet in C#, inserting new values into database.

I am currently working on a project to parse an excel sheet and insert any values into a database which were not inserted previously. The sheet contains roughly 80 date-value pairs for different names, with an average of about 1500 rows per pair. Each name has 5 date-value pairs entered manually at the end of the week. Over the week...

how to write Query for select statment where column IN

hi, i have write an Query where the table name is student has columns, class, names, Language now i need to write an single query where class='10', names ="kiran, manju, ram , peter", Language='english' how do I write a Query where one column wil have multiple values? Looking frwd for solution thank you ...

Convert number to varchar in SQL with formatting

Is there a way in T-SQL to convert a TINYINT to VARCHAR with custom number formatting? For instance, my TINYINT has a value of 3 and I want to convert it to a VARCH of 03, so that it always shows a 2 digit number. I don't see this ability in the CONVERT function. ...

Database design for a survey

I need to create a survey where answers are stored in a database. I'm just wondering what would be the best way to implement this in the database, specifically the tables required. The survey contains different types of questions. For example: text fields for comments, multiple choice questions, and possibly questions that could contain ...

Best way of searching through a DataGridView in VB.NET?

Hi peeps, On my form there is a datagridview that may get upwards to about 70k items depending on how scan happy our customers get. I also have a textbox which allows the user to search the datagridview using the textchanged event. I'm using a select statement with the like clause and filling the dataset. I dont think this will cut i...

SQL design for survey with answers of different data types

I am working on an online survey. Most questions have a scale of 1-5 for an answer. If we need to add a question to the survey, I use a simple web form, which does an INSERT into the appropriate table, and voila! surveys are asking the new question -- no new code or change to the database structure. We are being asked to add survey ques...

SQL Join Table Naming Convention

I have 2 tables: Users and Roles, and I have a table that joins these together. The only thing in the join table is Ids that link the 2 tables. What should I call this table? I've never really seen a good naming convention for this. Conventions I've seen before: UsersToRolesJoin UsersToRolesLink UsersToRolesMembership UsersRoles Ex...

SELECT TOP 1 in a many to many query

I have a faily simple many to many schema, which looks like this: What I'm trying to do is select all players with some arbitary conditions and I also want to select their most recent match, if they've played in one. What I've managed to do is: SELECT tblPlayer.PlayerId, tblPlayer.Surname, tblPlayer.Forename, (SELECT TOP 1 tblMatch.Ho...

Select rows that are different in SQL

I have a table with way too many columns and a couple million rows that I need to query for differences. On these rows there will hopefully be only one column that is different and that should be the Auto incremented id field. What I need to do is check to see if these rows ARE actually the same and if there are any that have any diffe...

SQLite column aliasing

Premise I recently ran into a bug in a select statement in my code. It was fairly trivial to fix after I realized what was going on, but I'm interested in finding a way to make sure a similar bug doesn't happen again. Here's an example of an offending query: select the, quick, brown fox, jumped, over, the, lazy, dog...

SQL Copy Row for a list, change one column value

I need to duplicate a row a couple thousand times. I need to change one column from the copied row based on a list of ids. Psuedo-code: INSERT INTO MyTable (TabID, Other Columns) VALUES (TabID = (SELECT TabID FROM OtherTable WHERE ParentID = 1), Other Columns) Is this doable? ...