Exclude results using WHERE statement in SQL

Question

I have a complex query to display some products in different shops. I'm trying to exclude some results but I'm not be able to do it. I have a table looking like this:

ProName     ProColor     ProSize      ShopName      ShopAddress
Pro1        col1         10           Shop1         Address1
Pro2        col2         12           Shop2         Address2
Pro3        col3         12           Shop1         Address1
Pro3        col3         12           Shop2         Address2

And the query (essential):

SELECT Products.ProId, Products.ProName, Products.ProColor, Products.ProSize, Shop.ShopId, Shop.ShopName, Shop.ShopAddress Stock.StockId, Stock.StoProId, Stock.StoShopId
FROM Products, Shop, Stock
WHERE ((Stock.StoProId = Products.ProId) AND (Stock.StoShopId = Shop.ShopId))

The above table has 4 columns. When Product and Color and Size are equals, but I have the product in different shops, I only want to show once in the table like this (no matter wich ShopName shows, but only one):

ProName     ProColor     ProSize      ShopName      ShopAddress
Pro1        col1         10           Shop1         Address1
Pro2        col2         12           Shop2         Address2
Pro3        col3         12           Shop1         Address1

Please, can someone help me?

This is not complex... but you definitely should think about using joins in your query for a start — Laurent S., Apr 16 '13 at 09:14
The original table is more complex than this, I only exposed the essence of that. — user2285630, Apr 16 '13 at 09:28

score 1 · Answer 1 · answered Apr 16 '13 at 09:24

1

A simple GROUP BY clause at the end of the request should work

GROUP BY Products.ProColor, Products.ProSize

but that's a strange query.

answered Apr 16 '13 at 09:24

adrilomb

71
3

The GROUP BY in ADF / JDeveloper force me to include all the items of the first select... – user2285630 Apr 16 '13 at 09:27
are you using a query/read-only view object or entity view objecT? – GavinWoods May 08 '13 at 14:31

Exclude results using WHERE statement in SQL

1 Answers1