Preserve parent-child relationships when copying hierarchical data
A CTE works nicely with MERGE, but is problematic in SQL Server 2005. Sorry for the misleading comment earlier.
The following shows how to clone a project (with multiple trees) and fix up up the parentage to separate the new forest from the old. Note that it does not depend on any particular arrangement of Id's, e.g. they need not be dense, monotonically increasing, ... .
-- Sample data.
declare @Projects as Table
( Id Int Identity, ProjectId Int, Value VarChar(16), ParentId Int Null );
insert into @Projects ( ProjectId, Value, ParentId ) values
( 611, 'Animal', 0 ),
( 611, 'Frog', 1 ),
( 611, 'Cow', 1 ),
( 611, 'Jersey Cow', 3 ),
( 611, 'Plant', 0 ),
( 611, 'Tree', 5 ),
( 611, 'Oak', 6 );
-- Display the raw data.
select * from @Projects;
-- Display the forest.
with IndentedProjects ( Id, ProjectId, Value, ParentId, Level, Path ) as
( -- Start with the top level rows.
select Id, ProjectId, Value, ParentId, 0, Convert( VarChar(1024), Right( '000' + Convert( VarChar(4), Id ), 4 ) )
from @Projects
where ParentId = 0
union all
-- Add the children one level at a time.
select P.Id, P.ProjectId, P.Value, P.ParentId, IP.Level + 1, Convert( VarChar(1024), IP.Path + '<' + Right( '000' + Convert( VarChar(4), P.Id ), 4 ) )
from IndentedProjects as IP inner join
@Projects as P on P.ParentId = IP.Id
)
select Space( Level * 2 ) + Value as [IndentedValue], Id, ProjectId, Value, ParentId, Level, Path
from IndentedProjects
order by Path;
-- Clone the project.
declare @OldProjectId as Int = 611;
declare @NewProjectId as Int = 42;
declare @Fixups as Table ( OldId Int, [NewId] Int );
begin transaction -- With suitable isolation since the hierarchy will be invalid until we apply the fixups!
insert into @Projects
output Inserted.ParentId, Inserted.Id
into @Fixups
select @NewProjectId, Value, Id -- Note that we save the old Id in the new ParentId.
from @Projects as P
where ProjectId = @OldProjectId;
-- Apply the fixups.
update PNew
set ParentId = IsNull( FNew.[NewId], 0 )
-- Output the fixups just to show what is going on.
output Deleted.Id, Deleted.ParentId as [ParentIdBeforeFixup], Inserted.ParentId as [ParentIdAfterFixup]
from @Fixups as F inner join
@Projects as PNew on PNew.Id = F.[NewId] inner join -- Rows we need to fix.
@Fixups as FOld on FOld.OldId = PNew.ParentId inner join
@Projects as POld on POld.Id = FOld.OldId left outer join
@Fixups as FNew on FNew.OldId = POld.ParentId;
commit transaction;
-- Display the forest.
with IndentedProjects ( Id, ProjectId, Value, ParentId, Level, Path ) as
( -- Start with the top level rows.
select Id, ProjectId, Value, ParentId, 0, Convert( VarChar(1024), Right( '000' + Convert( VarChar(4), Id ), 4 ) )
from @Projects
where ParentId =0
union all
-- Add the children one level at a time.
select P.Id, P.ProjectId, P.Value, P.ParentId, IP.Level + 1, Convert( VarChar(1024), IP.Path + '<' + Right( '000' + Convert( VarChar(4), P.Id ), 4 ) )
from IndentedProjects as IP inner join
@Projects as P on P.ParentId = IP.Id
)
select Space( Level * 2 ) + Value as [IndentedValue], Id, ProjectId, Value, ParentId, Level, Path
from IndentedProjects
order by Path;
How to flatten a hierarchical data structure given parent child relationships in R
I would think about it like a graph problem. There are 2 changes to make to the original data to fit this approach: switch the order of columns to show the hierarchical direction (parent to child), and add a top-level node (I'm calling it "Items") that links to the major groups (food & not food). You could probably do that second part programmatically but it seems like more of a pain than it's worth.
library(dplyr)
df <- tibble::tribble(
~Child, ~Parent,
"Fruit", "Food",
"Vegetable", "Food",
"Apple", "Fruit",
"Banana", "Fruit",
"Pear", "Fruit",
"Carrot", "Vegetable",
"Celery", "Vegetable",
"Bike", "Not Food",
"Car", "Not Food"
) %>%
select(Parent, Child) %>%
add_row(Parent = "Items", Child = c("Food", "Not Food"))
The first method is with data.tree, which is designed to work with this type of data. It creates a tree representation, which you can then convert back to a data frame with one of a few shapes.
library(data.tree)
g1 <- FromDataFrameNetwork(df)
g1
#> levelName
#> 1 Items
#> 2 ¦--Food
#> 3 ¦ ¦--Fruit
#> 4 ¦ ¦ ¦--Apple
#> 5 ¦ ¦ ¦--Banana
#> 6 ¦ ¦ °--Pear
#> 7 ¦ °--Vegetable
#> 8 ¦ ¦--Carrot
#> 9 ¦ °--Celery
#> 10 °--Not Food
#> 11 ¦--Bike
#> 12 °--Car
ToDataFrameTypeCol(g1)
#> level_1 level_2 level_3 level_4
#> 1 Items Food Fruit Apple
#> 2 Items Food Fruit Banana
#> 3 Items Food Fruit Pear
#> 4 Items Food Vegetable Carrot
#> 5 Items Food Vegetable Celery
#> 6 Items Not Food Bike <NA>
#> 7 Items Not Food Car <NA>
The second method is more convoluted and probably only makes sense if there are other graph operations you need to do. Make a graph with igraph, then get all the paths in the graph starting from the top node Items. That gives you a list of vertex objects; for each of those, extract IDs. One example of those is below.
library(igraph)
g2 <- graph_from_data_frame(df)
all_simple_paths(g2, from = "Items") %>%
purrr::map(as_ids) %>%
`[[`(4)
#> [1] "Items" "Food" "Fruit" "Banana"
Create data frames from all those vectors, bind, and reshape to get one column per level.
all_simple_paths(g2, from = "Items") %>%
purrr::map(as_ids) %>%
purrr::map_dfr(tibble::enframe, .id = "row") %>%
tidyr::pivot_wider(id_cols = row, names_prefix = "level_")
#> # A tibble: 11 × 5
#> row level_1 level_2 level_3 level_4
#> <chr> <chr> <chr> <chr> <chr>
#> 1 1 Items Food <NA> <NA>
#> 2 2 Items Food Fruit <NA>
#> 3 3 Items Food Fruit Apple
#> 4 4 Items Food Fruit Banana
#> 5 5 Items Food Fruit Pear
#> 6 6 Items Food Vegetable <NA>
#> 7 7 Items Food Vegetable Carrot
#> 8 8 Items Food Vegetable Celery
#> 9 9 Items Not Food <NA> <NA>
#> 10 10 Items Not Food Bike <NA>
#> 11 11 Items Not Food Car <NA>
In either case, drop the level 1 column if you don't actually want it.
How can I convert a hierarchical tree to parent-child relationships?
For those who are interested in my final code. It works for storing the data in the database and build it again into a hierarchical tree + a printable tree for HTML.
JavaScript code for the nestable plugin:
var nestable_update = function(e){
$(".dd-item").each(function(index){
$(this).data("id", index+1);
});
var list = e.length ? e : $(e.target),
output = list.data("output");
if (window.JSON) {
output.val(window.JSON.stringify(list.nestable("serialize")));
} else {
output.val("JSON browser support required for this demo.");
}
};
$(".dd").nestable({
maxDepth:5
}).on("change", nestable_update);
nestable_update($(".dd").data("output", $("#nestable_output")));
Database structure:
menuitem_id
menu_id
menuitem_order
menuitem_name
menuitem_page_id
parent_menuitem_id
PHP functions for building trees (format storing data in database + format getting data from database):
function create_flatten_hierarchical_tree($tree, $parent_id=0) {
$items = array();
foreach ($tree as $item) {
$items[] = array("id" => $item["id"], "parent_id" => $parent_id);
if (isset($item["children"])) $items = array_merge($items, create_flatten_hierarchical_tree($item["children"], $item["id"]));
}
return $items;
}
function create_hierarchical_tree($tree, $root=0) {
$return = array();
foreach($tree as $child => $parent) {
if($parent["parent_menuitem_id"] == $root) {
if(isset($tree[$child]["menuitem_id"]) === true){
$parent['children'] = create_hierarchical_tree($tree, $tree[$child]["menuitem_id"]);
}
unset($tree[$child]);
$return[] = $parent;
}
}
return empty($return) ? null : $return;
}
function print_hierarchical_tree($tree, $rows_pages) {
if(!is_null($tree) && count($tree) > 0) {
$return .= "<ol class='dd-list'>";
foreach($tree as $item){
$options = "";
foreach($rows_pages as $row_pages){
$selected = "";
if($row_pages["page_id"] == $item["menuitem_page_id"]){
$selected = "selected";
}
$options .= "<option value='".$row_pages["page_id"]."' $selected>".$row_pages["friendly_url"]."</option>";
}
$return .= "<li class='dd-item' data-id='".$item["menuitem_id"]."'><div class='dd-handle'>drag</div><div class='item_wrapper'><div class='item'><div class='item_title'>".$item["menuitem_name"]."</div></div><div class='item_sub'><div class='label_input'><label for='menuitem_name".$item["menuitem_id"]."'>Menuitem name</label><input type='text' id='menuitem_name".$item["menuitem_id"]."' name='menuitem_name[]' value='".$item["menuitem_name"]."' /></div><div class='label_input'><label for='page_link".$item["menuitem_id"]."'>Page link</label><label class='select'><select id='page_link".$item["menuitem_id"]."' name='menuitem_page_id[]'>".$options."</select></label></div> <a onClick='delete_menuitem(".$item["menuitem_id"].");' class='button red_bg delete'>Delete</a></div></div>";
$return .= print_hierarchical_tree($item["children"], $rows_pages);
$return .= "</li>";
}
$return .= "</ol>";
}
return empty($return) ? null : $return;
}
Core code of menu_edit.php page:
<?php
$stmt_menuitems = $dbh->prepare("SELECT * FROM inno_mw_thurs_menuitems mi WHERE mi.menu_id=:menu_id");
$stmt_menuitems->bindParam(":menu_id", $_GET["menu_id"]);
$stmt_menuitems->execute();
if (!empty($stmt_menuitems->rowCount())) {
?>
<div class="dd">
<?php
$result = $stmt_menuitems->fetchAll();
$tree = create_hierarchical_tree($result);
$stmt_pages = $dbh->prepare("SELECT * FROM inno_mw_thurs_pages");
$stmt_pages->execute();
$rows_pages = $stmt_pages->fetchAll();
$tree = print_hierarchical_tree($tree, $rows_pages);
echo $tree;
?>
</div>
<?php
}
Core code of menu_edit_process.php page:
if(isset($_POST["menu_id"])){
$menu_id = $_POST["menu_id"];
$nestable_output = json_decode($_POST["nestable_output"], true);
$parent_menuitem_ids_arr = create_flatten_hierarchical_tree($nestable_output);
$stmt = $dbh->prepare("TRUNCATE TABLE inno_mw_thurs_menuitems");
$stmt->execute();
$stmt = $dbh->prepare("INSERT INTO inno_mw_thurs_menuitems (menu_id, menuitem_order, menuitem_name, menuitem_page_id, parent_menuitem_id) VALUES (:menu_id, :menuitem_order, :menuitem_name, :menuitem_page_id, :parent_menuitem_id)");
$menuitem_order_arr = array();
foreach($_POST["menuitem_name"] as $f => $name){
$menuitem_name = $_POST["menuitem_name"][$f];
$menuitem_page_id = $_POST["menuitem_page_id"][$f];
$parent_menuitem_id = $parent_menuitem_ids_arr[$f]["parent_id"];
if(array_key_exists($parent_menuitem_id, $menuitem_order_arr) && $parent_menuitem_id != 0){
$menuitem_order_arr[$parent_menuitem_id] += 1;
}
else{
$menuitem_order_arr[$parent_menuitem_id] = 0;
}
$stmt->bindParam(":menu_id", $menu_id);
$stmt->bindParam(":menuitem_order", $menuitem_order_arr[$parent_menuitem_id]);
$stmt->bindParam(":menuitem_name", $menuitem_name);
$stmt->bindParam(":menuitem_page_id", $menuitem_page_id);
$stmt->bindParam(":parent_menuitem_id", $parent_menuitem_id);
$stmt->execute();
}
header("location: menus_list.php");
}
Please, feel free to improve this code.
Achieve hierarchy, Parent/Child Relationship in an effective and easy way
Unfortunately, if you can't change the data model, and you're using MySQL, you're stuck in a situation where you need recursive queries and you're using a DBMS that doesn't support recursive queries.
Quassnoi wrote an interesting series of blog articles, showing techniques for querying hierarchical data. His solutions are quite clever, but very complex.
http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/
PostgreSQL is another open-source RDBMS, which does support recursive queries, so you could fetch a whole tree stored in the way you show. But if you can't change the data model, I'd assume you can't switch to a different RDBMS.
There are several alternative data models that make it much easier to fetch arbitrarily-deep trees:
- Closure Table
- Nested Sets aka Modified Preorder Tree Traversal
- Path Enumeration aka Materialized Path
I cover these in my presentation Models for Hierarchical Data with SQL and PHP, and in my book SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.
Finally, there's another solution that I've seen used in the code for Slashdot, for their comments hierarchies: They store "parent_id" like in Adjacency List, but they also store a "root_id" column. Every member of a given tree has the same value for root_id, which is the highest ancestor node in its tree. Then it's easy to fetch a whole tree in one query:
SELECT * FROM site WHERE root_id = 123;
Then your application fetches all the nodes back from the database into an array, and you have to write the code to loop over this array, inserting the nodes into a tree data structure in memory. This is a good solution if you have many separate trees, and each tree has relatively few entries. It's good for Slashdot's case.
Convert a series of parent-child relationships into a hierarchical tree?
This requires a very basic recursive function to parse the child/parent pairs to a tree structure and another recursive function to print it out. Only one function would suffice but here's two for clarity (a combined function can be found at the end of this answer).
First initialize the array of child/parent pairs:
$tree = array(
'H' => 'G',
'F' => 'G',
'G' => 'D',
'E' => 'D',
'A' => 'E',
'B' => 'C',
'C' => 'E',
'D' => null
);
Then the function that parses that array into a hierarchical tree structure:
function parseTree($tree, $root = null) {
$return = array();
# Traverse the tree and search for direct children of the root
foreach($tree as $child => $parent) {
# A direct child is found
if($parent == $root) {
# Remove item from tree (we don't need to traverse this again)
unset($tree[$child]);
# Append the child into result array and parse its children
$return[] = array(
'name' => $child,
'children' => parseTree($tree, $child)
);
}
}
return empty($return) ? null : $return;
}
And a function that traverses that tree to print out an unordered list:
function printTree($tree) {
if(!is_null($tree) && count($tree) > 0) {
echo '<ul>';
foreach($tree as $node) {
echo '<li>'.$node['name'];
printTree($node['children']);
echo '</li>';
}
echo '</ul>';
}
}
And the actual usage:
$result = parseTree($tree);
printTree($result);
Here's the contents of $result:
Array(
[0] => Array(
[name] => D
[children] => Array(
[0] => Array(
[name] => G
[children] => Array(
[0] => Array(
[name] => H
[children] => NULL
)
[1] => Array(
[name] => F
[children] => NULL
)
)
)
[1] => Array(
[name] => E
[children] => Array(
[0] => Array(
[name] => A
[children] => NULL
)
[1] => Array(
[name] => C
[children] => Array(
[0] => Array(
[name] => B
[children] => NULL
)
)
)
)
)
)
)
)
If you want a bit more efficiency, you can combine those functions into one and reduce the number of iterations made:
function parseAndPrintTree($root, $tree) {
$return = array();
if(!is_null($tree) && count($tree) > 0) {
echo '<ul>';
foreach($tree as $child => $parent) {
if($parent == $root) {
unset($tree[$child]);
echo '<li>'.$child;
parseAndPrintTree($child, $tree);
echo '</li>';
}
}
echo '</ul>';
}
}
You'll only save 8 iterations on a dataset as small as this but on bigger sets it could make a difference.
MySQL copy hierarchical data and maintain relationship
You need to maintain not only the parent_orig_id, but also the orig_id. Since you've gone half the distance using the strategy of having columns in your table for this, I'll continue in that direction. Like this:
CREATE TABLE `site_content` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`site_id` int(11) DEFAULT NULL,
`parent` int(11) unsigned DEFAULT NULL,
`orig_id` int(11) unsigned DEFAULT NULL,
`parent_orig_id` int(11) unsigned DEFAULT NULL,
`content_text` text,
PRIMARY KEY (`id`));
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(1, 76, 0, NULL, '');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(2, 76, 1, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(3, 76, 1, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(4, 76, 1, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(5, 76, 1, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(6, 76, 1, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(7, 76, 2, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(8, 76, 3, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(9, 76, 3, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(11, 76, 9, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(12, 76, 9, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(13, 76, 9, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(14, 76, 4, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(15, 76, 4, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(28, 76, 4, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(29, 76, 4, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(30, 76, 5, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(32, 76, 5, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(36, 76, 41, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(41, 76, 32, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(42, 76, 32, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(43, 76, 41, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(44, 76, 41, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(45, 76, 41, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(46, 76, 41, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(47, 76, 41, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(48, 76, 41, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(49, 76, 41, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(50, 76, 42, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`) VALUES(51, 76, 42, NULL, 'some content');
INSERT INTO `site_content` (`id`, `site_id`, `parent`, `orig_id`, `parent_orig_id`, `content_text`)
SELECT NULL, "200", NULL, `orig_id`, `parent_orig_id`, `content_text` FROM site_content
WHERE `site_id` = '76';
UPDATE site_content AS sc_target
LEFT JOIN site_content AS sc_source
ON sc_source.parent_orig_id = sc_target.parent
SET sc_target.parent = sc_source.id where sc_target.site_id = "200"
Copying Parent and child attributes
This is weird solution.
We put in CTE all stuff from company 12,
We find
MAXid from this table and then add it toROW_NUMBER(),Convert
ParentStringto XML,Select what we need,
And got output (see below) that can be inserted into Categories table:
DECLARE @id int
SELECT @id = MAX(CategoryId) FROM #Categories
;WITH cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY CompanyId ORDER BY CategoryId) + @id as NewCategoryId,
CategoryId as OldCategoryId,
CompanyId,
ParentCategoryId,
CategoryName,
CAST('<p>'+REPLACE(STUFF(ParentString,LEN(ParentString),1,''),'-','</p><p>')+'</p>' as xml) parentxml
FROM #Categories
WHERE CompanyId = 12
)
SELECT c.NewCategoryId,
13 as CompanyId,
c1.NewCategoryId as NewParentCategoryId,
c.CategoryName,
(
SELECT CAST(f.NewCategoryId as nvarchar(max)) +'-'
FROM cte f
OUTER APPLY (
SELECT t.c.value('.','int') as xpart
FROM c.parentxml.nodes('/p') as t(c)
) x
WHERE OldCategoryId = x.xpart
FOR XML PATH('')
) as ParentString
FROM cte c
LEFT JOIN cte c1
ON c1.OldCategoryId = c.ParentCategoryId
Output:
NewCategoryId CompanyId NewParentCategoryId CategoryName ParentString
130 13 NULL Mens Clothing NULL
131 13 130 Shirt 130-
132 13 NULL Womens Clothing NULL
133 13 132 Shirt 132-
134 13 131 Short Sleeve 130-131-
135 13 NULL Drinks NULL
136 13 135 Water 135-
EDIT
If CategoryId column is an auto-incremented IDENTITY column then better use transaction and turn IDENTITY_INSERT ON:
SET IDENTITY_INSERT Categories ON
BEGIN TRANSACTION
...
COMMIT TRANSACTION
SET IDENTITY_INSERT Categories OFf
Related Topics
Use a Like Clause in Part of an Inner Join
How to Subtract 2 Dates in Oracle to Get the Result in Hour and Minute
How to Get Time Part from SQL Server 2005 Datetime in 'Hh:Mm Tt' Format
Postgres: What Is the Query 'Select * from User' Actually Doing
Paging with Oracle and SQL Server and Generic Paging Method
To Get Column Names from Table Having a Particular Value
Is It Ok Not to Use a Primary Key When I Don't Need One
Add New Column Without Table Lock
Select Records in on Table Based on Conditions from Another Table
How to Make Comment Reply Query in MySQL
How to Select Rows Where a Column Value Starts with a Certain String
Rails, Ransack: How to Search Habtm Relationship for "All" Matches Instead of "Any"