can anyone pls help me to join tis 2 query statement

[simonyong]

1. select * from MyDataBase
2. select count(*)as total_child,direct_mngr_personnel_number as personnel_number from MyDataBase group by direct_mngr_personnel_number

both of the query statement is came from same table

i need it into 1 so that i can call it to dataSet..
Pls....
help me....
i hav no idea already....

[debasisdas]

If both the query return similar column list and datype you can go for UNION.

[simonyong]

If both the query return similar column list and datype you can go for UNION.

ya..i tried it just now...but cant because of "group by"

debasisdas,
may i ask that is it possible if i use left join?

[arachno]

Maybe you're looking for something in the likes of this:

SELECT direct_mngr_personnel_number, and_the_other_columns_you_need, totalcount.total_child
FROM MyDataBase
INNER JOIN (SELECT COUNT(*) AS total_child, direct_mngr_personnel_number FROM MyDataBase
GROUP BY direct_mngr_personnel_number) totalcount
ON MyDataBase.direct_mngr_personnel_number = totalcount.direct_mngr_personnel_number
 

On the last line I took a wild guess that direct_mngr_personnel_number is your primary key. If it's not, change it to your primary key.

[amitpatel66]

ya..i tried it just now...but cant because of "group by"

debasisdas,
may i ask that is it possible if i use left join?

What is the error that it is displaying??

[simonyong]

Maybe you're looking for something in the likes of this:

SELECT direct_mngr_personnel_number, and_the_other_columns_you_need, totalcount.total_child
FROM MyDataBase
INNER JOIN (SELECT COUNT(*) AS total_child, direct_mngr_personnel_number FROM MyDataBase
GROUP BY direct_mngr_personnel_number) totalcount
ON MyDataBase.direct_mngr_personnel_number = totalcount.direct_mngr_personnel_number
 

On the last line I took a wild guess that direct_mngr_personnel_number is your primary key. If it's not, change it to your primary key.

yaya...similiar tat....

[simonyong]

Thx for giving me idea, i had done it successfully with following:

select a.*, b.total_child from MyDataBase a left join (select direct_mngr_personnel_number as personnel_number, count(*) as total_child from MyDataBase group by direct_mngr_personnel_number) b on a.personnel_number = b.personnel_number where lower(a.firstname) like '%" + TextBox1.Text.ToLower.Replace("'", "''") + "%' and lower(a.lastname) like '%" + TextBox2.Text.ToLower.Replace("'", "''") + "%'order by " + DropDownList1.SelectedValue.ToString + " asc"