Advanced mySQL and SQL 2000 Queries, Using Pointers

There are some more advanced queries you can do using SQL and mySQL. Below I discuss the case statement, unions, intersections, and creating a pointer to a column in a database using a Cursor.

1) Adding a condition to your query using the Case Statement:

select * from book
where case BookAuthor
when 'Tracy' then 'T'
when 'Bob' then lower(BookAuthor)
end like '%T%'

This query is using a case statement. The case statement allows you to put a condition in your query. You can't use the case statement in the enterprise manager in SQL, but you can use it in the query analyzer, views or stored procedures.

This statement says that when the BookAuthor is Tracy, return a T and when BookAuthor is Bob, then return the lower case BookAuthor column. That will just return what you want from the column, and then you can add the like condition to check if your case statement has a T in it. This can be handy when you are doing complex querys without using a stored procedure. The % is a wild card and means that anything can be before the T and anything can be after the T.

2) Using a Union Command:

select BookName as name from book
union
select FirstName as name from users

A union returns anything that is either in the book table or the users table, but not in both tables. The union is checking the book name from book table and the first name from the users table and returns only the rows that are not contained in both tables. You have to have the same number of columns in each select statement with the same name or alias.

3) Using an Intersection Command:

There isn't a command called insersection. An insersection is something that is contained in both tables. You can do an intersection using a sub query.

select id from book where
id in (select bookid from userbook)

This query checks the id and returns only the id's that are contained in the userbook table. The column in the subquery has to be one column.

If you want to return the items not contained in the userbook table, you can put a not before the in to say return the ids that are not in the userbook table.

4) Updating a column from data contained in another table

This is a handy query that I have only tested in SQL. I basically needed to retrieve data from a table and place that data into a specific column in the database in another table. This query will create a pointer to the gls_objective column and get all of the descriptions based on the id, add them together, add some break points CHAR(10), and set that description variable @description into the gls_objective column.

Declare ObjectiveCursor Cursor
For
Select OBJ_description,OBJ_goalGUID from Objectives order by obj_goalguid
Open ObjectiveCursor
Declare @goalid uniqueidentifier
Declare @description varchar(1024)
declare @ptr VARBINARY (16)
Fetch Next From ObjectiveCursor Into @description, @goalid
While @@Fetch_Status = 0
Begin
select @ptr=TEXTPTR(gls_objective) from goals where gls_goalguid=@goalid
set @description=@description+CHAR(10)+CHAR(10)
UPDATETEXT goals.gls_objective @ptr null null @description
Fetch Next From ObjectiveCursor Into @description, @goalid
End
Close ObjectiveCursor
Deallocate ObjectiveCursor