sql statement query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

sql statement query

Author

Topic

cgoasduff
Starting Member

21 Posts

Posted - 2009-03-16 : 12:41:21

Hi there

Could you please help me to combine two working sql statement in one?

here is the first one

select dpt_id,dpt_name,(select count(*) FROM tbl_department WHERE dpt_subsection_of=sc.dpt_id) childnodecount FROM tbl_department sc where dpt_subsection_of IS NULL

Here is the second one

SELECT dpt_name,section, dpt_id = t1.dpt_id FROM tbl_dpt_users t1 INNER JOIN tbl_department t2 ON t1.dpt_id = t2.dpt_id WHERE user_id = 197

This is my unsuccessful atempt to combine them

select dpt_id = t1.dpt_id FROM tbl_dpt_users t1,dpt_name,
(select count(*) FROM tbl_department WHERE dpt_subsection_of=t2.dpt_id) childnodecount FROM tbl_dpt_users t1 INNER JOIN tbl_department t2 ON t1.dpt_id = t2.dpt_id where dpt_subsection_of IS NULL AND user_id=197

Much appreciated

Chris

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-03-16 : 13:09:40

Without knowing anything about your tables, this should at least get you started in the right direction

Jim


SELECT 
       dept.dpt_id
      ,dept.dpt_name
      ,us.section
      ,[childnodecount] = count(dept.*)
FROM
  tbl_department dept
INNER JOIN
   tbl_users us
ON
  dept.dpt_id = us.dpt_id
WHERE
    dept.dpt_subsection_of = dept.dpt_id
and us.[user_id] = 197
GROUP BY
       dept.dpt_id
      ,dept.dpt_name
      ,us.section

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 13:19:08

[code]
SELECT dpt_name,section, dpt_id = t1.dpt_id,t2.dpt_name,t3.cnt
FROM tbl_dpt_users t1
INNER JOIN tbl_department t2
ON t1.dpt_id = t2.dpt_id
INNER JOIN (SELECT dpt_subsection_of,count(*) as cnt
FROM tbl_department
GROUP BY dpt_subsection_of) t3
ON t3.dpt_subsection_of=t2.dpt_id
WHERE user_id = 197
[/code]

cgoasduff
Starting Member

21 Posts

Posted - 2009-03-16 : 13:32:09

Hi I tried your suggestion but I still get an error: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '*'.

Here is my tables and their relation

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 13:36:59

whats the output you're looking at? please post some sample data and output you desire out of them.,

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-03-16 : 13:41:16

Sorry, that was bad for on my part, try count(dept.dpt_id). Is the tbl_user part of this query?

Jim

cgoasduff
Starting Member

21 Posts

Posted - 2009-03-16 : 13:45:14

quote:
Originally posted by visakh16

whats the output you're looking at? please post some sample data and output you desire out of them.,

visakh16 thanks a lot for your help it now works !! have a great day.

jimf thanks for your help too.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-16 : 13:47:13

welcome

cgoasduff
Starting Member

21 Posts

Posted - 2009-03-17 : 11:14:54

Hello, Actually I might need another bit of help regarding the above.

I am working with asp.net and the sql statement populate a treeview.

The sql statement you helped me with is great as it select the root category that the user can access. The problem is that it displays all the child sections and all the leafs under them. Even if these sections and leafs are not supposed to be accessible according to the table "tbl_dpt_users" to illustrate this look at the data I have for both tables.

I would like the root node to be(dpt_id:2)(this bit works only dpt_id:2 is shown)
then it should show (dpt_id:8)as a leaf
then it should show (dpt_id:12)as a node (because it is parent of dpt_id:15 according to the dpt_subsection_of field )
then it should show (dpt_id:15)as a leaf of dpt_12

right now as I said it displays everything under dpt_id:2

Here is the code: ( the first sql statement work brilliantly but I can't figure out how to bring the INNER JOIN with the tbl_dpt_users in the second one to filter the subsections:

private void PopulateRootLevel()
{
SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["IMS_DBConnectionString"].ConnectionString);
SqlCommand objCommand = new SqlCommand(@"SELECT dpt_name,section, dpt_id = t1.dpt_id,t2.dpt_name,t3.cnt FROM tbl_dpt_users t1 INNER JOIN tbl_department t2 ON t1.dpt_id = t2.dpt_id INNER JOIN (SELECT dpt_subsection_of,count(*) AS cnt FROM tbl_department GROUP BY dpt_subsection_of)t3 ON t3.dpt_subsection_of=t2.dpt_id WHERE user_id = 197", objConn);

SqlDataAdapter da = new SqlDataAdapter(objCommand);
DataTable dt = new DataTable();
da.Fill(dt);
PopulateNodes(dt, TreeView1.Nodes);

}

private void PopulateSubLevel(int parentid, TreeNode parentNode)
{
SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["IMS_DBConnectionString"].ConnectionString);
SqlCommand objCommand = new SqlCommand(@"select dpt_id,dpt_name,(select count(*) FROM tbl_department WHERE dpt_subsection_of=sc.dpt_id) cnt FROM tbl_department sc where dpt_subsection_of=@dpt_subsection_of", objConn);
objCommand.Parameters.Add("@dpt_subsection_of", SqlDbType.Int).Value = parentid;
SqlDataAdapter da = new SqlDataAdapter(objCommand);
DataTable dt = new DataTable();
da.Fill(dt);
PopulateNodes(dt, parentNode.ChildNodes);

}

protected void TreeView1_TreeNodePopulate(object sender, TreeNodeEventArgs e)
{
PopulateSubLevel(Int32.Parse(e.Node.Value), e.Node);
}

private void PopulateNodes(DataTable dt, TreeNodeCollection nodes)
{
foreach (DataRow dr in dt.Rows)
{
TreeNode tn = new TreeNode();
tn.Text = dr["dpt_name"].ToString();
tn.Value = dr["dpt_id"].ToString();
nodes.Add(tn);

//If node has child nodes, then enable on-demand populating
tn.PopulateOnDemand = ((int)(dr["cnt"]) > 0);
}
}

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 11:24:26

why was 12 included though its not available in tbl_dpt_users?

cgoasduff
Starting Member

21 Posts

Posted - 2009-03-17 : 11:36:10

I suppose you are right dpt_id 12 should be added to the tbl_dpt_users.
Just adding it now..

Actually no.. if I add dpt_id 12 in tbl_dpt_users. then dpt_12 and dpt_2 appear at the same level. Where 12 should be a child of 2. So I better remove it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-17 : 11:41:07

i was asking why it was included in output when it was not in users table. i'm still not clear of your requirement

cgoasduff
Starting Member

21 Posts

Posted - 2009-03-17 : 11:55:14

Maybe this graphic will help

Currently my treeview displays figure A as a result of the both sql queries. As you can see, Project which is dpt_12 shows all its children which is not good because only dpt_15 is allocated to the user number 197

Figure B is what I would like to achieve.

thank you

cgoasduff
Starting Member

21 Posts

Posted - 2009-03-18 : 10:22:50

Please guys ! is what I want to do possible ? I am kind of stuck but quite close to my goal too..

cgoasduff
Starting Member

21 Posts

Posted - 2009-03-27 : 11:05:59

Really no one can help me with this I am (still) trully blocked!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-03-27 : 13:23:54

Graphics are all well and good. Try following the first link in my signature, and following what it says. It makes your problem MUCH more clear, and a LOT easier to solve.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL or How to sell Used Cars
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources