CODEWITHSUNDEEP
mysqljoin
greenbandit
greenbandit

Reputation: 2275

merge tables and query

I have this tables in my mysql schema:

posts:

   ---------------------------------------------
   id|type | userid | title | msg | time_added

users:

   ------------------
   userid | username

How can I show only 6 entries where: type is audio or video and only for a certain username?

Upvotes: 0

Views: 70

Answers (4)

Andrew Cooper
Andrew Cooper

Reputation: 32576

Select title, msg, type
From Users Left Join Posts on Users.userid = Posts.userid
where type in ("audio", "video")
    and username = @username
limit 6

Upvotes: 0

Alec
Alec

Reputation: 9078

SELECT *
FROM posts p
INNER JOIN users u
  ON u.userid = p.userid
  AND u.username = 'name'
WHERE p.type IN ('audio','video')
ORDER BY time_added DESC
LIMIT 6

Upvotes: 0

Dan J
Dan J

Reputation: 16708

SELECT TOP 6 *
FROM posts
INNER JOIN users ON posts.userid = users.userid
WHERE type IN ('audio','video')
AND username = @username;

or (since TOP may be MS SQL Server only...)

SELECT *
FROM posts
INNER JOIN users ON posts.userid = users.userid
WHERE type IN ('audio','video')
AND username = @username
LIMIT 6;

Upvotes: 2

Mike Dinescu
Mike Dinescu

Reputation: 55720

Here's one way:

SELECT TOP 6 
      P.id
     ,P.type
     ,P.userid
     ,U.username
     ,P.title
     ,P.msg
     ,P.time_added
 FROM posts P
   INNER JOIN users U ON U.userid = P.userid
 WHERE U.username = 'given user name'
    AND (P.type = 'audio' OR P.type = 'video')

Upvotes: 0

Related Questions