Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL query help

Posted on 2013-01-11
5
Medium Priority
?
466 Views
Last Modified: 2013-01-11
Hello there,

I have this query which when i try to run i get syntax error. can somebody help me figure it out.

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SELECT'.

SELECT reportusercredential.id, reportusercredential.username, 
reportuserpermission.id as reportuserpermissionid, reportusercredential.password, 
reportusertype.id as usertypeid, Section.id as sectionid,  reportusertype.name as usertypename, 
Branch.name AS branchname,  Section.name as sectionname, 
SELECT STUFF((SELECT ',' + name FROM Branch b where ru.branchid = b.id FOR XML PATH ('')), 1, 1, '') 
as branchname1
 FROM reportusercredential,reportuserpermission ru, Section, Branch b, reportusertype 
 WHERE reportuserpermission.reportuserid=reportusercredential.id 
AND reportuserpermission.sectionid=Section.id AND 
reportuserpermission.branchid = Branch.id AND reportusertype.id = reportusercredential.usertypeid 
AND (('1'='1')) GROUP BY reportusercredential.id

Open in new window

0
Comment
Question by:zolf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 4

Accepted Solution

by:
krtyknmsql earned 680 total points
ID: 38766625
Just remove the SELECT statement before the STUFF.
SELECT reportusercredential.id, reportusercredential.username, 
reportuserpermission.id as reportuserpermissionid, reportusercredential.password, 
reportusertype.id as usertypeid, Section.id as sectionid,  reportusertype.name as usertypename, 
Branch.name AS branchname,  Section.name as sectionname, 
STUFF((SELECT ',' + name FROM Branch b where ru.branchid = b.id FOR XML PATH ('')), 1, 1, '') 
as branchname1
 FROM reportusercredential,reportuserpermission ru, Section, Branch b, reportusertype 
 WHERE reportuserpermission.reportuserid=reportusercredential.id 
AND reportuserpermission.sectionid=Section.id AND 
reportuserpermission.branchid = Branch.id AND reportusertype.id = reportusercredential.usertypeid 
AND (('1'='1')) GROUP BY reportusercredential.id

Open in new window

0
 

Author Comment

by:zolf
ID: 38766758
thanks now i get this error

Msg 8120, Level 16, State 1, Line 3
Column 'reportusercredential.username' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


SELECT reportusercredential.id, reportusercredential.username,
ru.id as reportuserpermissionid, reportusercredential.password,
reportusertype.id as usertypeid, Section.id as sectionid,  reportusertype.name as usertypename,
b.name AS branchname,  Section.name as sectionname,
STUFF((SELECT ',' + name FROM Branch b where ru.branchid = b.id FOR XML PATH ('')), 1, 1, '')
as branchname1
 FROM reportusercredential,reportuserpermission ru, Section, Branch b, reportusertype
 WHERE ru.reportuserid=reportusercredential.id
AND ru.sectionid=Section.id AND
ru.branchid = b.id AND reportusertype.id = reportusercredential.usertypeid
AND (('1'='1')) GROUP BY reportusercredential.id
0
 
LVL 41

Assisted Solution

by:ralmada
ralmada earned 660 total points
ID: 38766994
Try
SELECT distinct -- the distinct might not be required, but I can't tell since I'm not familiar with your data, try both ways
	reportusercredential.id, 
	reportusercredential.username, 
	reportuserpermission.id as reportuserpermissionid, 
	reportusercredential.password, 
	reportusertype.id as usertypeid, 
	Section.id as sectionid,  
	reportusertype.name as usertypename, 
	Section.name as sectionname, 
	STUFF((SELECT ',' + name FROM Branch b where ru.branchid = b.id FOR XML PATH ('')), 1, 1, '') as branchname1
FROM 	reportusercredential,
	reportuserpermission ru, 
	Section, 
	reportusertype 
WHERE 	reportuserpermission.reportuserid=reportusercredential.id 
	AND reportuserpermission.sectionid=Section.id 
	AND reportusertype.id = reportusercredential.usertypeid 

Open in new window

0
 
LVL 5

Assisted Solution

by:RehanYousaf
RehanYousaf earned 660 total points
ID: 38767165
your code is
SELECT 
	reportusercredential.id, 
	reportusercredential.username, 
	reportuserpermission.id AS reportuserpermissionid, 
	reportusercredential.password, 
	reportusertype.id AS usertypeid, 
	Section.id AS sectionid,  
	reportusertype.name AS usertypename, 
	Branch.name AS branchname,  
	Section.name AS sectionname, 
	STUFF((SELECT ',' + name FROM Branch b WHERE ru.branchid = b.id FOR XML PATH ('')), 1, 1, '') AS branchname1
FROM 
	reportusercredential,
	reportuserpermission ru, 
	SECTION, 
	Branch b, 
	reportusertype 
WHERE 
	reportuserpermission.reportuserid = reportusercredential.id 
	AND reportuserpermission.sectionid=Section.id 
	AND reportuserpermission.branchid = Branch.id 
	AND reportusertype.id = reportusercredential.usertypeid 
	AND (('1'='1'))
GROUP BY 
	reportusercredential.id
	

Open in new window


question is why do you need
(('1'='1'))
and
GROUP BY reportusercredential.id


your query will work if you do

SELECT 
	reportusercredential.id, 
	reportusercredential.username, 
	reportuserpermission.id AS reportuserpermissionid, 
	reportusercredential.password, 
	reportusertype.id AS usertypeid, 
	Section.id AS sectionid,  
	reportusertype.name AS usertypename, 
	Branch.name AS branchname,  
	Section.name AS sectionname, 
	STUFF((SELECT ',' + name FROM Branch b WHERE ru.branchid = b.id FOR XML PATH ('')), 1, 1, '') AS branchname1
FROM 
	reportusercredential,
	reportuserpermission ru, 
	SECTION, 
	Branch b, 
	reportusertype 
WHERE 
	reportuserpermission.reportuserid = reportusercredential.id 
	AND reportuserpermission.sectionid=Section.id 
	AND reportuserpermission.branchid = Branch.id 
	AND reportusertype.id = reportusercredential.usertypeid 
	--AND (('1'='1'))
--GROUP BY 
--	reportusercredential.id
	

Open in new window

0
 

Author Closing Comment

by:zolf
ID: 38767341
thanks
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question