CODEWITHSUNDEEP
mysqlsql
Pzoco
Pzoco

Reputation: 167

SQL Query I can't figure out

I have the tables:

contest_images {contestID, imageID}
image {ID, userID}
user {ID}

(They contain more information than that, but this is all we need)

My query so far:

SELECT imageID, userID FROM contest_images
JOIN image ON contest_images.imageID = image.ID 
JOIN user ON user.ID = image.userID
ORDER BY contest_images.imageID  DESC

The contest_images can contain multiple images from one user (which is intended)

I want to retrieve the x newest all users have added. (So I can restrict the users to only have one image in the contest at a time)

I also tried to make a view displaying {contestID, imageID, userID}.

Thanks in advance

Upvotes: 2

Views: 91

Answers (3)

Pzoco
Pzoco

Reputation: 167

Thank you guys very much! :)

I went with

SELECT contestID, MAX(imageID), userID FROM contest_images
JOIN image ON contest_images.imageID = image.ID 
JOIN user ON user.ID = image.userID
GROUP BY image.userID

Hence the contestID will be specified in a WHERE clause in the end :)

But thanks alot both of you :P (I really was stuck on this.. :( )

Upvotes: 1

Sanal K
Sanal K

Reputation: 733

Try like this,

SELECT MAX(imageID), userID FROM contest_images
JOIN image ON contest_images.imageID = image.ID 
JOIN user ON user.ID = image.userID
GROUP BY image.userID

Upvotes: 3

Deepshikha
Deepshikha

Reputation: 10264

To limit to one image/ latest image in contest at a time you can use Top operator as :

SELECT top 1 imageID, userID FROM contest_images
JOIN [image] ON contest_images.imageID = [image].ID 
JOIN [user] ON [user].ID = image.userID
ORDER BY contest_images.imageID  DESC

Upvotes: 2

Related Questions