Click here to Skip to main content
14,875,227 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
Hi brother! im new in c# and sql
i need your help aout it now ^^!

so, i have a table like this

id, parent_id, name
1, 0, max
2, 1, john
3, 1, sean

4, 2, truong
5, 2, muong
........

(you will see id 1 have 2 child and id 2 have 2 child)

i want to make code can check in sql data if 'id' have 2 'parent_id' will switch to next 'id' if have new data insert. (ps: each 'id' just can hold 2 childs)
Posted
Comments
Andy Lanng 14-Jul-15 10:26am
   
I don't know what you mean by "switch to the next id", but conditional counts are simple enough. I'll add a solution but let me know if it's not what you need

1 solution

So the format of an SQL query is as follows:

Select [column [, secondcolumn [etc]]
from [table [join table2 [etc]]
[where clause [and|or secondclause [etc]]]
[group by field [, secondclause [etc]]]
[having clause [ and|or secondclause [etc]]]

where clause operates on the flat table, the having clause operates on aggregates.

So, we can get the aggregate using a group by:

SQL
Select parent_id, Count(*) as 'count'
from table
group by parent_id


This list will look like this:
parent_id, count
0, 1
1, 2
2, 2

we can then add a having clause on the aggregate field 'count':

SQL
Select parent_id, Count(*) as 'count'
from table
group by parent_id
having count(*) < 2


Now our results look like
parent_id, count
0, 1


Is that what you needed?
   
v2
Comments
EADever 14-Jul-15 11:48am
   
first of all thanks for your help!
The 2nd query that i needed, i just wonder how i can make it work in c# code :(
Andy Lanng 14-Jul-15 12:32pm
   
How do you mean "in c# code"?

There are loads of resources on how to perform sql queries from c#

If you are using a model-based database connection (i.e. entity framework) then it will easily translate into linq
EADever 14-Jul-15 22:06pm
   
do you have skype? i need your help if you have free time!
Andy Lanng 15-Jul-15 4:01am
   
No I don't (and I wouldn't give out personal details here. It's a rule ^_^)

I am always here. Post a new question if the subject is not regarding this question. I'll look for your post ^_^
EADever 15-Jul-15 7:41am
   
Oh i just dont know about it! Thanks for the informations :)

I have a question about sql query: How to get query result id if parent_id count < 2? (result will give id value)
Andy Lanng 15-Jul-15 7:43am
   
Post this as a new question. We're all about points here so we try to keep a thread on-topic.

Remember to include the query from the solution above

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900