How to adjust this query?

This my query

SELECT DISTINCT a.id,a.venue_id,a.user_id,mm.profilenam,mm.photo_thumb 
  FROM announce_arrival AS a,members as mm 
 WHERE a.user_id = mm.mem_id AND a.venue_id=mm.mem_id

I have venue_id and user_id and I want to map both there ids with members tables mem_id which will return me profilename and photo_thumb. Both ids will be different and they must be distinct.

---

I like this answer:

SELECT DISTINCT a.id
               , a.venue_id
               , a.user_id
               , m1.profilenam
               , m2.photo_thumb
 FROM announce_arrival AS a
      , members as m1
      , members as m2  
 WHERE a.user_id = m1.mem_id  
 AND   a.venue_id=m2.mem_id

but I dont want a.id to be there, I just want venue_id and user_id and the result that I get I want each of that row.

 SELECT 开发者_运维技巧DISTINCT COUNT(*) 
 FROM announce_arrival AS a       
 INNER JOIN members as m1 ON (a.user_id = m1.mem_id)
 INNER JOIN members as m2 ON (a.venue_id= m2.mem_id)
 GROUP BY a.venue_id, a.user_id  ORDER BY date DESC,time DESC;

how can i use count on above query?

---

If you want to see distinct venues and users, try this query.

 SELECT 
   count(*) as rowcount
  , a.venue_id                
  , a.user_id                
  , m1.profilenam                
  , m2.photo_thumb  
 FROM announce_arrival AS a       
 INNER JOIN members as m1 ON (a.user_id = m1.mem_id)
 INNER JOIN members as m2 ON (a.venue_id= m2.mem_id)
 GROUP BY a.venue_id, a.user_id

If members.mem_id is a primary key (or unique field) then fields profilenam and photo_thumb are dependant on those fields, and the group by s safe to use.

Note
You may need to replace one or both inner join with left join, if yuo don't get the expect results.

About distinct
Distinct and group by are a lot alike, but work differently.

For more info on this see: http://dev.mysql.com/tech-resources/articles/debunking-group-by-myths.html

Critique on your DB design
Members.mem_id links to both announce_arrival.user_id and announce_arrival.venue_id. This looks very suspect, because if user_id happens to be the same as some venue_id, there is no telling is mem_id links to user_id #10 or venue_id #10 or both.
I would recommend that you split mem_id into two fields:

members.venue_id -> links to announce_arrival.venue_id; is null if no link
members.user_id -> links to announce_arrival.user_id; is null if no link.

If you do that your query changes into:

 SELECT 
   count(*) as rowcount
  , count(a.venue_id) as venue_count
  , count(a.user_id) as user_count
  , a.venue_id                
  , a.user_id                
  , m1.profilenam                
  , m2.photo_thumb  
 FROM announce_arrival AS a       
 LEFT JOIN members as m1 ON (a.user_id = m1.user_id)
 LEFT JOIN members as m2 ON (a.venue_id = m2.venue_id)
 GROUP BY a.venue_id, a.user_id

Notice the need to use a left join here, because you want to show rows that match either on the first join, or the second join or both.
If you use inner join you will only get rows where both joins produce a match which will give only a very limited subset of rows.

Count
count(*) counts all rows it is recommended because it is the fastest count possible; it includes null rows in the count.
count(somefield) counts all rows where somefield is not null.

---

If you want to get just two IDs with profilename and photo thumb, first you should remove a.id from your query and use JOIN

SELECT DISTINCT a.venue_id,a.user_id,mm.profilenam,mm1.photo_thumb
FROM announce_arrival a
LEFT JOIN members mm ON a.user_id = mm.mem_id
LEFT JOIN members mm1 ON a.venue_id = mm1.mem_id

But it seems for me, than you have strange DB scheme and strange references.

---

I have attempted to interpret the broken query.

My interpretation of the design is:

1. for every announce_arival
2. there is one members record where user_id = mem_id
3. and there is one members record where venue_id = mem_id

 SELECT DISTINCT a.id
               , a.venue_id
               , a.user_id
               , m1.profilenam
               , m2.photo_thumb
 FROM announce_arrival AS a
      , members as m1
      , members as m2  
 WHERE a.user_id = m1.mem_id  
 AND   a.venue_id=m2.mem_id

If I have misunderstood then please elaborate on your question.

I think that your design could put the profile name and photo_thumb in separate tables.

---

Maybe

SELECT DISTINCT 
    announce_arrival.id,
    announce_arrival.venue_id,
    announce_arrival.user_id,
    members.profilenam,
    members.photo_thumb    
FROM announce_arrival 
INNER JOIN members ON members.mem_id = announce_arrival.user_id
INNER JOIN venue ON venue.id = announce_arrival.venue_id

but without the schema we are just guessing...

EDIT:

SELECT DISTINCT Count(*)
FROM announce_arrival 
INNER JOIN members ON members.mem_id = announce_arrival.user_id
INNER JOIN venue ON venue.id = announce_arrival.venue_id