Need help with a SQL Query

Question

Need help with a SQL Query

Ritika Thakkar

SSC Journeyman

Points: 99
More actions
January 4, 2010 at 6:39 am

#141401

Hi,
I need help in writing a query.
I have two tables : Report and Questions
Report contains ReportID, ReportName
Questions contains ReportID, QuestionID and QuestionName
User will select ReportName from an Application. He may chose to select multiple reportnames at the same time.
What i want the query to return is a list of all the questions that are common to all reports.
So if reportid 1 contains questionids 1,2,3 and reportid 2 contains questions 2,3,4 then when this query is passed reportids 1 and 2, it should return questionids 2,3 only.
Any help will be much appreciated.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Please provide sample data scripts. The link below will show you how to do this.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Nabha SSCrazy Eights Points: 8534 More actions · Answer 2

You gotta give some test data in a consumable format (sorry if you are just asking for a direction/suggestion only?)

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Edit: Sorry, dint see the post from Chris.

---------------------------------------------------------------------------------

LutzM SSC Guru Points: 107049 More actions · Answer 3

Additionally to the test data it would be nice to how you'd provide those multiple reportids (e.g. comma separated, table, xml aso).

Basically, being a little more specific what you mean by

then when this query is passed reportids 1 and 2

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Something like this is what you may be needing. Need to know how the list of report id's is being passed back to the database, is it a comma separated list that needs to be parsed?

select

*

from

dbo.Report rpt

inner join dbo.Questions qt

on (rpt.ReportID = qt.ReportID)

inner join dbo.DelimitedSplit('RptID1,RptID2',',') ds -- This is a in-line table valued function

on (rpt.ReportID = ds.Item) -- ds.Item may require conversion to properly work

Also, I am not posting the code for the DelimitedSplit function at this time as it has been posted several times in other threads. In addition, if you search SSC for split functions, you will also find other possible routines as well.

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 5

This is indeed a very interesting question and I tried my hand on it too. Here's the table creation script:

create table questions (reportid int,questionid int)

insert questions

select 1,1

union all

select 1,2

union all

select 1,3

union all

select 2,2

union all

select 2,3

union all

select 2,4

and here's the query to get the required output:

select distinct questionid from questions D where D.questionid not in

(select A.questionid from (select distinct questionid from questions) A cross join (select distinct reportid from questions) B

left join questions C on A.questionid = C.questionid AND B.reportid = C.reportid

where C.reportid is null)

Sunny-138471 Ten Centuries Points: 1162 More actions · Answer 6

Since the user selects only some of the reports, you may have to put the filter in the query too

select distinct questionid from questions D where D.questionid not in

(select A.questionid from (select distinct questionid from questions where reportid in (1,2)) A

cross join (select distinct reportid from questions where reportid in (1,2)) B

left join questions C on A.questionid = C.questionid AND B.reportid = C.reportid

where C.reportid is null)

and D.reportid in (1,2)