Problem querying 2 tables & output single rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem querying 2 tables & output single rows

Author

Topic

twentythree
Starting Member

2 Posts

Posted - 2005-01-11 : 18:17:48

I've been working on this all day yesterday, have read as much as I can on joins but still can't get this to work. I'm at my wits end here. It must be something simple but I'm just missing it.

I basically have two tables. One that stores products, and one that stores there details. I'm trying to query both tables so that it shows ONLY one product, but lists their details (field: productdetail1) and their price (also in the details table).

But right now I basically get one row for every single detail. And this is not what I want. I've tried outer joins, and they don't work either.

This is what I have now:

SELECT     j.jewelry AS Expr1, p.price AS Expr2, p.ProductDetail1 AS Expr3, p.ProductDetailID AS Expr4, j.*
FROM         bodyart.jewelry j INNER JOIN
                      bodyart.ProductDetails p ON p.ProductID = j.ProductID
WHERE     (j.jewelry = N'tapers')

Any ideas of what I'm doing wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-11 : 18:27:18

If a product has more than one detail rows, which one do you want to show?

Tara

twentythree
Starting Member

2 Posts

Posted - 2005-01-11 : 18:38:15

Let me give a little background as to what I want because maybe I'm not even doing this right.
I'm basically doing an advanced search feature on our website where I need to search two seperate tables. Right now searching two tables with an inner join I get the same products output multiple times for each detail.

Basically I want the user to be able to search and then have it only show them one product (even if it has multiple details). I also want them to be able to search details and only have it pull up the one main product.

For instance I'm wanting to have the ability to search by price (say all items that are under $10 or whatever the form variable passes to the search query). But right now when I do that it'll give me 10 details all for one product (thus showing the same product like 10 times on teh search results page). I just want to have that condensed down to only show one product.

I'm basically trying to code this in a SQL view so that on the page I just have to access that view. Or am I going about this all wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-01-11 : 19:06:07

What you are doing is fine so far. But in order to pull up just one detail for a product, you need to pick which detail to pull up. You will be using GROUP BY in your query.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources