CODEWITHSUNDEEP
mysqlsqlsqlite
user2631770
user2631770

Reputation: 45

SQL query for the given relation

there are 3 tables "user", "blog" and "images" a single user can have multiple blogs

a single blog can have multiple images and visa verse that is multiple images can be used for multiple blogs

I need a query for total number of images in a single blog..

And I also need a query for total number of images per user..

please guide with query to fetch data..

my database created is like this:

Create table Author ( Id int , Name nvarchar(max) );

Create table Image ( Id int ,ImagePath nvarchar(max) );

Create table Blog ( Id int ,Name nvarchar(max) ,AuthorId int );

Create table BlogImages ( Id int ,BlogId int ,ImageId int );

ALTER TABLE Blog ADD FOREIGN KEY (AuthorId) REFERENCES Author(Id)

ALTER TABLE BlogImages ADD FOREIGN KEY (BlogId) REFERENCES Blog(Id)

ALTER TABLE BlogImages ADD FOREIGN KEY (ImageId) REFERENCES Image(Id)

In above relation I have a table BlogImages having blogId and ImageId , which means single imageID can have multiple blogIds , so multiple blogs using the same image

Upvotes: 0

Views: 100

Answers (2)

Rob
Rob

Reputation: 919

Reply to modified question:

For number of images in a single blog:

select COUNT(Image.Id)
from Image, BlogImages
where Image.Id = BlogImages.ImageId
and BlogImages.BlogId = @BlogId

where @BlogId is the id of the blog you want to count images for.

For number of images by user:

select COUNT(Image.Id)
from Image, BlogImages, Blog
where Image.Id = BlogImages.ImageId
and BlogImages.BlogId = Blog.Id
and Blog.AuthorId = @AuthorId

where @AuthorId is the id of the user you want to count images for.

You should add distinct after the select keyword if you do not want to count the same image twice.

Upvotes: 0

Akshay
Akshay

Reputation: 1901

For getting total number of images per user try following query

select U.userId,count(*) from
(
select U.userId,I.imageId from user U,blog B, images I
where B.userId==U.userId and B.blogId==I.blogId
)
group by U.userId

And for total number of images in a single blog use following

select B.blogId,count(*) from
(
select B.blogId,I.imageId from blog B,images I
where B.blogId==I.blogId
)
group by B.blogId

Upvotes: 0

Related Questions