SQL question

[sklett]

Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are linked by the
Prod_Docs many-to-many table. I can't figure out how to accomplish this.
Here is more info:
SO let's say there are 10 Docs_Main records in the Prod_Docs table
Assume that there are 500 records in the Docs_Categories table

All I want is to get the Docs_Categories records that are actually being
used AND... I can't have any duplicates. So if 3 of those 10 Docs_Main
records use the same Docs_Categories.. I don't want to get 3 repeating
records. I need to use this in a DataRelation.

Also, in case you are wondering what in the world I'm trying to do... I
want to present my Data like this:

Product Data
Document Category
Document
Document
Document
Document Category
Document

It's kicking my butt, I need to get the data in this weird backwards way to
accomplish this.

If anyone has a better idea that what I'm already trying.. PLEASE share,
because I have several situations like this throughout the site.

Thanks for reading!

-Steve

 

[sklett]

OK, I made an UGLY, but working code sample of what I want to do. Hopefully
by looking at this, some of you will be able to tell me how to do this the
correct way

if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId = PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end

 

[sklett]

How does this look?

SELECT DISTINCT DC.CatId, DC.Category
FROM Docs_Categories DC, (SELECT DM.CatId FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId


Is that an efficient way to get what I want? Is there a faster way?

OK, I made an UGLY, but working code sample of what I want to do. Hopefully
by looking at this, some of you will be able to tell me how to do this the
correct way

if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId = PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end

Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are linked by the
Prod_Docs many-to-many table. I can't figure out how to accomplish this.
Here is more info:
SO let's say there are 10 Docs_Main records in the Prod_Docs table
Assume that there are 500 records in the Docs_Categories table

All I want is to get the Docs_Categories records that are actually being
used AND... I can't have any duplicates. So if 3 of those 10 Docs_Main
records use the same Docs_Categories.. I don't want to get 3 repeating
records. I need to use this in a DataRelation.

Also, in case you are wondering what in the world I'm trying to do... I
want to present my Data like this:

Product Data
Document Category
Document
Document
Document
Document Category
Document

It's kicking my butt, I need to get the data in this weird backwards way to
accomplish this.

If anyone has a better idea that what I'm already trying.. PLEASE share,
because I have several situations like this throughout the site.

Thanks for reading!

-Steve

 

[William Ryan [eMVP]]

Sklett:

When you run it how is it working? The first thing that comes to mind is
that if the subquery returns multiple rows if may cause you a problem. I'll
build those tables when I get into work tomorrow and play with it.

Bill

How does this look?

SELECT DISTINCT DC.CatId, DC.Category
FROM Docs_Categories DC, (SELECT DM.CatId FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId


Is that an efficient way to get what I want? Is there a faster way?

OK, I made an UGLY, but working code sample of what I want to do. Hopefully
by looking at this, some of you will be able to tell me how to do this the
correct way

if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId = PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end

Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are linked by the
Prod_Docs many-to-many table. I can't figure out how to accomplish this.
Here is more info:
SO let's say there are 10 Docs_Main records in the Prod_Docs table
Assume that there are 500 records in the Docs_Categories table

All I want is to get the Docs_Categories records that are actually being
used AND... I can't have any duplicates. So if 3 of those 10 Docs_Main
records use the same Docs_Categories.. I don't want to get 3 repeating
records. I need to use this in a DataRelation.

Also, in case you are wondering what in the world I'm trying to do... I
want to present my Data like this:

Product Data
Document Category
Document
Document
Document
Document Category
Document

It's kicking my butt, I need to get the data in this weird backwards

way
to

accomplish this.

If anyone has a better idea that what I'm already trying.. PLEASE share,
because I have several situations like this throughout the site.

Thanks for reading!

-Steve

 

[sklett]

Cool, thanks. I look forward to it!

Sklett:

When you run it how is it working? The first thing that comes to mind is
that if the subquery returns multiple rows if may cause you a problem. I'll
build those tables when I get into work tomorrow and play with it.

Bill

How does this look?

SELECT DISTINCT DC.CatId, DC.Category
FROM Docs_Categories DC, (SELECT DM.CatId FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId


Is that an efficient way to get what I want? Is there a faster way?

OK, I made an UGLY, but working code sample of what I want to do. Hopefully
by looking at this, some of you will be able to tell me how to do this the
correct way

if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId = PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end




Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are linked

by
the

Prod_Docs many-to-many table. I can't figure out how to accomplish this.
Here is more info:
SO let's say there are 10 Docs_Main records in the Prod_Docs table
Assume that there are 500 records in the Docs_Categories table

All I want is to get the Docs_Categories records that are actually being
used AND... I can't have any duplicates. So if 3 of those 10 Docs_Main
records use the same Docs_Categories.. I don't want to get 3 repeating
records. I need to use this in a DataRelation.

Also, in case you are wondering what in the world I'm trying to

do...

I

 

[sklett]

You are correct. I just added some more records..... and BOOM, I see as
many category entries.
Now I really don't know what to do about this....

I'm looking forward to hearing from you, hopefully there is a simple and
solid solution

-SK

Sklett:

When you run it how is it working? The first thing that comes to mind is
that if the subquery returns multiple rows if may cause you a problem. I'll
build those tables when I get into work tomorrow and play with it.

Bill

How does this look?

SELECT DISTINCT DC.CatId, DC.Category
FROM Docs_Categories DC, (SELECT DM.CatId FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId


Is that an efficient way to get what I want? Is there a faster way?

OK, I made an UGLY, but working code sample of what I want to do. Hopefully
by looking at this, some of you will be able to tell me how to do this the
correct way

if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId = PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end




Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are linked

by
the

Prod_Docs many-to-many table. I can't figure out how to accomplish this.
Here is more info:
SO let's say there are 10 Docs_Main records in the Prod_Docs table
Assume that there are 500 records in the Docs_Categories table

All I want is to get the Docs_Categories records that are actually being
used AND... I can't have any duplicates. So if 3 of those 10 Docs_Main
records use the same Docs_Categories.. I don't want to get 3 repeating
records. I need to use this in a DataRelation.

Also, in case you are wondering what in the world I'm trying to

do...

I

 

[sklett]

William-

Here is the latest code from the stored procedure. It has changed slightly
w/ the addition of the DocId field. This will allow me to use DataRelation
object. I will explain better:

I have a DAL method that grabs a DataSet with 2 tables, table[0] contains
all the categories that are called out in the Prod_Docs (many-2-many) table.
In this SELECT statement, I'm getting the ProdId, DocId, CatId, and all
other document specific information

Then in the next select statement, I'm getting the ALL the Categories that
are linked to any of the above documents. The DataRelation object needs 2
constraints:
1) CatId
2) DocId

When I present this data, I use a DataView object on table[1] and use it's
select statement to get just the categories for the Product that I am
currently showing
then as I bind each Category in the DataList, I bind a child DataList to the
Categories children views (docs)

God, I'm even confused now. If you need more explanation, please ask and I
would be happy to help.


SELECT DM.* FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId /* AND PD.ProdId = @ProdId */

-- get the unique categories for the DataRelation
SELECT DC.CatId, DC.Category, TT.DocId, TT.ProdId
FROM Docs_Categories DC, (SELECT DM.CatId, PD.ProdId, DM.DocId FROM
Docs_Main DM, Prod_Docs PD WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId

Sklett:

When you run it how is it working? The first thing that comes to mind is
that if the subquery returns multiple rows if may cause you a problem. I'll
build those tables when I get into work tomorrow and play with it.

Bill

How does this look?

SELECT DISTINCT DC.CatId, DC.Category
FROM Docs_Categories DC, (SELECT DM.CatId FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId


Is that an efficient way to get what I want? Is there a faster way?

OK, I made an UGLY, but working code sample of what I want to do. Hopefully
by looking at this, some of you will be able to tell me how to do this the
correct way

if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId = PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end




Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are linked

by
the

Prod_Docs many-to-many table. I can't figure out how to accomplish this.
Here is more info:
SO let's say there are 10 Docs_Main records in the Prod_Docs table
Assume that there are 500 records in the Docs_Categories table

All I want is to get the Docs_Categories records that are actually being
used AND... I can't have any duplicates. So if 3 of those 10 Docs_Main
records use the same Docs_Categories.. I don't want to get 3 repeating
records. I need to use this in a DataRelation.

Also, in case you are wondering what in the world I'm trying to

do...

I

 

[William Ryan [eMVP]]

Sklett:

Yes, I was playing with it this morning and forced multiple records back and
it blows up.

What I'm thinking is a more simple approach and was wondering if it would
work for you....

Say you pulled all three tables over as straight SELECT statements. Then
you used a DataRelation on them locally to constrain them. You'd know the
ChildRows and ParentRows of wherever you were(if you needed to walk through
it with code) and if Showing it via a GUI is the issue, you'll have no
problem if you use bound controls.

What is the ultimate goal with this data, do you need it for behind the
scenes processing or is it something that will be displayed ?

You are correct. I just added some more records..... and BOOM, I see as
many category entries.
Now I really don't know what to do about this....

I'm looking forward to hearing from you, hopefully there is a simple and
solid solution

-SK

Sklett:

When you run it how is it working? The first thing that comes to mind is
that if the subquery returns multiple rows if may cause you a problem. I'll
build those tables when I get into work tomorrow and play with it.

Bill

How does this look?

SELECT DISTINCT DC.CatId, DC.Category
FROM Docs_Categories DC, (SELECT DM.CatId FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId


Is that an efficient way to get what I want? Is there a faster way?

OK, I made an UGLY, but working code sample of what I want to do.
Hopefully
by looking at this, some of you will be able to tell me how to do

this
the

correct way

if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId =
PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end




Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are

linked

by do... backwards
way

 

[William Ryan [eMVP]]

Yeah, I'm having a bit of trouble following you here. Where are you
defining the DataRelation? How is this supposed to be displayed?

William-

Here is the latest code from the stored procedure. It has changed slightly
w/ the addition of the DocId field. This will allow me to use DataRelation
object. I will explain better:

I have a DAL method that grabs a DataSet with 2 tables, table[0] contains
all the categories that are called out in the Prod_Docs (many-2-many) table.
In this SELECT statement, I'm getting the ProdId, DocId, CatId, and all
other document specific information

Then in the next select statement, I'm getting the ALL the Categories that
are linked to any of the above documents. The DataRelation object needs 2
constraints:
1) CatId
2) DocId

When I present this data, I use a DataView object on table[1] and use it's
select statement to get just the categories for the Product that I am
currently showing
then as I bind each Category in the DataList, I bind a child DataList to the
Categories children views (docs)

God, I'm even confused now. If you need more explanation, please ask and I
would be happy to help.


SELECT DM.* FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId /* AND PD.ProdId = @ProdId */

-- get the unique categories for the DataRelation
SELECT DC.CatId, DC.Category, TT.DocId, TT.ProdId
FROM Docs_Categories DC, (SELECT DM.CatId, PD.ProdId, DM.DocId FROM
Docs_Main DM, Prod_Docs PD WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId

Sklett:

When you run it how is it working? The first thing that comes to mind is
that if the subquery returns multiple rows if may cause you a problem. I'll
build those tables when I get into work tomorrow and play with it.

Bill

How does this look?

SELECT DISTINCT DC.CatId, DC.Category
FROM Docs_Categories DC, (SELECT DM.CatId FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId


Is that an efficient way to get what I want? Is there a faster way?

OK, I made an UGLY, but working code sample of what I want to do.
Hopefully
by looking at this, some of you will be able to tell me how to do

this
the

correct way

if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId =
PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id = object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end




Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are

linked

by do... backwards
way

 

[sklett]

Hi William-

I could do what you suggest if I were working w/ 1 product at a time. To
explain, here is what I want the data to be displayed like

Product
Category
Item
Item
Item
Category
Item
Item
Product2
Category
Item
Item

you get the idea.

I think I have since realized that this not possible using DataRelations, or
if it is, the technique is very odd. What I would need to accomplish the
above is:
a dataset with the following tables
table 0 = All the products
table 1 = All the categories that are referenced by ANY of the items that
are referenced by ANY of the products
table 2 = all the Items that are refernced by ANY of the products

Then I would need to get the category records that are related to each
product, then from there get the item records that are related to each
category.

Does that make sense? The problem comes in that I always have redundant
category records so I ned up with something like this:

Product
CategoryA
Item
CategoryA
Item
CategoryA
Item

when I want
Product
CategoryA
Item
Item
Item

I need to have the ItemId in the table with the Category data, so that makes
the SELECT DISTINCT fail


tabl

Sklett:

Yes, I was playing with it this morning and forced multiple records back and
it blows up.

What I'm thinking is a more simple approach and was wondering if it would
work for you....

Say you pulled all three tables over as straight SELECT statements. Then
you used a DataRelation on them locally to constrain them. You'd know the
ChildRows and ParentRows of wherever you were(if you needed to walk through
it with code) and if Showing it via a GUI is the issue, you'll have no
problem if you use bound controls.

What is the ultimate goal with this data, do you need it for behind the
scenes processing or is it something that will be displayed ?

You are correct. I just added some more records..... and BOOM, I see as
many category entries.
Now I really don't know what to do about this....

I'm looking forward to hearing from you, hopefully there is a simple and
solid solution

-SK

Sklett:

When you run it how is it working? The first thing that comes to mind is
that if the subquery returns multiple rows if may cause you a problem. I'll
build those tables when I get into work tomorrow and play with it.

Bill
How does this look?

SELECT DISTINCT DC.CatId, DC.Category
FROM Docs_Categories DC, (SELECT DM.CatId FROM Docs_Main DM,

Prod_Docs
PD

WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId


Is that an efficient way to get what I want? Is there a faster way?

OK, I made an UGLY, but working code sample of what I want to do.
Hopefully
by looking at this, some of you will be able to tell me how to do this
the
correct way

if exists (select * from dbo.sysobjects where id =
object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId =
PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id =
object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end




Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are

linked

by
the
Prod_Docs many-to-many table. I can't figure out how to accomplish
this.
Here is more info:
SO let's say there are 10 Docs_Main records in the Prod_Docs table
Assume that there are 500 records in the Docs_Categories table

All I want is to get the Docs_Categories records that are actually
being
used AND... I can't have any duplicates. So if 3 of those 10
Docs_Main
records use the same Docs_Categories.. I don't want to get 3 repeating
records. I need to use this in a DataRelation.

Also, in case you are wondering what in the world I'm trying to do...
I
want to present my Data like this:

Product Data
Document Category
Document
Document
Document
Document Category
Document

It's kicking my butt, I need to get the data in this weird backwards
way
to
accomplish this.

If anyone has a better idea that what I'm already trying.. PLEASE
share,
because I have several situations like this throughout the site.

Thanks for reading!

-Steve

 

[sklett]

William, I think I answered you in your earlier post.

as for the DataRelation I am adding them just after I get the fill the
DataSet with the tables of data. Then I utilize the DataSet and
DataRelations in DataLists and nested DataLists

Yeah, I'm having a bit of trouble following you here. Where are you
defining the DataRelation? How is this supposed to be displayed?

William-

Here is the latest code from the stored procedure. It has changed slightly
w/ the addition of the DocId field. This will allow me to use DataRelation
object. I will explain better:

I have a DAL method that grabs a DataSet with 2 tables, table[0] contains
all the categories that are called out in the Prod_Docs (many-2-many) table.
In this SELECT statement, I'm getting the ProdId, DocId, CatId, and all
other document specific information

Then in the next select statement, I'm getting the ALL the Categories that
are linked to any of the above documents. The DataRelation object needs 2
constraints:
1) CatId
2) DocId

When I present this data, I use a DataView object on table[1] and use it's
select statement to get just the categories for the Product that I am
currently showing
then as I bind each Category in the DataList, I bind a child DataList to the
Categories children views (docs)

God, I'm even confused now. If you need more explanation, please ask

and

I
would be happy to help.


SELECT DM.* FROM Docs_Main DM, Prod_Docs PD
WHERE DM.DocId = PD.DocId /* AND PD.ProdId = @ProdId */

-- get the unique categories for the DataRelation
SELECT DC.CatId, DC.Category, TT.DocId, TT.ProdId
FROM Docs_Categories DC, (SELECT DM.CatId, PD.ProdId, DM.DocId FROM
Docs_Main DM, Prod_Docs PD WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId

Sklett:

When you run it how is it working? The first thing that comes to mind is
that if the subquery returns multiple rows if may cause you a problem. I'll
build those tables when I get into work tomorrow and play with it.

Bill
How does this look?

SELECT DISTINCT DC.CatId, DC.Category
FROM Docs_Categories DC, (SELECT DM.CatId FROM Docs_Main DM,

Prod_Docs
PD

WHERE DM.DocId = PD.DocId) TT
WHERE DC.CatId = TT.CatId


Is that an efficient way to get what I want? Is there a faster way?

OK, I made an UGLY, but working code sample of what I want to do.
Hopefully
by looking at this, some of you will be able to tell me how to do this
the
correct way

if exists (select * from dbo.sysobjects where id =
object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at start'
drop table [t_temp]
end

SELECT DM.CatId INTO t_temp FROM Docs_Main AS DM, Prod_Docs AS PD
WHERE DM.DocId = PD.DocId

-- select the categories
SELECT DISTINCT DC.CatId, DC.Category FROM Docs_Categories AS DC, t_temp
WHERE DC.CatId = t_temp.CatId

-- select the docs
SELECT DM.* FROM Docs_Main AS DM, Prod_Docs AS PD WHERE DM.DocId =
PD.DocId


-- clean up the temp table
if exists (select * from dbo.sysobjects where id =
object_id(N'[t_temp]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
print N'dropping at end'
drop table [t_temp]
end




Hi-

I have 3 tables

Prod_Docs (many-to-many)
[ProdId (fk) ] [DocId(fk) ]

Docs_Main
[DocId(pk)] [CatId(fk)]

Docs_Categories
[CatId(pk)]

I want to select all the records in Docs_Categories that are

linked

by
the
Prod_Docs many-to-many table. I can't figure out how to accomplish
this.
Here is more info:
SO let's say there are 10 Docs_Main records in the Prod_Docs table
Assume that there are 500 records in the Docs_Categories table

All I want is to get the Docs_Categories records that are actually
being
used AND... I can't have any duplicates. So if 3 of those 10
Docs_Main
records use the same Docs_Categories.. I don't want to get 3 repeating
records. I need to use this in a DataRelation.

Also, in case you are wondering what in the world I'm trying to do...
I
want to present my Data like this:

Product Data
Document Category
Document
Document
Document
Document Category
Document

It's kicking my butt, I need to get the data in this weird backwards
way
to
accomplish this.

If anyone has a better idea that what I'm already trying.. PLEASE
share,
because I have several situations like this throughout the site.

Thanks for reading!

-Steve