join many select statement - 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

General SQL Server Forums

New to SQL Server Programming

join many select statement

Author

Topic

kazineel
Starting Member

16 Posts

Posted - 2010-06-18 : 09:41:44

Hey Guys, I want to join my all select statement, here is my all select statement. I don't understand how to join,

$sql="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as TTC FROM TestExecution WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result=mysql_query($sql);

$sql1="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as P FROM TestExecution WHERE status='P' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result1=mysql_query($sql1);

$sql2="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as N FROM TestExecution WHERE status='N' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result2=mysql_query($sql2);

$sql3="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as F FROM TestExecution WHERE status='F' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result3=mysql_query($sql3);

$sql4="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as U FROM TestExecution WHERE status='U' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result4=mysql_query($sql4);

$sql5="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as X FROM TestExecution WHERE status='X' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result5=mysql_query($sql5);

$sql6="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as Def FROM TestExecution WHERE status='Def' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result6=mysql_query($sql6);

$sql7="SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id, COUNT(TestCase_id) as B FROM TestExecution WHERE status='B' AND Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%' GROUP BY Tester_Id ";
$result7=mysql_query($sql7);

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-18 : 10:04:16

I think you just need this

SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as [Count] 
FROM TestExecution 
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%'
AND status IN ('P','N','F','U','X','B','Def') 
GROUP BY TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-18 : 10:05:19

select ...
UNION ALL
select ...

and so on

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-18 : 10:06:43

quote:

Originally posted by vijayisonly

I think you just need this

SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as [Count] 
FROM TestExecution 
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%'
AND status IN ('P','N','F','U','X','B','Def') 
GROUP BY TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status

Vijay don't post here!
USA is playing NOW

No, you're never too old to Yak'n'Roll if you're too young to die.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-18 : 10:11:33

quote:
Originally posted by webfred

quote:
Originally posted by vijayisonly

I think you just need this
SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as [Count]
FROM TestExecution
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%'
AND status IN ('P','N','F','U','X','B','Def')
GROUP BY TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status

Vijay don't post here!
USA is playing NOW

No, you're never too old to Yak'n'Roll if you're too young to die.

Headed to work after watching most of Germany - Serbia. You shud be sulking with beer...what are you doing posting here??

Boy...dint see that coming

kazineel
Starting Member

16 Posts

Posted - 2010-06-18 : 10:15:37

quote:

Originally posted by vijayisonly

I think you just need this

SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as [Count] 
FROM TestExecution 
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%'
AND status IN ('P','N','F','U','X','B','Def') 
GROUP BY TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status

I got this error

SQL query: Documentation

SELECT TestPlan_id, Category_id, Sub_category_id, Tester_Id,
STATUS , COUNT( TestCase_id ) AS [Count]
FROM TestExecution
WHERE Lab_id LIKE '%1%'
AND TestPlan_id LIKE '%1%'
AND Test_iteration_id LIKE '%1%'
AND TestCycle_id LIKE '%2%'
AND STATUS IN (
'P', 'N', 'F', 'U', 'X', 'B', 'Def'
)
GROUP BY TestPlan_id, Category_id, Sub_category_id, Tester_Id,
STATUS
LIMIT 0 , 30

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Count]
FROM TestExecution
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' ' at line 6

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-18 : 10:18:18

You seem to be using MySQL.This is a dedicated MS SQL Server Forum.
Try it without []

SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as Cnt
FROM TestExecution 
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%'
AND status IN ('P','N','F','U','X','B','Def') 
GROUP BY TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status

kazineel
Starting Member

16 Posts

Posted - 2010-06-18 : 10:22:23

quote:

Originally posted by vijayisonly

You seem to be using MySQL.This is a dedicated MS SQL Server Forum.
Try it without []

SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as Cnt
FROM TestExecution 
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%'
AND status IN ('P','N','F','U','X','B','Def') 
GROUP BY TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status

BINGO!!!! working. THanks Baddy

kazineel
Starting Member

16 Posts

Posted - 2010-06-18 : 10:35:31

quote:

Originally posted by vijayisonly

You seem to be using MySQL.This is a dedicated MS SQL Server Forum.
Try it without []

SELECT TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status
, COUNT(TestCase_id) as Cnt
FROM TestExecution 
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%'
AND status IN ('P','N','F','U','X','B','Def') 
GROUP BY TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status

One question, Can I count the result by status (P,N,...) , bcz if you see my result then you understand

Full Texts
TestPlan_id Category_id Sub_category_id Tester_Id status Cnt
1 1 1 22 N 2
1 1 1 22 P 18
1 1 2 22 P 17
1 1 3 22 P 14
1 1 4 22 Def 4
1 1 6 22 P 74
1 1 7 22 Def 4

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-18 : 10:39:31

Remove Sub_category_id from the SELECT and GROUP BY

kazineel
Starting Member

16 Posts

Posted - 2010-06-18 : 10:52:13

quote:
Originally posted by vijayisonly

Remove Sub_category_id from the SELECT and GROUP BY

Thanx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-18 : 11:55:14

Np. You are welcome

kazineel
Starting Member

16 Posts

Posted - 2011-06-19 : 11:46:12

quote:
Originally posted by vijayisonly

Np. You are welcome

Hey Guys,
About this query,
SELECT TestPlan_id
,status
, COUNT(TestCase_id) as Cnt
FROM TestExecution
WHERE Lab_id LIKE '%1%' AND TestPlan_id LIKE '%1%' AND Test_iteration_id LIKE '%1%' AND TestCycle_id LIKE '%2%'
AND status IN ('P','N','F','U','X','B','Def')
GROUP BY TestPlan_id
, Category_id
, Sub_category_id
, Tester_Id
,status

The Result is like this

TestPlan_id status Cnt
1 N 2
1 P 18
1 P 17
1 P 14
1 Def 4
1 P 74
1 Def 4

How to display the result row to column

like this way

TestPlan_id P N Def
1 2 1 4
2 5 5 6
Total 7 6 10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources