creating recordset with only one entry per user

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi

i am trying to write a query to produce a descending recordset of photo_id
but only one from each user e.g

if these are the top records

photo_id 150 m_name dave
photo_id 149 m_name dave
photo_id 148 m_name dave
photo_id 147 m_name john
photo_id 146 m_name john
photo_id 145 m_name fred

i want the query to produce this

photo_id 150 m_name dave
photo_id 147 m_name john
photo_id 145 m_name fred

and so on, there are other fields also but that gives you the idea i hope.
what i have come up with is this

SELECT MAX(FORUM_ALBUM.Photo_id) AS ID,
FORUM_ALBUM.Photo_Name,FORUM_ALBUM_USERS.M_Name,FO RUM_ALBUM.Member_id
FROM FORUM_ALBUM, FORUM_ALBUM_USERS
WHERE FORUM_ALBUM.Member_id=FORUM_ALBUM_USERS.MEMBER_ID AND
FORUM_ALBUM.Photo_Status=1
GROUP BY FORUM_ALBUM.Photo_id,FORUM_ALBUM.Photo_Name,
FORUM_ALBUM_USERS.M_Name, FORUM_ALBUM.Member_id
ORDER BY FORUM_ALBUM.Photo_id DESC;

this gives me the records i want in the correct order but it gives multiple
instances of each M_Name instead of just one record for each M_Name

i hope i have explained this clearly enough

thanks

Dave
 
Davecl said:
i am trying to write a query to produce a descending recordset of photo_id
but only one from each user e.g

if these are the top records

photo_id 150 m_name dave
photo_id 149 m_name dave
photo_id 148 m_name dave
photo_id 147 m_name john
photo_id 146 m_name john
photo_id 145 m_name fred

i want the query to produce this

photo_id 150 m_name dave
photo_id 147 m_name john
photo_id 145 m_name fred

and so on, there are other fields also but that gives you the idea i hope.
what i have come up with is this

SELECT MAX(FORUM_ALBUM.Photo_id) AS ID,
FORUM_ALBUM.Photo_Name,FORUM_ALBUM_USERS.M_Name,FO RUM_ALBUM.Member_id
FROM FORUM_ALBUM, FORUM_ALBUM_USERS
WHERE FORUM_ALBUM.Member_id=FORUM_ALBUM_USERS.MEMBER_ID AND
FORUM_ALBUM.Photo_Status=1
GROUP BY FORUM_ALBUM.Photo_id,FORUM_ALBUM.Photo_Name,
FORUM_ALBUM_USERS.M_Name, FORUM_ALBUM.Member_id
ORDER BY FORUM_ALBUM.Photo_id DESC;

this gives me the records i want in the correct order but it gives multiple
instances of each M_Name instead of just one record for each M_Name
Click to expand...


You're probably getting different photo names for the
different photo ids. Since you are not using the photo name
field in the result, just get rid of the photo name field.
 
Davecl said:
hi

i am trying to write a query to produce a descending recordset of photo_id
but only one from each user e.g

if these are the top records
Click to expand...

i want the query to produce this

photo_id 150 m_name dave
photo_id 147 m_name john
photo_id 145 m_name fred

and so on, there are other fields also but that gives you the idea i hope.
what i have come up with is this
Click to expand...

this gives me the records i want in the correct order but it gives multiple
instances of each M_Name instead of just one record for each M_Name

i hope i have explained this clearly enough

thanks

Dave
Click to expand...

Dave,

Tables:

Please forgive the dates appended to the table names.

Note: Because I was not sure of the schema for FORUM_ALBUM, or
FORUM_ALBUM_USERS, the test table below is based on the sample data
give.

Note: The original query used pre-SQL-92 ANSI Standard joins.
Although my query below does not have any joins because I am using
only one table based on the sample data, I still recommend the use
of SQL-92 ANSI Standard joins.


Note: Photo_id contains data identical in content to the title of
the column. Why is it being stored if it is all the same?
Especially in a column with "id" in the title. "id" implies
identification.

Note: Photo_Name appears to contain integer values. "Name" is
indicative of (although not mandating of) character data.

Note: M_Name contains data identical in content to the title of the
column. Why is it being stored if it is all the same?

Note: I could not determine the PRIMARY KEY with certainty, and so I
used "Photo_Name", since it *appeared* to be unique.


CREATE TABLE Albums_20051126_1
(Photo_id TEXT(8)
,Photo_Name INTEGER
,M_Name TEXT(6)
,Member_id TEXT(4)
,CONSTRAINT pk_Albums_20051126_1
PRIMARY KEY (Photo_Name)
)


Sample Data:

photo_id, 150, m_name, dave
photo_id, 149, m_name, dave
photo_id, 148, m_name, dave
photo_id, 147, m_name, john
photo_id, 146, m_name, john
photo_id, 145, m_name, fred


Query:

SELECT A1.Photo_ID
,A1.Photo_Name
,A1.M_Name
,A1.Member_id
FROM Albums_20051126_1 AS A1
WHERE A1.Photo_Name =
(SELECT MAX(A01.Photo_Name) AS Photo_Name
FROM Albums_20051126_1 AS A01
WHERE A01.Member_id = A1.Member_id
GROUP BY A01.Member_id)


Results:

photo_id, 150, m_name, dave
photo_id, 147, m_name, john
photo_id, 145, m_name, fred


The above query appears to produce the desired output.


Although meant for an sqlserver newsgroup, the
following link is still applicable for MS Access:
http://www.aspfaq.com/etiquette.asp?id=5006, and
is excellent when it comes to detailing how to
provide the information that will best enable
others to answer your questions.


Sincerely,

Chris O.
 
Chris,

Thanks for your efforts Chris, you obvioulsy spent time on this and i didnt
give you enough information to start with so i'm sorry for that, i will try
again, here are more details of the 2 tables i'm working with

FORUM_ALBUM has the following fields
Photo_id [Integer] (AutoNumber)
Member_id [Integer]
Photo_Name [nChar (50)]
Photo_desc [nChar (255)]
Photo_Status [Integer]
Photo_Cat [Integer]
Cat_Photo_Display [Integer]

here's some sample data (commas are just to indicate fields, they do not
appear in data)

175, 10, pic.jpg, desc, 1, 70, 0
174, 10, pic1.jpg, desc1, 1, 70, 0
173, 10, pic2.jpg, desc2, 1, 70, 0
130, 2, pic3.jpg, desc3, 1, 100, 0
124, 2, pic4.jpg, desc4, 1, 100, 0
100, 32, pic5.jpg, desc5, 1, 120, 0

FORUM_ALBUM_USERS has these
Member_id [Integer]
M_NAME [nChar (25)]

sample data again

10, Dave
2, John
32, Fred

i have to use the 2 tables because i need the M_NAME from FORUM_ALBUM_USERS
and that does not appear in FORUM_ALBUM (i did not design the db and i can't
alter it, which is another reason i have to use 2 tables)which is also why i
used
WHERE FORUM_ALBUM.Member_id=FORUM_ALBUM_USERS.Member_id to ensure the
correct name goes with the correct Member_id
I need the query to produce
Photo_id, Photo_Name, Member_id,M_NAME but with only one record for each
M_NAME or Member_id (doesn't matter which obviously)
so using the sample data, i need
175, pic.jpg, 10, Dave
130, pic3.jpg, 2, John
100, pic5.jpg, 32, Fred

and so on.
I would really appreciate it if you could look at this again for me.

Thanks

Dave
 
Davecl said:
Chris,

Thanks for your efforts Chris, you obvioulsy spent time on this and i didnt
give you enough information to start with so i'm sorry for that, i will try
again, here are more details of the 2 tables i'm working with
Click to expand...

Dave,

You are welcome.



here's some sample data (commas are just to indicate fields, they do not
appear in data)
Click to expand...

Actually, I just sample data into MS Word, use "Convert Text to
Table", and then paste that table into the newly created MS Access
table in Datasheet View. That's pretty much the quickest way
(unless there are only one or two rows of one or two columns and the
data is simple and short).

Commas make that very easy. :)

Thanks

Dave
Click to expand...

Dave,

Tables:

Note: There is no data type named "nChar" in JET SQL 4.0. CHAR is
already a unicode data type.

CREATE TABLE FORUM_ALBUM_USERS_20051127_1
(Member_id INTEGER
,M_NAME CHAR(25)
,CONSTRAINT pk_FORUM_ALBUM_USERS_20051127_1
PRIMARY KEY (Member_id)
)

Note: I did not use AUTOINCREMENT for Photo_id so I could load your
sample values.

CREATE TABLE FORUM_ALBUM_20051127_1
(Photo_id INTEGER
,Member_id INTEGER
,Photo_Name CHAR(50)
,Photo_desc CHAR(255)
,Photo_Status INTEGER
,Photo_Cat INTEGER
,Cat_Photo_Display INTEGER
,CONSTRAINT pk_FORUM_ALBUM_20051127_1
PRIMARY KEY (Photo_id)
,CONSTRAINT fk_FORUM_ALBUM_FORUM_ALBUM_USERS_20051127_1
FOREIGN KEY (Member_id)
REFERENCES FORUM_ALBUM_USERS_20051127_1 (Member_id)
)


Sample Data:

FORUM_ALBUM_USERS_20051127_1
10, Dave
2, John
32, Fred

FORUM_ALBUM_20051127_1
175, 10, pic.jpg, desc, 1, 70, 0
174, 10, pic1.jpg, desc1, 1, 70, 0
173, 10, pic2.jpg, desc2, 1, 70, 0
130, 2, pic3.jpg, desc3, 1, 100, 0
124, 2, pic4.jpg, desc4, 1, 100, 0
100, 32, pic5.jpg, desc5, 1, 120, 0


Desired Results:

175, pic.jpg, 10, Dave
130, pic3.jpg, 2, John
100, pic5.jpg, 32, Fred


Query:

Note: This query is, essentially, the same as the previous query I
posted, except that it now has a two-table INNER JOIN.

(Remember to cut off the dates, "_20051127_1", I've appended to the
table names before executing it.)

SELECT FA1.Photo_id
,FA1.Photo_Name
,FAU1.Member_id
,FAU1.M_NAME
FROM FORUM_ALBUM_USERS_20051127_1 AS FAU1
INNER JOIN
FORUM_ALBUM_20051127_1 AS FA1
ON FAU1.Member_id = FA1.Member_id
WHERE FA1.Photo_id =
(SELECT MAX(FA01.Photo_id)
FROM FORUM_ALBUM_20051127_1 AS FA01
WHERE FA01.Member_id = FA1.Member_id
GROUP BY FA01.Member_id)


Output:

175, pic.jpg, 10, Dave
130, pic3.jpg, 2, John
100, pic5.jpg, 32, Fred


There, that also appears to produce the output data.


Sincerely,

Chris O.
 
Davecl said:
Chris

Thank you so much, that is exactly what i needed (until somebody picks fault
with my code again, lol)

If you would like to see the query in action go to
http://www.parrotlinks.com/forum2/default.asp and scroll down to the photo
albums :-)
Click to expand...

Davecl,

Hmmm, parrots and cats, I can live with that. "Meow," when you hear
a parrot immitating a cat, you really do a double take.


Sincerely,

Chris O.
 
Back
Top