SQL ?: How to combine results from multiple tables

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

If that's even the best way, I don't know.

Here are the basic tables I have:

Tbl_Products
[ProdId (pk)] [Name]

Tbl_Prod_Faqs
[ProdId(fk)] [FaqId(fk)]

Tbl_Prod_Images
[ProdId(fk)] [ImgId(fk)]


OK, I would like to execute a query that will return all the Products from
Tbl_Products that have entries in Tbl_Prod_Faqs, Tbl_Prod_Images and any
other many-to-many tables that have a ProdId (fk)
I can think of very clunky, ugly ways to do this, but I am looking for the
correct way to ask "Gimme all the records that have Faq data, Images, Etc"

This is what I came up with at first
SELECT DISTINCT PF.ProdId FROM Tbl_Prod_Faqs AS PF
LEFT JOIN Tbl_Products AS P ON PF.ProdId = P.ProdId


So this will get me the Product data for anything listed in the
Tbl_Prod_faqs, but what about Tbl_Prod_Images? How can I do that one
without having a second SELECT statement? Then, if I did have 2 select
statements, how can I merge them using my Stored Procedure into 1 table or
result? I don't even know where to start.

Despite my basic ".....heeeeelp me" approach to this, if someone could
simply send me in the right direction, I can figure it out.. currently I
just can't see the whole picture or a good starting point.

Any help appreciated.

Thanks,
Steve
 
I think you might be correct. I made all these custom types and then looped
throught the result Datatable, parsing out the data I wanted and it's just
UGLY. it works, it's solid, but it stinky, ugly code so I'm now willing to
look at the scary world of DataRelations ;)

I don't know anything about them, but I'm gonna look for some articles.
Thanks for the suggestion though, I hope it works out for me.

-Steve


William Ryan said:
I think a DataRelation object is what you need instead of using the joins.
Steve said:
If that's even the best way, I don't know.

Here are the basic tables I have:

Tbl_Products
[ProdId (pk)] [Name]

Tbl_Prod_Faqs
[ProdId(fk)] [FaqId(fk)]

Tbl_Prod_Images
[ProdId(fk)] [ImgId(fk)]


OK, I would like to execute a query that will return all the Products from
Tbl_Products that have entries in Tbl_Prod_Faqs, Tbl_Prod_Images and any
other many-to-many tables that have a ProdId (fk)
I can think of very clunky, ugly ways to do this, but I am looking for the
correct way to ask "Gimme all the records that have Faq data, Images, Etc"

This is what I came up with at first
SELECT DISTINCT PF.ProdId FROM Tbl_Prod_Faqs AS PF
LEFT JOIN Tbl_Products AS P ON PF.ProdId = P.ProdId


So this will get me the Product data for anything listed in the
Tbl_Prod_faqs, but what about Tbl_Prod_Images? How can I do that one
without having a second SELECT statement? Then, if I did have 2 select
statements, how can I merge them using my Stored Procedure into 1 table or
result? I don't even know where to start.

Despite my basic ".....heeeeelp me" approach to this, if someone could
simply send me in the right direction, I can figure it out.. currently I
just can't see the whole picture or a good starting point.

Any help appreciated.

Thanks,
Steve
Click to expand...
Click to expand...
 
OK, I have a bit of a problem here. I was reading this article here:
http://msdn.microsoft.com/msdnmag/issues/02/11/datapoints/default.aspx

And everything was sounding easy. Then I thought about HOW I wantedt o
display my Data VS how it's stored in teh database.. and that is the tricky
part.
Here is a different table breakdown
Tbl_Products
[ ProdId(pk) ] [ PartNum ]

Tbl_Prod_Faqs (may-to-many)
[ ProdId (fk) ] [ FaqId(fk) ]

Tbl_Faq_Main
[ FaqId(pk) ] [ CatId (fk) ] [ Question ] [ Answer ]

Tbl_Faq_Categories
[ CatId(pk) ] [ Category ]

so that seems fine to me.

I want to display it hierarchically like this:

ProductA
FaqCategory1
Faqs[]
FaqCategory2
Faqs[]
ProductB
FaqCategory1
Faqs[]
FaqCategory2
Faqs[]

You get the idea. So I have a series of nested controls and I need to pass
them the appropriate data source. Using 4 tables linked with DataRelations,
I don't see how I can easily get the FaqCategories for a given product.
Unless I threw them into their own table? Like a many-to-many "products to
FaqCategories" table, but now I'm losing focus...

what do you suggest? What is the cleanest, nicest way to handle my
situation here?





William Ryan said:
I think a DataRelation object is what you need instead of using the joins.
Steve said:
If that's even the best way, I don't know.

Here are the basic tables I have:

Tbl_Products
[ProdId (pk)] [Name]

Tbl_Prod_Faqs
[ProdId(fk)] [FaqId(fk)]

Tbl_Prod_Images
[ProdId(fk)] [ImgId(fk)]


OK, I would like to execute a query that will return all the Products from
Tbl_Products that have entries in Tbl_Prod_Faqs, Tbl_Prod_Images and any
other many-to-many tables that have a ProdId (fk)
I can think of very clunky, ugly ways to do this, but I am looking for the
correct way to ask "Gimme all the records that have Faq data, Images, Etc"

This is what I came up with at first
SELECT DISTINCT PF.ProdId FROM Tbl_Prod_Faqs AS PF
LEFT JOIN Tbl_Products AS P ON PF.ProdId = P.ProdId


So this will get me the Product data for anything listed in the
Tbl_Prod_faqs, but what about Tbl_Prod_Images? How can I do that one
without having a second SELECT statement? Then, if I did have 2 select
statements, how can I merge them using my Stored Procedure into 1 table or
result? I don't even know where to start.

Despite my basic ".....heeeeelp me" approach to this, if someone could
simply send me in the right direction, I can figure it out.. currently I
just can't see the whole picture or a good starting point.

Any help appreciated.

Thanks,
Steve
Click to expand...
Click to expand...
 
Back
Top