How do i...please reply I want to perform join on above queries so I will make comparison in my project about advertisement published and news published

Question

0.00/5 (No votes)

See more:

SELECT
[NewspaperName]
,[AreaOfNews]
,[RateOfNewsPublished]
,[TotalAmount]

FROM [ecampus].[dbo].[tblDailyNewsPublishedDetails] WHERE NewspaperName='AAPALA SARVMAT' AND DateOfPublication BETWEEN '2016-09-28' AND '2016-10-05'

SELECT [NewspaperName]
,[AreaOfAdvertisement]
,[RatePerSqCm]
,[TotalAmount]
,[Discount]
,[ActualAmountPaid]
FROM [ecampus].[dbo].[tblAdvertisementRequestDetails] WHERE NewspaperName='AAPALA SARVMAT' AND DateOfPublication BETWEEN '2016-09-28' AND '2016-10-05' and [AdvertisementPublishStatus]='Y'

What I have tried:

Please reply I want to perform join on above queries so i will make comparison in my project about advertisement published and news published

Posted 5-Oct-16 23:06pm

nsshinde

Updated 5-Oct-16 23:22pm

OriginalGriff

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2016-10-05T23:22:00

Start by changing your DB: add a third table
Newspapers

C#

ID        INT, IDENTITY, PRIMARY KEY
PaperName NVARCHAR

And then get rid of your string based NewpaperName from your other tables in favor of a FOREIGN KEY to the ID field of the Newspapers table.
You can then use a join to get the name:

SQL

SELECT n.PaperName, npd.AreaOfNews FROM tblDailyNewsPublishedDetails npd
JOIN Newspapers n ON n.ID = npd.PaperID

That's a lot more efficient than storeing teh paper name each time.
Then you can add to the JOIN:

SQL

SELECT n.PaperName, npd.AreaOfNews, ard.AreaOfAdvertisment FROM tblDailyNewsPublishedDetails npd
JOIN Newspapers n ON n.ID = npd.PaperID
JOIN tblAdvertisementRequestDetails ard ON n.ID = ard.PaperID AND ard.DateOfPublication = npd.DateOfPublication
WHERE ard.DateOfPublication BETWEEN '2016-09-28' AND '2016-10-05' and ard.AdvertisementPublishStatus='Y'

Which will probably give you the start of what you want.