SQL question

  • Thread starter Thread starter sklett
  • Start date Start date
S

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
 
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
 
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?

sklett said:
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 said:
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
Click to expand...
Click to expand...
 
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
sklett said:
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?

sklett said:
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 said:
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Cool, thanks. I look forward to it!


William Ryan said:
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
sklett said:
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?

sklett said:
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
Click to expand...
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
Click to expand...
Click to expand...
do...
I
Click to expand...
Click to expand...
 
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

William Ryan said:
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
sklett said:
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?

sklett said:
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
Click to expand...
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
Click to expand...
Click to expand...
do...
I
Click to expand...
Click to expand...
 
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





William Ryan said:
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
sklett said:
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?

sklett said:
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
Click to expand...
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
Click to expand...
Click to expand...
do...
I
Click to expand...
Click to expand...
 
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 ?


sklett said:
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

William Ryan said:
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
sklett said:
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
Click to expand...
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
Click to expand...
Click to expand...
linked
by do... backwards
way
Click to expand...
Click to expand...
 
Yeah, I'm having a bit of trouble following you here. Where are you
defining the DataRelation? How is this supposed to be displayed?
sklett said:
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





William Ryan said:
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
sklett said:
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
Click to expand...
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
Click to expand...
Click to expand...
linked
by do... backwards
way
Click to expand...
Click to expand...
 
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
William Ryan said:
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 ?


sklett said:
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

William Ryan said:
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,
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
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


William Ryan said:
Yeah, I'm having a bit of trouble following you here. Where are you
defining the DataRelation? How is this supposed to be displayed?
sklett said:
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
Click to expand...
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





William Ryan said:
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,
Click to expand...
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
Click to expand...
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top