problem with a DISTINCT query

C

CharlesKiel

I'm having a problem with the following query being used on an access
db on my website:

SELECT Distinct (videos.id), Videos.*, chi.airdate FROM Videos, chi
WHERE Chi.video = Videos.id and videos.promoter is not NULL UNION
SELECT Distinct (videos.id), Videos.*, chi.airdate FROM Videos, chi
WHERE Chi.video = Videos.id and videos.promoter is not NULL order by
artist, title

[Videos] looks like this

[id] [artist] [title] [promoter]

[chi], the list that keeps track of what videos are aired, looks like
this

[video] [airdate] [type]

where chi.video = videos.id


It returns the list with multiple records with the same video.id and a
different chi.airdate . The result I want to achieve is to return
records without repeating those with the same videos.id number. This
way the report that prints out simply shows that a video was run, not
showing how many times. How do I do this?

counting how many times a video runs is something I'm going to figure
out later.
 
K

Ken Sheridan

If you remove the airdate column from the SELECT clause then the query will
return DISTINCT rows from the Videos table only, the join with the chi table
being included only to restrict the rows returned to those where there is a
matching row in chi. Also the UNION operation here serves no purpose as far
as I can see. So the query would be:

SELECT DISTINCT Videos.*
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
WHERE promoter IS NOT NULL
ORDER BY artist, title;

You only need the WHERE promoter IS NOT NULL clause if you want to exclude
rows from the result table where there is no value in the promoter column.
Is that what's wanted?

Possible variations could be to return distinct video data, and the latest
date when each was aired:

SELECT id, artist, title, promoter,
MAX(airdate) As LastAired
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
GROUP BY id, artist, title, promoter;

or the earliest date:

SELECT id, artist, title, promoter,
MIN(airdate) As FirstAired
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
GROUP BY id, artist, title, promoter;

BTW to count the times each video has been aired join the tables, group by
id etc. and count the rows per video:

SELECT id, artist, title, promoter,
COUNT(*) As TimesAired
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
GROUP BY id, artist, title, promoter;

Ken Sheridan
Stafford, England
 
C

CharlesKiel

If you remove the airdate column from the SELECT clause then the query will
return DISTINCT rows from the Videos table only, the join with the chi table
being included only to restrict the rows returned to those where there is a
matching row in chi.  Also the UNION operation here serves no purpose asfar
as I can see.  So the query would be:

SELECT DISTINCT Videos.*
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
WHERE promoter IS NOT NULL
ORDER BY artist, title;

You only need the WHERE promoter IS NOT NULL clause if you want to exclude
rows from the result table where there is no value in the promoter column. 
Is that what's wanted?

Possible variations could be to return distinct video data, and the latest
date when each was aired:

SELECT id, artist, title, promoter,
MAX(airdate) As LastAired
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
GROUP BY id, artist, title, promoter;

or the earliest date:

SELECT id, artist, title, promoter,
MIN(airdate) As FirstAired
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
GROUP BY id, artist, title, promoter;

BTW to count the times each video has been aired join the tables, group by
id etc. and count the rows per video:

SELECT id, artist, title, promoter,
COUNT(*) As TimesAired
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
GROUP BY id, artist, title, promoter;

Ken Sheridan
Stafford, England



[email protected] said:
I'm having a problem with the following query being used on an access
db on my website:
Click to expand...
SELECT Distinct (videos.id), Videos.*, chi.airdate FROM Videos, chi
WHERE Chi.video = Videos.id and videos.promoter is not NULL UNION
SELECT Distinct (videos.id), Videos.*, chi.airdate FROM Videos, chi
WHERE Chi.video = Videos.id and videos.promoter is not NULL order by
artist, title
Click to expand...
[Videos] looks like this
Click to expand...
[id] [artist] [title] [promoter]
Click to expand...
[chi], the list that keeps track of what videos are aired, looks like
this
Click to expand...
[video] [airdate] [type]
Click to expand...
where chi.video = videos.id
Click to expand...
It returns the list with multiple records with the same video.id and a
different chi.airdate . The result I want to achieve is to return
records without repeating those with the same videos.id number. This
way the report that prints out simply shows that a video was run, not
showing how many times. How do I do this?
Click to expand...
counting how many times a video runs is something I'm going to figure
out later.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Sweet. That works.

The UNION thing, I don't remember what my logic was behind that. It
was getting late and I was trying anything, and i forgot to take it
out.

So now I have another problem. In the query we came up with:

SELECT id, artist, title, promoter, COUNT(*) As mySQL2 FROM Videos
INNER JOIN Chi ON Chi.video = Videos.id GROUP BY id, artist, title,
promoter

I'm missing "Label" to give me the record label. So when I change it
to this:

SELECT id, artist, title, promoter, label, COUNT(*) As mySQL2 FROM
Videos INNER JOIN Chi ON Chi.video = Videos.id GROUP BY id, artist,
title, promoter

I get this:
"You tried to execute a query that does not include the specified
expression 'label' as part of an aggregate function. "

How do I fix this?
 
J

John Spencer

Add label to the group by clause


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

If you remove the airdate column from the SELECT clause then the query
will
return DISTINCT rows from the Videos table only, the join with the chi
table
being included only to restrict the rows returned to those where there is
a
matching row in chi. Also the UNION operation here serves no purpose as
far
as I can see. So the query would be:

SELECT DISTINCT Videos.*
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
WHERE promoter IS NOT NULL
ORDER BY artist, title;

You only need the WHERE promoter IS NOT NULL clause if you want to exclude
rows from the result table where there is no value in the promoter column.
Is that what's wanted?

Possible variations could be to return distinct video data, and the latest
date when each was aired:

SELECT id, artist, title, promoter,
MAX(airdate) As LastAired
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
GROUP BY id, artist, title, promoter;

or the earliest date:

SELECT id, artist, title, promoter,
MIN(airdate) As FirstAired
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
GROUP BY id, artist, title, promoter;

BTW to count the times each video has been aired join the tables, group by
id etc. and count the rows per video:

SELECT id, artist, title, promoter,
COUNT(*) As TimesAired
FROM Videos INNER JOIN Chi
ON Chi.video = Videos.id
GROUP BY id, artist, title, promoter;

Ken Sheridan
Stafford, England



[email protected] said:
I'm having a problem with the following query being used on an access
db on my website:
Click to expand...
SELECT Distinct (videos.id), Videos.*, chi.airdate FROM Videos, chi
WHERE Chi.video = Videos.id and videos.promoter is not NULL UNION
SELECT Distinct (videos.id), Videos.*, chi.airdate FROM Videos, chi
WHERE Chi.video = Videos.id and videos.promoter is not NULL order by
artist, title
Click to expand...
[Videos] looks like this
Click to expand...
[id] [artist] [title] [promoter]
Click to expand...
[chi], the list that keeps track of what videos are aired, looks like
this
Click to expand...
[video] [airdate] [type]
Click to expand...
where chi.video = videos.id
Click to expand...
It returns the list with multiple records with the same video.id and a
different chi.airdate . The result I want to achieve is to return
records without repeating those with the same videos.id number. This
way the report that prints out simply shows that a video was run, not
showing how many times. How do I do this?
Click to expand...
counting how many times a video runs is something I'm going to figure
out later.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Sweet. That works.

The UNION thing, I don't remember what my logic was behind that. It
was getting late and I was trying anything, and i forgot to take it
out.

So now I have another problem. In the query we came up with:

SELECT id, artist, title, promoter, COUNT(*) As mySQL2 FROM Videos
INNER JOIN Chi ON Chi.video = Videos.id GROUP BY id, artist, title,
promoter

I'm missing "Label" to give me the record label. So when I change it
to this:

SELECT id, artist, title, promoter, label, COUNT(*) As mySQL2 FROM
Videos INNER JOIN Chi ON Chi.video = Videos.id GROUP BY id, artist,
title, promoter

I get this:
"You tried to execute a query that does not include the specified
expression 'label' as part of an aggregate function. "

How do I fix this?
 
C

CharlesKiel

You guys have been a big big help!
Thanks a lot to the both of you!

I have a lot of work to do yet, so I'll be back!

Peace out.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Frontpage database query paramater help please 6
Scroll does not show preview 0
SELECT DISTINCT query 8
Creating a Search using a combo box and then creating a report. 4
Distinct not working in 2007 as in 2003 7
Help on Counting and grouping distinct records 3
Problems with the screentip of an hyperlink Word to PDF 2
Select Distinct query 8

Top