How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttid
Look at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid|||Oh, I see. I have to use Having and it's after the Group. It works great.
Thank you very much and have a great day!
"Geoff N. Hiten" wrote:
> Look at the "Having" clause of the select statement. "Having" works on
> "Group By" much like the 'Where" works on "From". In your case, you would
> add something like:
> HAVING Sum(Amount) > 0
> after your Group By section.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> > Hi
> > I'm builing this string dynamically in C# program. I need to add one more
> > filtering condition of "Sum(Amount) >0" to the Select statement. I added
> > it
> > but I got an error message. Can someone help on how to do this in one
> > select
> > statment?
> >
> > Thanks, Alpha
> >
> > select exttid,sum(amount) as FeeBalance from tblLedger
> > where exttid in
> > (select distinct(exttid) from tblBilling b
> > where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> > and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> > and void = 0
> > group by exttid
>
>

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one selec
t
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttidsql

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.
I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegro ups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>
|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> I can't make head or tail of what you are trying to do here (mostly because
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, it
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired results.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegro ups.com...

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:[vbcol=seagreen]
> I can't make head or tail of what you are trying to do here (mostly becaus
e
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, i
t
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired result
s.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...|||On 3 Jan 2007 06:50:11 -0800, Loganx80 wrote:

>OK,
>Sorry that my example isn't the easiest to understand. I was actually
>just trying to make it more readable. If it's true that the CASE
>expression cannot return more than one value (such as an IN list), then
>I may need to take another approach, but let me at least give you a
>better example.
>Let's say I have a table with products in it. Each product has a
>category #.
>Categories:
>1 Shoes
>2 Pants
>3 Shirts
>4 TVs
>5 DVD Players
>6 Xboxes
>Now I want to do a select statement based upon a category type passed
>into the procedure @.CategoryType. When @.CategoryType is "electronics",
>I want to return all products with category numbers 4, 5, or 6.
>Likewise, if "clothing" is passed in, I want to return all products
>with 1, 2, 3.
>SELECT * FROM Products WHERE CategoryID IN
> (CASE @.CategoryType
> WHEN "electronics" THEN (4,5,6)
> WHEN "clothing" THEN (1,2,3)
> END)
>
>Hopefully, this makes more sense.
Hi Matthew,
Best way to do this is to add a categories table with a CategoryID and a
CategoryType column. That way, you can add products (and categories!)
without having to change any queries. I have always hated hard-coded
magic values.
But if you do want to hard-code the values, try something like this
SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM Products
WHERE (@.CategoryType = 'electronics' AND CategoryID IN (4, 5, 6))
OR (@.CategoryType = 'clothing' AND CategoryID IN (1, 2, 3));
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How can you use an IN statement with a CASE statement

I am trying to run a query that filters using an IN statment, but the
IN statement needs to be dynamically generated based upon some
criteria. Here is my example:
DECLARE @.tempTable TABLE
(
letter char(1),
id int
)
INSERT INTO @.tempTable VALUES ('A', 1)
INSERT INTO @.tempTable VALUES ('B', 2)
INSERT INTO @.tempTable VALUES ('C', 3)
SELECT * FROM @.tempTable WHERE letter IN
(Case 2
WHEN 1 THEN ('A')
WHEN 2 THEN ('B', 'C')
END)
If you try to run this code, it gives an error on the line that
contains the 'B', 'C' (incorrect syntax near ',').
If you remove the C and just leave the two lines, one with A, one with
B, then it will work as expected.I can't make head or tail of what you are trying to do here (mostly because
of the 'Case 2' which will always try to run the B,C variant), but in any
case, an IN list cannot be the result of a CASE expression.
Maybe you meant something like this (wild guesses here):
DECLARE @.tinyint TINYINT;
SET @.tinyint = 2;
SELECT * FROM @.temptable
WHERE @.tinyint = CASE
WHEN letter = 'A' THEN 1
WHEN letter IN ('B','C') THEN2
END
The important thing to remember is that CASE is not for control of flow, it
is an expression that returns exactly one datatype, and all possible
outcomes must be able to yield the same datatype (or NULL).
Maybe you could try with a slightly more realistic example of what you are
really trying to accomplish, with real sample data and real desired results.
I can't deduce anything from A,B,C.
A
<MatthewSumpter@.gmail.com> wrote in message
news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
>I am trying to run a query that filters using an IN statment, but the
> IN statement needs to be dynamically generated based upon some
> criteria. Here is my example:
> DECLARE @.tempTable TABLE
> (
> letter char(1),
> id int
> )
> INSERT INTO @.tempTable VALUES ('A', 1)
> INSERT INTO @.tempTable VALUES ('B', 2)
> INSERT INTO @.tempTable VALUES ('C', 3)
> SELECT * FROM @.tempTable WHERE letter IN
> (Case 2
> WHEN 1 THEN ('A')
> WHEN 2 THEN ('B', 'C')
> END)
>
> If you try to run this code, it gives an error on the line that
> contains the 'B', 'C' (incorrect syntax near ',').
> If you remove the C and just leave the two lines, one with A, one with
> B, then it will work as expected.
>|||OK,
Sorry that my example isn't the easiest to understand. I was actually
just trying to make it more readable. If it's true that the CASE
expression cannot return more than one value (such as an IN list), then
I may need to take another approach, but let me at least give you a
better example.
Let's say I have a table with products in it. Each product has a
category #.
Categories:
1 Shoes
2 Pants
3 Shirts
4 TVs
5 DVD Players
6 Xboxes
Now I want to do a select statement based upon a category type passed
into the procedure @.CategoryType. When @.CategoryType is "electronics",
I want to return all products with category numbers 4, 5, or 6.
Likewise, if "clothing" is passed in, I want to return all products
with 1, 2, 3.
SELECT * FROM Products WHERE CategoryID IN
(CASE @.CategoryType
WHEN "electronics" THEN (4,5,6)
WHEN "clothing" THEN (1,2,3)
END)
Hopefully, this makes more sense.
Thanks,
Matthew
Aaron Bertrand [SQL Server MVP] wrote:
> I can't make head or tail of what you are trying to do here (mostly because
> of the 'Case 2' which will always try to run the B,C variant), but in any
> case, an IN list cannot be the result of a CASE expression.
> Maybe you meant something like this (wild guesses here):
> DECLARE @.tinyint TINYINT;
> SET @.tinyint = 2;
> SELECT * FROM @.temptable
> WHERE @.tinyint = CASE
> WHEN letter = 'A' THEN 1
> WHEN letter IN ('B','C') THEN2
> END
> The important thing to remember is that CASE is not for control of flow, it
> is an expression that returns exactly one datatype, and all possible
> outcomes must be able to yield the same datatype (or NULL).
> Maybe you could try with a slightly more realistic example of what you are
> really trying to accomplish, with real sample data and real desired results.
> I can't deduce anything from A,B,C.
> A
>
> <MatthewSumpter@.gmail.com> wrote in message
> news:1167774294.707435.113300@.42g2000cwt.googlegroups.com...
> >I am trying to run a query that filters using an IN statment, but the
> > IN statement needs to be dynamically generated based upon some
> > criteria. Here is my example:
> >
> > DECLARE @.tempTable TABLE
> > (
> > letter char(1),
> > id int
> > )
> >
> > INSERT INTO @.tempTable VALUES ('A', 1)
> > INSERT INTO @.tempTable VALUES ('B', 2)
> > INSERT INTO @.tempTable VALUES ('C', 3)
> >
> > SELECT * FROM @.tempTable WHERE letter IN
> > (Case 2
> > WHEN 1 THEN ('A')
> > WHEN 2 THEN ('B', 'C')
> > END)
> >
> >
> > If you try to run this code, it gives an error on the line that
> > contains the 'B', 'C' (incorrect syntax near ',').
> >
> > If you remove the C and just leave the two lines, one with A, one with
> > B, then it will work as expected.
> >|||On 3 Jan 2007 06:50:11 -0800, Loganx80 wrote:
>OK,
>Sorry that my example isn't the easiest to understand. I was actually
>just trying to make it more readable. If it's true that the CASE
>expression cannot return more than one value (such as an IN list), then
>I may need to take another approach, but let me at least give you a
>better example.
>Let's say I have a table with products in it. Each product has a
>category #.
>Categories:
>1 Shoes
>2 Pants
>3 Shirts
>4 TVs
>5 DVD Players
>6 Xboxes
>Now I want to do a select statement based upon a category type passed
>into the procedure @.CategoryType. When @.CategoryType is "electronics",
>I want to return all products with category numbers 4, 5, or 6.
>Likewise, if "clothing" is passed in, I want to return all products
>with 1, 2, 3.
>SELECT * FROM Products WHERE CategoryID IN
> (CASE @.CategoryType
> WHEN "electronics" THEN (4,5,6)
> WHEN "clothing" THEN (1,2,3)
> END)
>
>Hopefully, this makes more sense.
Hi Matthew,
Best way to do this is to add a categories table with a CategoryID and a
CategoryType column. That way, you can add products (and categories!)
without having to change any queries. I have always hated hard-coded
magic values.
But if you do want to hard-code the values, try something like this
SELECT Col1, Col2, ... -- Never use SELECT * !!!
FROM Products
WHERE (@.CategoryType = 'electronics' AND CategoryID IN (4, 5, 6))
OR (@.CategoryType = 'clothing' AND CategoryID IN (1, 2, 3));
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

How can one find out if two tables can be joinned dynamically

Hi All,

Given a table, is there a way to programmatically find out what other tables and views it can be joinned with? Essentially, given two tables, two views, or a table and a view, I want to be able to find out if they can be joinned. I was able to find related tables with sp_fkeys, but this does not work all the time, and does not work at all with views. I also played with the information_schema but got mixed up and confused quickly.

Any help would be greatly appreciated.

Can you explain what you are trying to do with joining tables dynamically? What is the problem you are trying to solve? It seems very complicated to me. You can use sp_fkeys or INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS or sys.foreign_keys (SQL2005) to get the list of foreign keys and go from there. This will be accurate as long as you have the necessary constraints defined in your schema.

Views do not explicitly have any relationship to tables. You can use WITH SCHEMABINDING option with views but it is different than a foreign key reference. You can use INFORMATION_SCHEMA.VIEW_TABLE_USAGE to get tables that are referenced in a view. There is similar view to get the column usage information.

|||

Umachandar,

Thanks a bunch for the pointers. I am buildinng a reporting application, where users can select columns from a list of tables and views. Naturally, I only want to show tables and views that are related. For example, I know I can join table Orders and view [Order Details Extended], therefore I want to show the two datasources. My problem is that I want to show the relationship dynamically.

I am a little confused with INFORMATION_SCHEMA.VIEW_TABLE_USAGE:

When I run this in Query analyzer, I get a bunch or records, including:

Northwind |dbo |Category Sales for 1997| Northwind |dbo |Product Sales for 1997|

The last column, which is the Table_Name, is [Product Sales for 1997] which is a view! What does this mean? How do I get a list of all tables related to a view? Or vice versa?

Thanks again,

Amine

|||

The usual approach that I have seen taken with this is to come up with your own table that holds the relationship information.

The users start off by selecting one main table to query then as they add data elements from other tables the query builder retrieves the appropriate row and adds that to the query it is building.

MainTable Related Table Relationship

Customers Orders Customers.CustomerID = Sales.CustomerID

Query is bult as follows

SELECT -- whatever columns you select

FROM Customers, Orders

WHERE Customers.CustomerID = Sales.CustomerID

Even though this is not ANSI standard syntax (and I hate seeing joins like this) it is easier to build the string dynamically. If another table is selected then they simply add that to the FROM clause " , CustomerPhone" and to the where clause " AND CustomerPhone.CustomerID = Sales.CustomerID"

|||

Views can reference views or tables. The information schema view goes only one level so you will have to write a recursive query like below to get all dependencies:

with vt
as (
select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where objectproperty(object_id(quotename(TABLE_SCHEMA) + N'.' + quotename(TABLE_NAME)), 'IsView') = 1
union all
select t2.VIEW_SCHEMA, t2.VIEW_NAME, t1.TABLE_SCHEMA, t1.TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE as t1
join vt as t2
on t1.VIEW_SCHEMA = t2.TABLE_SCHEMA and t1.VIEW_NAME = t2.TABLE_NAME
)
select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from vt
union all
select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where objectproperty(object_id(quotename(TABLE_SCHEMA) + N'.' + quotename(TABLE_NAME)), 'IsView') = 0
order by VIEW_NAME, TABLE_NAME;

This query is harder to write in SQL Server 2000 but if you know the depth of the dependencies before hand then you can write a single query. Otherwise you can create a multi-statement TVF that simulates above logic.

|||

David,

Thanks for the help. I was trying to avoid doing that. I wanted to only show the tables and views that were related at design time. I guess I could do the "blind" join and if this fails, notify the user.

Thanks

Amine

|||

Awsome. This will get me going.

Thanks a lot!

|||

Hi Again,

I ran this query in SQL 2005 and it worked OK, but does not run in MSDE or SQL 2000. Any ideas why? I get

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'with'.

|||The query put forth by one of the other illustrious posters uses a Common Table Expression (CTE) which is a new feature for SQL 2005. He does mention that for SQL 2000 you will need a different plan of attack.|||

Hi

Try it in SQL 2000

select t2.VIEW_SCHEMA, t2.VIEW_NAME, t1.TABLE_SCHEMA, t1.TABLE_NAME
from
(select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where objectproperty(object_id(quotename(TABLE_SCHEMA) + N'.' + quotename(TABLE_NAME)), 'IsView') = 1) t2 inner join
INFORMATION_SCHEMA.VIEW_TABLE_USAGE as t1
on t1.VIEW_SCHEMA = t2.TABLE_SCHEMA and t1.VIEW_NAME = t2.TABLE_NAME

Irfan

How can one find out if two tables can be joinned dynamically

Hi All,

Given a table, is there a way to programmatically find out what other tables and views it can be joinned with? Essentially, given two tables, two views, or a table and a view, I want to be able to find out if they can be joinned. I was able to find related tables with sp_fkeys, but this does not work all the time, and does not work at all with views. I also played with the information_schema but got mixed up and confused quickly.

Any help would be greatly appreciated.

Can you explain what you are trying to do with joining tables dynamically? What is the problem you are trying to solve? It seems very complicated to me. You can use sp_fkeys or INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS or sys.foreign_keys (SQL2005) to get the list of foreign keys and go from there. This will be accurate as long as you have the necessary constraints defined in your schema.

Views do not explicitly have any relationship to tables. You can use WITH SCHEMABINDING option with views but it is different than a foreign key reference. You can use INFORMATION_SCHEMA.VIEW_TABLE_USAGE to get tables that are referenced in a view. There is similar view to get the column usage information.

|||

Umachandar,

Thanks a bunch for the pointers. I am buildinng a reporting application, where users can select columns from a list of tables and views. Naturally, I only want to show tables and views that are related. For example, I know I can join table Orders and view [Order Details Extended], therefore I want to show the two datasources. My problem is that I want to show the relationship dynamically.

I am a little confused with INFORMATION_SCHEMA.VIEW_TABLE_USAGE:

When I run this in Query analyzer, I get a bunch or records, including:

Northwind |dbo |Category Sales for 1997| Northwind |dbo |Product Sales for 1997|

The last column, which is the Table_Name, is [Product Sales for 1997] which is a view! What does this mean? How do I get a list of all tables related to a view? Or vice versa?

Thanks again,

Amine

|||

The usual approach that I have seen taken with this is to come up with your own table that holds the relationship information.

The users start off by selecting one main table to query then as they add data elements from other tables the query builder retrieves the appropriate row and adds that to the query it is building.

MainTable Related Table Relationship

Customers Orders Customers.CustomerID = Sales.CustomerID

Query is bult as follows

SELECT -- whatever columns you select

FROM Customers, Orders

WHERE Customers.CustomerID = Sales.CustomerID

Even though this is not ANSI standard syntax (and I hate seeing joins like this) it is easier to build the string dynamically. If another table is selected then they simply add that to the FROM clause " , CustomerPhone" and to the where clause " AND CustomerPhone.CustomerID = Sales.CustomerID"

|||

Views can reference views or tables. The information schema view goes only one level so you will have to write a recursive query like below to get all dependencies:

with vt
as (
select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where objectproperty(object_id(quotename(TABLE_SCHEMA) + N'.' + quotename(TABLE_NAME)), 'IsView') = 1
union all
select t2.VIEW_SCHEMA, t2.VIEW_NAME, t1.TABLE_SCHEMA, t1.TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE as t1
join vt as t2
on t1.VIEW_SCHEMA = t2.TABLE_SCHEMA and t1.VIEW_NAME = t2.TABLE_NAME
)
select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from vt
union all
select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where objectproperty(object_id(quotename(TABLE_SCHEMA) + N'.' + quotename(TABLE_NAME)), 'IsView') = 0
order by VIEW_NAME, TABLE_NAME;

This query is harder to write in SQL Server 2000 but if you know the depth of the dependencies before hand then you can write a single query. Otherwise you can create a multi-statement TVF that simulates above logic.

|||

David,

Thanks for the help. I was trying to avoid doing that. I wanted to only show the tables and views that were related at design time. I guess I could do the "blind" join and if this fails, notify the user.

Thanks

Amine

|||

Awsome. This will get me going.

Thanks a lot!

|||

Hi Again,

I ran this query in SQL 2005 and it worked OK, but does not run in MSDE or SQL 2000. Any ideas why? I get

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'with'.

|||The query put forth by one of the other illustrious posters uses a Common Table Expression (CTE) which is a new feature for SQL 2005. He does mention that for SQL 2000 you will need a different plan of attack.|||

Hi

Try it in SQL 2000

select t2.VIEW_SCHEMA, t2.VIEW_NAME, t1.TABLE_SCHEMA, t1.TABLE_NAME
from
(select VIEW_SCHEMA, VIEW_NAME, TABLE_SCHEMA, TABLE_NAME
from INFORMATION_SCHEMA.VIEW_TABLE_USAGE
where objectproperty(object_id(quotename(TABLE_SCHEMA) + N'.' + quotename(TABLE_NAME)), 'IsView') = 1) t2 inner join
INFORMATION_SCHEMA.VIEW_TABLE_USAGE as t1
on t1.VIEW_SCHEMA = t2.TABLE_SCHEMA and t1.VIEW_NAME = t2.TABLE_NAME

Irfan