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
6 2096
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. -
select movieid, movietitle as moviename,
-
count(moviereviewid) as Moviereviewscnt ,
-
count(videoid) as Vidoescnt
-
from movies inner join
-
videoid on videoid.movieid = movies.movieid
-
where moviereviewid >0 and videoid > 0
-
group by movies.movieid
-
Thanks
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. -
select movieid, movietitle as moviename,
-
count(moviereviewid) as Moviereviewscnt ,
-
count(videoid) as Vidoescnt
-
from movies inner join
-
videoid on videoid.movieid = movies.movieid
-
where moviereviewid >0 and videoid > 0
-
group by movies.movieid
-
Thanks
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. -
select videoid,videotitle,count(blogcid),count(destid)
-
from food inner join
-
video on video.destid = food.foodid
-
where blogcid > 0 and destid > 0
-
group by videoid,videotitle
-
thanks
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. -
select videoid,videotitle,count(blogcid),count(destid)
-
from food inner join
-
video on video.destid = food.foodid
-
where blogcid > 0 and destid > 0
-
group by videoid,videotitle
-
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
Hi,
Based on the sample data given here is the query you want. -
create table Food( foodid int, blogcid int)
-
-
insert into food
-
select 3, 12356 union
-
select 3, 12357 union
-
select 3, 12358 union
-
select 9, 15265 union
-
select 15, 18963 union
-
select 15, 18964 union
-
select 19, 0 union
-
select 13, 22156
-
-
-
create table videos( videoid int,videotitle varchar(20),destid int)
-
-
insert into videos
-
SELECT 9,'paneerpakora', 3 UNION
-
SELECT 10 ,'paneerpakora', 3 UNION
-
SELECT 11 ,'aloogobhi', 9 UNION
-
SELECT 16 ,'chinese chickensalad', 15 UNION
-
SELECT 17 ,'chinese chickensalad', 15 UNION
-
SELECT 18 ,'chinese chickensalad', 15 UNION
-
SELECT 45 ,'Chicken', 19 UNION
-
SELECT 0 ,'tomato soup', 13
-
-
-
SELECT FOODID,COUNT(DISTINCT BLOGCID),COUNT(DISTINCT VIDEOID)
-
FROM FOOD INNER JOIN
-
VIDEOS ON VIDEOS.DESTID = FOOD.FOODID
-
WHERE BLOGCID > 0 AND DESTID > 0
-
GROUP BY FOODID
-
Thanks
Hi,
Based on the sample data given here is the query you want. -
create table Food( foodid int, blogcid int)
-
-
insert into food
-
select 3, 12356 union
-
select 3, 12357 union
-
select 3, 12358 union
-
select 9, 15265 union
-
select 15, 18963 union
-
select 15, 18964 union
-
select 19, 0 union
-
select 13, 22156
-
-
-
create table videos( videoid int,videotitle varchar(20),destid int)
-
-
insert into videos
-
SELECT 9,'paneerpakora', 3 UNION
-
SELECT 10 ,'paneerpakora', 3 UNION
-
SELECT 11 ,'aloogobhi', 9 UNION
-
SELECT 16 ,'chinese chickensalad', 15 UNION
-
SELECT 17 ,'chinese chickensalad', 15 UNION
-
SELECT 18 ,'chinese chickensalad', 15 UNION
-
SELECT 45 ,'Chicken', 19 UNION
-
SELECT 0 ,'tomato soup', 13
-
-
-
SELECT FOODID,COUNT(DISTINCT BLOGCID),COUNT(DISTINCT VIDEOID)
-
FROM FOOD INNER JOIN
-
VIDEOS ON VIDEOS.DESTID = FOOD.FOODID
-
WHERE BLOGCID > 0 AND DESTID > 0
-
GROUP BY FOODID
-
Thanks
Hi
Thanks a lot for your response the query worked out
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Neil |
last post by:
Hello,
I do hope some kind soul can help me with what I thought was not going
to be difficult, but for this newbie it's a bit harder than I thought.
Here's what I'm trying to do.....
I can...
|
by: Michael |
last post by:
I have two tables with a 1-many relationship. I want to write a
select statement that looks in the table w/many records and compares
it to the records in the primary table to see if there are any...
|
by: David Logan |
last post by:
Hello,
I am trying to construct a query across 5 tables but primarily 3
tables. Plan, Provider, ProviderLocation are the three primary tables
the other tables are lookup tables for values the...
|
by: Andrew McNab |
last post by:
Hi folks,
I have a problem with an MS Access SQL query which is being used in an
Access Report, and am wondering if anyone can help.
Basically, my query (shown below) gets some records from a...
|
by: Ben |
last post by:
I believe I am missunderstanding how subqueries work. I simple
subquery works fine but when I wish do compare 2 or more fields at
once I don't get the results I wish.
Table A...
|
by: ET |
last post by:
Please help with the query:
There are two tables, A and B.
A table has information about cell phones, like cell number, sim
number, model, manufacturer etc...
B table has user related...
|
by: Mrozu |
last post by:
Hi
I need execute SELECT SQL query in DataSet. No in SQL
server(da.selectcommand) and fill dataset, only show in datagrid
filtered by select-where query records from dataset:) For example I...
|
by: JJ |
last post by:
I need to do a SELECT query that joins four tables. The largest of the
tables could eventually have over 1 million records. I need to do a SELECT
that returns a maximum of 1000 records which will...
|
by: plaforest |
last post by:
Hello All,
Thank you for your thoughtful consideration.
I am running Access 2000 (9.0.3821 SR-1)
This query works:
SELECT ,
FROM table1
|
by: erikbower65 |
last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA:
1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
|
by: kcodez |
last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Sept 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM)
The start time is equivalent to 19:00 (7PM) in Central...
|
by: Taofi |
last post by:
I try to insert a new record but the error message says the number of query names and destination fields are not the same
This are my field names
ID, Budgeted, Actual, Status and Differences
...
|
by: Rina0 |
last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
|
by: DJRhino |
last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer)
If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _
310030356 Or 310030359 Or 310030362 Or...
|
by: lllomh |
last post by:
How does React native implement an English player?
|
by: Mushico |
last post by:
How to calculate date of retirement from date of birth
|
by: DJRhino |
last post by:
Was curious if anyone else was having this same issue or not....
I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
| |