microsoft sql select query from multiple tables

Question

This query is working in mysql but is not working in microsoft sql server management studio 2008, can someone help me out?

SELECT DISTINCT C.firstname,C.lastname,QC.category_name,QR.cid,QR.catid,QR.rhid 
FROM cms_question_report QR, 
cms_clients C,
cms_questioncategory QC ,
cms_reporthistory RH 
WHERE C.id=QR.cid 
AND QR.rhid=RH.id 
AND QR.catid='3' 
AND QR.catid=QC.id

I am getting the error: Invalid object name cms_question_report

Add the name of the schema (likely dbo) and make sure your editor is connecting to the database that has these tables and not the master database — BICube
– BICube, Commented May 27, 2015 at 3:52
Check selected database is correct or not..!!or write Use Database before this query — Dhaval
– Dhaval, Commented May 27, 2015 at 3:52

Josh Crozier · Accepted Answer · 2015-12-10 23:33:37Z

1

SELECT DISTINCT C.firstname,C.lastname,QC.category_name,QR.cid,QR.catid,QR.rhid 
FROM cms_question_report QR
left join cms_clients C
on C.id=QR.cid 
left join cms_questioncategory QC
on QR.catid=QC.id
and QR.catid='3'
left join cms_reporthistory RH
on QR.rhid=RH.id

I think this should do

edited Dec 10, 2015 at 23:33

Josh Crozier

242k56 gold badges401 silver badges316 bronze badges

answered May 27, 2015 at 3:56

HJK

1009 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BICube Over a year ago

This will only join on catid=3. In their example, they filter on catid=3. That's a significant difference.

HJK Over a year ago

he/she wanted only catid=3

BICube Over a year ago

But this will return all catids and only incur the join on catid = 3

BICube Over a year ago

Anyways, I might have a misunderstanding, they accepted your answer.

Roger · Accepted Answer · 2015-05-27 03:55:30Z

1

specify Normally it happens when you have specific schema and you don't specify it for example:

Replace dbo. with your schema and/or type your database name

SELECT DISTINCT C.firstname,C.lastname,QC.category_name,QR.cid,QR.catid,QR.rhid 
FROM databasename.dbo.cms_question_report QR, 
databasename.dbo.cms_clients C,
databasename.dbo.cms_questioncategory QC ,
databasename.dbo.cms_reporthistory RH 
WHERE C.id=QR.cid 
AND QR.rhid=RH.id 
AND QR.catid='3' 
AND QR.catid=QC.id

edited May 27, 2015 at 3:55

answered May 27, 2015 at 3:53

Roger

1,6613 gold badges30 silver badges57 bronze badges

2 Comments

SQLDummy Over a year ago

Thanks but I'm using other queries without including the dbo, but anyway I tried your query and I got the error: Incorrect syntax near '*'.

Roger Over a year ago

Try again "replacing databasename with the name of your database" and remove the '*'

Collectives™ on Stack Overflow

microsoft sql select query from multiple tables

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related