can anyone pls help me to join tis 2 query statement

[simonyong]

1. select * from MyDataBase
2. select count(*)as total_child,dir ect_mngr_person nel_number as personnel_numbe r from MyDataBase group by direct_mngr_per sonnel_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_per sonnel_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_per sonnel_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_per sonnel_number as personnel_numbe r, count(*) as total_child from MyDataBase group by direct_mngr_per sonnel_number) b on a.personnel_num ber = b.personnel_num ber where lower(a.firstna me) like '%" + TextBox1.Text.T oLower.Replace( "'", "''") + "%' and lower(a.lastnam e) like '%" + TextBox2.Text.T oLower.Replace( "'", "''") + "%'order by " + DropDownList1.S electedValue.To String + " asc"