I'm working with some code. There are several queries whose effect is, if the row exists with some of the data filled in, then that row is updated with the rest of the data, and if the row does not exist, a new one is created. They look like this: INSERT INTO table_name (col1, col2, col3) SELECT %s AS COL1, %s AS COL2, %s AS COL3 FROM ...
I'm trying to figure out the best way to setup some tables in my database. I will be saving data about users in one table and navigations in another. I will simplify the tables. I have two tables at the moment, User that has User_ID and User_Name. Navigation that has Nav_ID and Nav_Name. What i'm thinking about is that Every user can...
This seems very basic but I can't figure it out. I've got a table "item_tags", and I want to select all of the items that match tags 1 and 2 (as in, each item has to have both tags). How would I do this in mysql? Create table is: CREATE TABLE `item_tags` ( `uid_local` int(11) NOT NULL DEFAULT '0', `uid_foreign` int(11) NOT NULL D...
I have an application that uses DBF files and I need to import them to SQL Server 2008. However, I also need to modify some of the data along the way and some columns will be added to tables while others will be deprecated. So far I'm using DBF -> Access -> MS Migration Assistant -> SQL Server 2008. But I think that there has to be a be...
Hello all, I am trying to translate this: SELECT IDNum, Max(Convert(varchar(20), (TimeOut - TimeIn), 108)) FROM IO_Times Group by IDNum order by Max(Convert(varchar(20), (TimeOut - TimeIn), 108)) desc"; Into LINQ in C#. The above returns a list of the maximum value of (TimeOut-TimeIn) that corresponds to each unique ID...
I have a table called "users" with following columns userid,name,gender,department,managerid.... I wanted to do this query but was having 2 issues 1. <> in line 4 is is causing problem, which is due to to the XML. I need to use but was not able to get things going even after some permutations. 2. the check department = 'engineering' ...
I have a selection list that is generated dynamically, it lists a number of checkboxes that are based on an end-user editable table, as such I have no idea what data, or how much, might be contained in this table and how many checkboxes or what they might contain, other than the primary keys of the table. The user will select the checks...
Hi everyone, I'm making an website and I have problem generate the parent/child tree like: Page 1 ---Sub page 1 ------Sub page 1 - 1 Page 2 Page 3 ---Sub page 3 ------Sub page 3 - 1 ------Sub page 3 - 2 If I use UID, it's impossible to write the "ORDER BY" t-sql to make the tree. I'm thinking of a function that can generate the ID (v...
Hopefully the title is clear enough! I'm looking for a single query that replicates the functionality of the following one, but without using a subquery: select p_id from a_p where a_id=1 and p_id not in (select p_id from a_p where a_id=2) For example, table a_p has the following rows: a_id | p_id 1 | 1 1 | 2 2 | 2 H...
Hey guys, I have the following table structured like this: So basically as you can see, the department goes through name changes every couple of years. Look at number 16 for example. I want a select query that will only get the name when the date is the greatest. How do I do that? ...
I know of two ways to insert without duplication. The first is using a WHERE NOT EXISTS clause: INSERT INTO table_name (col1, col2, col3) SELECT %s, %s, %s WHERE NOT EXISTS ( SELECT * FROM table_name AS T WHERE T.col1 = %s AND T.col2 = %s) the other is doing a LEFT JOIN: INSERT INTO table_name (col1, col2, col3) SELECT ...
I'm working updating some legacy code that does not properly handle user input. The code does do a minimal amount of sanitization, but does not cover all known threats. Our newer code uses parameterized queries. As I understand it, the queries are precompiled, and the input is treated simply as data which cannot be executed. In that cas...
I'm still a neophyte when it comes to SQL queries, so I was hoping someone could lend me a hand. Assume I have 3 tables, Skill, Set, and Contact, and 2 linking tables, SkillSet and ContactSet. (the 3 tables have an "ID" column that is being used as a primary key) Contacts can have any number of sets, and vice versa (many-to-many) Sets...
How can the lookup order of 'where exists' be changed example SELECT name, dialcode FROM np_prefixes WHERE EXISTS ( SELECT d.name FROM np_prefixes d GROUP BY d.name ) GROUP BY name ) I require to get the first record it comes across in the reverse order it is currently getting as a example the result return...
I have a situation where I want to insert a row if it doesn't exist, and to not insert it if it already does. I tried creating sql queries that prevented this from happening (see here), but I was told a solution is to create constraints and catch the exception when they're violated. I have constraints in place already. My question is - ...
Here's the scenario: I have a table with 3 columns: 'KeyColumn', 'SubKeyColumn' and 'BooleanColumn', where the first two are the primary keys of the table. For my query, I'd like to count the number of rows there are for any given value in 'KeyColumn', and I'd also like to know which ones have a value of true for 'BooleanColumn'. My i...
I have a Sql Database table similar to the following: Day Period Subject Mon 1 Ch Mon 2 Ph Mon 3 Mth Mon 4 CS Mon 5 Lab1 Mon 6 Lab2 Mon 7 Lab3 Tue 1 Ph Tue 2 Ele Tue 3 Hu Tue 4 Ph Tue 5 En Tue 6 CS2 Tue...
I have the following query which returns the salary of all employees. This work perfectly but I need to collect extra data that I will aggregate into one cell (see Result Set 2). How can I aggregate data into a comma separated list? A little bit like what Sum does, but I need a string in return. SELECT Employee.Id, SUM(Pay) as Salary F...
I need to do a SELECT for a product_id where the length the product_id is 4 characters long and the last character can be any letter. I know the wildcard for selecting any letters but I don't know how to denote that it has to be the last character and that the product_ids I'm looking for must be 4 characters long. Does that make sense?...
Hello, After receiving very good correction from fuzzy lollipop, I amended my code to create an insert statement for every row of data. This is the code that I entered: INSERT DEPARTMENTS (Department_Id,Department_Name,Manager_Id,Location_Id) VALUES ('D0001','Think Tank',NULL,'L0001') GO INSERT DEPARTMENTS (Department_Id,Department_N...