By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,575 Members | 1,948 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,575 IT Pros & Developers. It's quick & easy.

Select query for fetching records from two tables

P: 17
Hi

I have created two tables which are movies table and videos table

Columns of movies table are movie id, movietitle, moviereviewid and the select statement which I have written to fetch count of reviews is as follows

select movieid, count(moviereviewid) as Moviereviewscnt from movies
where moviereviewid >0

columns of videos table are videoid, videoname and movievideoid and the select statment which I have written to fetch count of videos is as follows

select movieid, count(videoid) as Vidoescnt from videos
where videoid >0

I want to write a select query to fetch all those movies which has got moviereviews and movievideos in a single table i.e. it should have
movieid, moviename, moviereviewscnt and movievideoscnt

In other words it should show only those movies which has reviews and videos which is >0
Sep 17 '08 #1
Share this Question
Share on Google+
6 Replies


deepuv04
Expert 100+
P: 227
Hi,
Your requirement is not clear to me, it will be more clear if you can provide some sample data and output you want.

Any way try the following query, probably will help you.

Expand|Select|Wrap|Line Numbers
  1. select movieid, movietitle as moviename,
  2.        count(moviereviewid) as Moviereviewscnt ,
  3.        count(videoid) as Vidoescnt
  4. from movies inner join
  5.      videoid on videoid.movieid = movies.movieid
  6. where moviereviewid >0 and videoid > 0
  7. group by movies.movieid
  8.  
Thanks
Sep 17 '08 #2

P: 17
Hi

Thanks for your response but the query which you gave doesnt work. It displays the same cnt for movie reviews and videoreviews

Let me explain you in detail

As I said I have got two tables like one is 'Food table' and the other one is the 'Videos' table

Columns which I have created in 'Food' table are 'foodid' and 'blogcid'
colcumns which I have created in Videos table are 'videoid, 'destid', 'videotitle'

Common column for the above two tables are foodid and destid which has the same content id for inner join

I need to fetch records of those foodids which has got blogs and videos

For example

Food id blog cid
3 12356
9 15265
15 18963
19 0
13 22156

videoid videotitle destid
9 paneerpakora 3
11 aloogobhi 9
16 chinese chickensalad 15
45 Chicken 19
0 tomato soup 13

From the above data I have to fetch only those records which has blogs and videos. In other words I need a select query to fetch records of those dishes alone which has blogs >0 and destid >0 which means that fetched foodids should have both blogs and videos it should fetch records which doesnt have blogs or videos.






Hi,
Your requirement is not clear to me, it will be more clear if you can provide some sample data and output you want.

Any way try the following query, probably will help you.

Expand|Select|Wrap|Line Numbers
  1. select movieid, movietitle as moviename,
  2.        count(moviereviewid) as Moviereviewscnt ,
  3.        count(videoid) as Vidoescnt
  4. from movies inner join
  5.      videoid on videoid.movieid = movies.movieid
  6. where moviereviewid >0 and videoid > 0
  7. group by movies.movieid
  8.  
Thanks
Sep 17 '08 #3

deepuv04
Expert 100+
P: 227
Hi,
thanks for the information provided, but missed with the expected output.
can you give the sample output you are expecting.

I just changed the query to match with the sample data given, here is the query.
Expand|Select|Wrap|Line Numbers
  1. select videoid,videotitle,count(blogcid),count(destid)
  2. from food inner join
  3.      video on video.destid = food.foodid
  4. where blogcid > 0 and destid > 0
  5. group by videoid,videotitle
  6.  
thanks
Sep 17 '08 #4

P: 17
Hi,
thanks for the information provided, but missed with the expected output.
can you give the sample output you are expecting.

I just changed the query to match with the sample data given, here is the query.
Expand|Select|Wrap|Line Numbers
  1. select videoid,videotitle,count(blogcid),count(destid)
  2. from food inner join
  3.      video on video.destid = food.foodid
  4. where blogcid > 0 and destid > 0
  5. group by videoid,videotitle
  6.  
thanks

Hi

Thank you for your response. Query doesnt give the expected result.

Here is the sample data :

Food id blog cid
3 12356
3 12357
3 12358
9 15265
15 18963
15 18964
19 0
13 22156

videoid videotitle destid
9 paneerpakora 3
10 paneerpakora 3
11 aloogobhi 9
16 chinese chickensalad 15
17 chinese chickensalad 15
18 chinese chickensalad 15
45 Chicken 19
0 tomato soup 13


Here is the expected result:

Output should display as given

Food id blogcnt Videocnt
3 3 2
9 1 1
15 2 3
13 1 1

On executing the query it should display only those records for which blogs and videos are existing. It should not dipslay the records there is no videos or if there is no blogs. In other words it should fetch only those records for which
videos and blogs are posted, displaying the count of blogs and count of videos as given above
Sep 17 '08 #5

deepuv04
Expert 100+
P: 227
Hi,
Based on the sample data given here is the query you want.

Expand|Select|Wrap|Line Numbers
  1. create table Food( foodid int, blogcid int)
  2.  
  3. insert into food
  4. select 3, 12356 union
  5. select 3, 12357 union
  6. select 3, 12358 union
  7. select 9, 15265 union
  8. select 15, 18963 union
  9. select 15, 18964 union
  10. select 19, 0 union
  11. select 13, 22156 
  12.  
  13.  
  14. create table videos( videoid int,videotitle varchar(20),destid int)
  15.  
  16. insert into videos
  17. SELECT 9,'paneerpakora', 3 UNION
  18. SELECT 10 ,'paneerpakora', 3 UNION
  19. SELECT 11 ,'aloogobhi', 9 UNION
  20. SELECT 16 ,'chinese chickensalad', 15 UNION
  21. SELECT 17 ,'chinese chickensalad', 15 UNION
  22. SELECT 18 ,'chinese chickensalad', 15 UNION
  23. SELECT 45 ,'Chicken', 19 UNION
  24. SELECT 0 ,'tomato soup', 13
  25.  
  26.  
  27. SELECT   FOODID,COUNT(DISTINCT BLOGCID),COUNT(DISTINCT VIDEOID)  
  28. FROM FOOD INNER JOIN
  29.      VIDEOS ON VIDEOS.DESTID = FOOD.FOODID
  30. WHERE BLOGCID > 0 AND DESTID > 0
  31. GROUP BY FOODID
  32.  
Thanks
Sep 18 '08 #6

P: 17
Hi,
Based on the sample data given here is the query you want.

Expand|Select|Wrap|Line Numbers
  1. create table Food( foodid int, blogcid int)
  2.  
  3. insert into food
  4. select 3, 12356 union
  5. select 3, 12357 union
  6. select 3, 12358 union
  7. select 9, 15265 union
  8. select 15, 18963 union
  9. select 15, 18964 union
  10. select 19, 0 union
  11. select 13, 22156 
  12.  
  13.  
  14. create table videos( videoid int,videotitle varchar(20),destid int)
  15.  
  16. insert into videos
  17. SELECT 9,'paneerpakora', 3 UNION
  18. SELECT 10 ,'paneerpakora', 3 UNION
  19. SELECT 11 ,'aloogobhi', 9 UNION
  20. SELECT 16 ,'chinese chickensalad', 15 UNION
  21. SELECT 17 ,'chinese chickensalad', 15 UNION
  22. SELECT 18 ,'chinese chickensalad', 15 UNION
  23. SELECT 45 ,'Chicken', 19 UNION
  24. SELECT 0 ,'tomato soup', 13
  25.  
  26.  
  27. SELECT   FOODID,COUNT(DISTINCT BLOGCID),COUNT(DISTINCT VIDEOID)  
  28. FROM FOOD INNER JOIN
  29.      VIDEOS ON VIDEOS.DESTID = FOOD.FOODID
  30. WHERE BLOGCID > 0 AND DESTID > 0
  31. GROUP BY FOODID
  32.  
Thanks

Hi

Thanks a lot for your response the query worked out
Sep 18 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.