Add column, with default value, to existing table in SQL Server

PROBLEM

---

How do you add a column, with a default value, to an existing table in SQL Server 2000/2005?

SOLUTION

---

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Insert data from one table to another

PROBLEM

---

I have 2 different tables but the columns are named slightly differently. I want to take information from 1 table and put it into the other table. I need the info from table 1 put into table 2 only when the "info field" in table 1 is not null. Table 2 has a unique id anytime something is created, so anything inserted needs to get the next available id number.

Table 1

category
clientLastName
clientFirstName
incidentDescription
info field is not null then insert all fields into table 2

Table 2

*need a unique id assigned
client_last_name
client_first_name
taskDescription
category

SOLUTION

---

This should work. You don't need to worry about the identify field in Table2.

INSERT INTO Table2
 (client_last_name, client_first_name, taskDescription, category)
 (SELECT clientLastName, clientFirstName, incidentDescription, category
  FROM Table1
  WHERE info_field IS NOT NULL)

How do I run this MySQL JOIN query?

PROBLEM

---

The first table is a list of personas. A user can have many personas.

mysql> select id, user_id, name from personas_personas;
+----+---------+--------------+
| id | user_id | name         |
+----+---------+--------------+
|  8 |       1 | startup      |
|  9 |       1 | nerd         |
| 10 |       1 | close        |
| 12 |       2 | Nerd         |
| 13 |       2 | Startup      |
| 14 |       2 | Photographer |
+----+---------+--------------+
6 rows in set (0.00 sec)

Now, I have another table called "approvals".

mysql> select id, from_user_id, to_user_id, persona_id  from friends_approvals;
+----+--------------+------------+------------+
| id | from_user_id | to_user_id | persona_id |
+----+--------------+------------+------------+
|  2 |            1 |          2 |          8 |
|  3 |            1 |          2 |          9 |
+----+--------------+------------+------------+
2 rows in set (0.00 sec)

If from_user wants to approve to_user to a persona, then a record is inserted.

I'm trying to do this query...

Given a user, find all its personas. Then, for each persona, determine if it's approved for a certainto_user. If so, return is_approved=1 in the result set. Otherwise, return is_approved=0 in the result set.

SOLUTION

---

 SELECT 
   pp.*,
   CASE
     WHEN exists (
         SELECT 
          * 
         FROM 
          friends_approvals fa  
         WHERE 
          fa.from_user_id = pp.user_id AND
          fa.persona_id = pp.id AND
          fa.to_user_id = 2
          )
      THEN 1
      ELSE 0
   END as is_approved

 FROM 
   personas_personas pp 
 WHERE 
   pp.user_id=1

Or, depending on your taste:

 SELECT 
   pp.*,
   CASE
     WHEN fa.from_user_id IS NOT NULL
      THEN 1
      ELSE 0
   END as is_approved

 FROM 
   personas_personas pp 
     LEFT OUTER JOIN friends_approvals fa ON
       pp.user_id = fa.from_user_id AND
       pp.id = fa.persona_id AND
       fa.to_user_id = 2
 WHERE 
   pp.user_id=1

How to Get most current value in group by query

PROBLEM

---

I have two tables questions and answered. answered contains the answers for all the questions for all the users. One user can answer a question multiple times. A question can be answered correctly or incorrectly.

I am looking for a query that will return the count of correct and incorrect answers over all questions in one category. I want to use the most current answer only, though. So if a user answered the same question incorrectly before and correctly more recently, I only want to count the newest - correct - one.

SOLUTION

---

Here is the query that you need:

SELECT a.correct, count(*) as counter
FROM answered a
JOIN (SELECT user_id, question_id, max(created) as maxCreated
      FROM answered
      GROUP BY user_id, question_id) aux
  ON a.user_id = aux.user_id AND
     a.question_id = aux.question_id AND
     a.created = aux.maxCreated
JOIN questions q ON a.question_id = q.id
WHERE a.user_id = 1 AND q.category_id = 1
GROUP BY a.correct

Use the aux sub-query to select only the rows with the last answer to a question from a given user.

“Friend” relationships between 2 tables

PROBLEM

---

I have two tables like this

Users table
id    | name
-------------
1     |  s1
2     |  s2
3     |  s3
4     |  s4
5     |  s5
6     |  s6

friends table
friendID | user_a | user_b
--------------------
1        |   1    |   2
2        |   3    |   1
3        |   4    |   2
4        |   1    |   3

I want to run this query: Who is friends with s1?

SOLUTION

---

SELECT DISTINCT c.name
FROM users a, friends b, users c
WHERE a.id=b.user_a
    AND b.user_b=c.id
    AND a.name='s1';

Result:

╔══════╗
║ NAME ║
╠══════╣
║ s2   ║
║ s3   ║
╚══════╝