SELECT DISTINCT?

Report · Jan 12, 2007

When I just - SELECT DISTINCT atblProducts.ID AS ProductID, it selects
distinct Product IDs but when I add the rest of them to the select, it
doesn't select the ProductID as DISTINCT.

Lionstone?

SELECT DISTINCT
dbo.atblProducts.ID AS ProductID, dbo.Categories.ID AS Categories,
dbo.atblProducts.Price AS Price, dbo.atblDescription.Paragraph AS
Description, dbo.atblProducts.Name AS Name
FROM dbo.atblProducts INNER JOIN dbo.Groups ON
dbo.atblProducts.ID = dbo.Groups.pid INNER JOIN dbo.PCat ON
dbo.Groups.gid = dbo.PCat.gid INNER JOIN dbo.Categories ON dbo.PCat.cid
= dbo.Categories.ID LEFT OUTER JOINdbo.atblDescription ON dbo.Groups.pid
= dbo.atblDescription.PID
WHERE (dbo.atblProducts.Price IS NOT NULL)
AND(dbo.atblProducts.Price <> 0)
ORDER BY dbo.atblProducts.ID

Report · Jan 12, 2007

I've an article on this subject:

http://www.charon.co.uk/content.aspx?CategoryID=27&ArticleID=50

In essence, if the same product has multiple categories or prices, then
those rows will be selected as well. In effect, distinct rows are returned.
Not specific to one field.

--
Jules
http://www.charon.co.uk/charoncart
Charon Cart 3
Shopping Cart Extension for Dreamweaver MX/MX 2004

Report · Jan 12, 2007

I don't understand the solution.

I've tried plugging in some things but none of them worked.

Report · Jan 12, 2007

Julian,

Any ideas how to get this to work?

Report · Jan 12, 2007

Is the Newsgroup down right now?

Report · Jan 12, 2007

SELECT DISTINCT dbo.Categories.ID AS Categories, dbo.atblProducts.ID AS ProductID, dbo.atblProducts.Name AS Name
FROM dbo.atblProducts INNER JOIN
dbo.Groups ON dbo.atblProducts.ID = dbo.Groups.pid INNER JOIN
dbo.PCat ON dbo.Groups.gid = dbo.PCat.gid INNER JOIN
dbo.Categories ON dbo.PCat.cid = dbo.Categories.ID
GROUP BY dbo.atblProducts.ID, dbo.atblProducts.Name, dbo.Categories.ID

Report · Jan 12, 2007

SELECT MIN(dbo.Categories.ID) AS Categories, MIN(dbo.atblProducts.ID) AS
ProductID, MIN(dbo.atblProducts.Name) AS Name
FROM dbo.atblProducts INNER JOIN
dbo.Groups ON dbo.atblProducts.ID = dbo.Groups.pid INNER JOIN
dbo.PCat ON dbo.Groups.gid = dbo.PCat.gid INNER JOIN
dbo.Categories ON dbo.PCat.cid = dbo.Categories.ID
GROUP BY dbo.atblProducts.ID, dbo.atblProducts.Name, dbo.Categories.ID

Report · Jan 15, 2007

OK, how about a different approach.

I can't seem to get a DISTINCT list of items

If you look below at the results, there are duplicate Product IDs: 197,
200, 200-G

What is obvious is the fact that it's not actually selecting DISTINCT
rows of ProductID

ProductID | Name | categories
196 | Exploring the Earth Moon System Teacher's Guide | 3
197 | Exploring the Human Body Teacher's Guide | 2
197 | Exploring the Human Body Teacher's Guide | 11
198 | Exploring the Earth's History Teacher's Guide | 10
200 | Solar System Simulator | 3
200 | Solar System Simulator | 38
200-G | Solar System Simulator (German) | 3
200-G | Solar System Simulator (German) | 38

These results come from this query below:
------------------------------------------
SELECT DISTINCT dbo.atblProducts.ID AS ProductID, dbo.atblProducts.Name
AS Name, dbo.Categories.ID AS Categories
FROM dbo.atblProducts
INNER JOIN dbo.Groups ON dbo.atblProducts.ID = dbo.Groups.pid
INNER JOIN dbo.PCat ON dbo.Groups.gid = dbo.PCat.gid
INNER JOIN dbo.Categories ON dbo.PCat.cid = dbo.Categories.ID
GROUP BY dbo.atblProducts.ID, dbo.atblProducts.Name, dbo.Categories.ID

Report · Jan 15, 2007

You are not selecting distinct product ids, you are selecting distinct rows.
The 3rd field is different (category id). If you want only distinct product
ids, you need to select distinct product ids only. If you need the name as
well, select that. Selecting the 3rd field makes the rows different. You can
get around it by using a MIN or MAX on the 3rd field, but that will only
give you one of the fields (for example in product 197, you have 2 and 11 --
you would only get 2 in the result.)

SELECT DISTINCT dbo.atblProducts.ID AS ProductID, dbo.atblProducts.Name
AS Name, MIN(dbo.Categories.ID) AS Categories
FROM dbo.atblProducts
INNER JOIN dbo.Groups ON dbo.atblProducts.ID = dbo.Groups.pid
INNER JOIN dbo.PCat ON dbo.Groups.gid = dbo.PCat.gid
INNER JOIN dbo.Categories ON dbo.PCat.cid = dbo.Categories.ID
GROUP BY dbo.atblProducts.ID, dbo.atblProducts.Name, dbo.Categories.ID

Tom Muck
http://www.tom-muck.com

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:eog6rg$iq3$1@forums.macromedia.com...
> OK, how about a different approach.
>
> I can't seem to get a DISTINCT list of items
>
> If you look below at the results, there are duplicate Product IDs: 197,
> 200, 200-G
>
> What is obvious is the fact that it's not actually selecting DISTINCT rows
> of ProductID
>
> ProductID | Name | categories
> 196 | Exploring the Earth Moon System Teacher's Guide | 3
> 197 | Exploring the Human Body Teacher's Guide | 2
> 197 | Exploring the Human Body Teacher's Guide | 11
> 198 | Exploring the Earth's History Teacher's Guide | 10
> 200 | Solar System Simulator | 3
> 200 | Solar System Simulator | 38
> 200-G | Solar System Simulator (German) | 3
> 200-G | Solar System Simulator (German) | 38
>
> These results come from this query below:
> ------------------------------------------
> SELECT DISTINCT dbo.atblProducts.ID AS ProductID, dbo.atblProducts.Name AS
> Name, dbo.Categories.ID AS Categories
> FROM dbo.atblProducts
> INNER JOIN dbo.Groups ON dbo.atblProducts.ID = dbo.Groups.pid
> INNER JOIN dbo.PCat ON dbo.Groups.gid = dbo.PCat.gid
> INNER JOIN dbo.Categories ON dbo.PCat.cid = dbo.Categories.ID
> GROUP BY dbo.atblProducts.ID, dbo.atblProducts.Name, dbo.Categories.ID

Report · Jan 15, 2007

This is giving the same result.

Report · Jan 15, 2007

Tom Muck wrote:
> You are not selecting distinct product ids, you are selecting distinct rows.
> The 3rd field is different (category id). If you want only distinct product
> ids, you need to select distinct product ids only. If you need the name as
> well, select that. Selecting the 3rd field makes the rows different. You can
> get around it by using a MIN or MAX on the 3rd field, but that will only
> give you one of the fields (for example in product 197, you have 2 and 11 --
> you would only get 2 in the result.)
>
> SELECT DISTINCT dbo.atblProducts.ID AS ProductID, dbo.atblProducts.Name
> AS Name, MIN(dbo.Categories.ID) AS Categories
> FROM dbo.atblProducts
> INNER JOIN dbo.Groups ON dbo.atblProducts.ID = dbo.Groups.pid
> INNER JOIN dbo.PCat ON dbo.Groups.gid = dbo.PCat.gid
> INNER JOIN dbo.Categories ON dbo.PCat.cid = dbo.Categories.ID
> GROUP BY dbo.atblProducts.ID, dbo.atblProducts.Name, dbo.Categories.ID

OK, thanks Tom it now makes a little more sense when you said I am
selecting DISTINCT rows.

I really only want one result / ProductID. Because there are categories
creating multiple listings, the ProductID is being pulled in more than
once. OK, That makes sense. What I really want to do is create a query
that doesn't display or SELECT the other fields. However, I need them to
display. How do I JOIN these tables 4 tables without pulling in
multiples of the same thing?

Is this a case in which I need a subquery?

Also, I don't really understand the GROUP BY "thingy" ;) Anyways,

Report · Jan 15, 2007

If you don't want multiple categories, you can't join on the categories
table. It doesn't make sense when you say you don't want to select the
category but you want to display it. You have different categories for the
same product, so if you want to display them you have to select them.

Without seeing your data it is hard to figure out what you are doing, but
you can probably either get rid of the group by completely or change it to
this, assuming you used MIN on the category:

GROUP BY dbo.atblProducts.ID, dbo.atblProducts.Name

Alternatively, you can write a function in SQL to string your categories
into a list.

Tom

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:eog923$le9$1@forums.macromedia.com...
> Tom Muck wrote:
>> You are not selecting distinct product ids, you are selecting distinct
>> rows. The 3rd field is different (category id). If you want only distinct
>> product ids, you need to select distinct product ids only. If you need
>> the name as well, select that. Selecting the 3rd field makes the rows
>> different. You can get around it by using a MIN or MAX on the 3rd field,
>> but that will only give you one of the fields (for example in product
>> 197, you have 2 and 11 -- you would only get 2 in the result.)
>>
>> SELECT DISTINCT dbo.atblProducts.ID AS ProductID, dbo.atblProducts.Name
>> AS Name, MIN(dbo.Categories.ID) AS Categories
>> FROM dbo.atblProducts
>> INNER JOIN dbo.Groups ON dbo.atblProducts.ID = dbo.Groups.pid
>> INNER JOIN dbo.PCat ON dbo.Groups.gid = dbo.PCat.gid
>> INNER JOIN dbo.Categories ON dbo.PCat.cid = dbo.Categories.ID
>> GROUP BY dbo.atblProducts.ID, dbo.atblProducts.Name, dbo.Categories.ID
>
> OK, thanks Tom it now makes a little more sense when you said I am
> selecting DISTINCT rows.
>
> I really only want one result / ProductID. Because there are categories
> creating multiple listings, the ProductID is being pulled in more than
> once. OK, That makes sense. What I really want to do is create a query
> that doesn't display or SELECT the other fields. However, I need them to
> display. How do I JOIN these tables 4 tables without pulling in multiples
> of the same thing?
>
> Is this a case in which I need a subquery?
>
> Also, I don't really understand the GROUP BY "thingy" ;) Anyways,

Report · Jan 15, 2007

OK, let me try again.

One table unique products.
One table of categories that these products will show up in. One table
may have multiple ...I may have figured it out.

Hold on.

Tom Muck wrote:
> If you don't want multiple categories, you can't join on the categories
> table. It doesn't make sense when you say you don't want to select the
> category but you want to display it. You have different categories for the
> same product, so if you want to display them you have to select them.
>
> Without seeing your data it is hard to figure out what you are doing, but
> you can probably either get rid of the group by completely or change it to
> this, assuming you used MIN on the category:
>
> GROUP BY dbo.atblProducts.ID, dbo.atblProducts.Name
>
> Alternatively, you can write a function in SQL to string your categories
> into a list.

Report · Jan 15, 2007

OK, I now realize that what I was asking for didn't make logical sense.

Thanks Tom, for your help. I'm sure I'll be back for something similar
but I also think this helped a great deal. I just need a mini course on
GROUP BY. I've pretty much settled that I need to get an SQL book.

By the way, would this "function" thing that you mentioned allow me to
create a sub query that would do what I want?

What I really want is a list of all the products and then within each
product, listing, I want it to print out all the categories that each is
in without pulling in duplicate product IDs.

Part of the reason this was such a problem for me is because I need to
export it out to an excel file so that I can put it all on a disk so
people can have a list of all our products and associated images. It's
not dynamic so what you see is really what you get. Duplicates would
only confuse. I'll probably just have to create another file with all
the categories listed separately.

That will probably work well enough.

Thanks

Report · Jan 15, 2007

What I really want is a list of all the products and then within each
product, listing, I want it to print out all the categories that each is
in without pulling in duplicate product IDs.

I understand now that this can't be done but I can display them GROUP BY...

For curiosities sake, what I am now trying to figure out is how do I
group by categories/category so that it pulls in products but pulls them
in according to their categories/category

I probably already have the answer to that now that I know the nature of
what I need.

I'll let you know.

Thanks

Report · Jan 15, 2007

"Lee" <lee_nospam_@artjunky.com> wrote in message
news:eogd9j$qin$1@forums.macromedia.com...
> For curiosities sake, what I am now trying to figure out is how do I group
> by categories/category so that it pulls in products but pulls them in
> according to their categories/category

You can:
1. Sort by category, only displaying a category header when it changes.
2. Sort by product, listing additional categories until the product changes.
3. Use the data shaping provider to get a shaped (heirarchical) recordset
where the "category" field is another recordset containing all of the
product's categories.

You can read up on the data shaping provider at www.4guysfromrolla.com