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 2124 deepuv04 227
Recognized Expert New Member
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
deepuv04 227
Recognized Expert New Member
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
deepuv04 227
Recognized Expert New Member
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 select from my database the records that I need, but I need to
insert them somewhere down the page so that I can process them. Here's
my select...
|
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 records
that do not match based on a certain field.
Here is how my tables are setup:
Task Code Table
TCode,TCodeFName,Active
|
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 other tables.
PlanID is the primary in Plan and
Plan Provider ProviderLocation Lookups
-------- ---------- ---------------- -----------...
|
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 couple of
tables in my database using INNER JOINS and the WHERE clause to
specify the required constraints. However, I also want to read two...
|
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
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3
| |
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 information, because one cell phone can be
used by more then one user, one after another, that information is
UserID, Name, Location, Date Received, Date...
|
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
fill dataset(ds)
da.selectcommand.commandtext="select * from xxx"
da.fill(ds.tables(0))
and when i write
|
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 display on multiple pages.
Which way would be more efficient....
1. Do the SELECT and LIMIT it to 1000 records, then relying on MySQL's
caching,...
|
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: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |