CODEWITHSUNDEEP
phpmysqlsqljoin
C4d
C4d

Reputation: 3282

Mysql - join 2 queries with limit

Im not very familiar with using 'join' in queries. I really tried solving this by my own, but it seems to be too hard.

I got 2 Tables:

Table 'users':
+-----------------+-----------------+
|     member      |     online      |
+-----------------+-----------------+
| mahran          |               1 | 
| peter           |               1 |
| Jen             |               1 |
| Steve           |               0 |
+-----------------+-----------------+

Table 'tickets'
+-----------------+----------------+----------------+
|      name       |    category    |      time      |
+-----------------+----------------+----------------+
| mahran          | silver         |              1 |
| peter           | blue           |              1 |
| mahran          | blue           |              2 |
| peter           | red            |              3 |
| peter           | green          |              2 |
| Jen             | silver         |              1 |
+-----------------+----------------+----------------+

The chellange:
I need each member (users.member) who's online (users.online). The next thing is to get the category for each member (user.member = tickets.name) with the highest time (probably ORDER BY time DESC LIMIT 1).

So, for example: Peter is online. Peters highest time is 3 at the position of category=red. So I want peter to show up in the result with his category 'red'. Mahran would show up with blue. Jen would get silver. And steve would be left out because he's not online.

I hope this was clear. In general I know how the queries would look like but theres no chance for me merging them together.

What needs to be merged:

SELECT member FROM users WHERE online = 1;
|
v for each member
SELECT category FROM tickets WHERE name=users.member ORDER BY time DESC.

So, any ideas how to solve this?

Here is a fiddle with a not working query: Click

Upvotes: 0

Views: 97

Answers (6)

Biggum
Biggum

Reputation: 372

Sorry, but peter seems to be RED, It's time is 3. Don't you?

Depending on table definition, is not guaranteed to have one only result for each user. For example, if peter has time 3 in two categories, you can get one different category depending of the SQL sorting method.

To be sure, tickets.Category and tickets.time must be in a unique key (both toghether, not a unike key for each field)

Assuming that, the Query could be this.

select t2.name,  t2.category
from
tickets t2
INNER JOIN (Select
        u.member, max(time)
        from users u, tickets t
        where
        u.member = t.name
        and u.online = 1
        group by u.member
       ) as usermaxtime on t2.name = usermaxtime.member;

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You can do this easily with a correlated subquery:

select u.member,
       (select t.category
        from tickets t
        where t.name = u.member
        order by t.time desc
        limit 1
       ) as MostRecentCategory
from users u
where u.online = 1;

This can make use of the following indexes: users(online, member) and ticket(member, time, category).

Upvotes: 2

munsifali
munsifali

Reputation: 1731

try this

SELECT DISTINCT User.member,Ticket.category FROM users AS USER
INNER JOIN tickets AS Ticket ON (User.member = Ticket.name)
WHERE User.online = 1;

Upvotes: 0

Joël Salamin
Joël Salamin

Reputation: 3576

Here is the query you're looking for:

SELECT U.member
    ,T.category
FROM users U
INNER JOIN tickets T ON T.name = U.member
INNER JOIN (SELECT T2.name
               ,MAX(T2.time) AS [maxTime]
           FROM tickets T2
           GROUP BY T2.name) AS M ON M.name = T.name
                               AND M.maxTime = T.time
WHERE U.online = 1

The use of [name] to join the two tables is not a good practice, it's much better to use keys instead. But my query is just here to help you understanding the process of jointure.

Hope this will help you.

Upvotes: 1

Punitha Subramani
Punitha Subramani

Reputation: 1477

Can you make sql fiddle? 

USE DISTINCT

stackoverflow.com/questions/11704012/mysql-distinct-join

Upvotes: 0

wbars
wbars

Reputation: 533

If i understand you correctly

 SELECT DISTINCT users.member, tickets.category FROM tickets JOIN users ON users.member = tickets.name WHERE users.online = 1 ORDER BY tickets.time DESC

Upvotes: 0

Related Questions