hi, can i use count and top 6 in the same sql command ?? and also, how can i use inner joins to link guest id in reservation table with guest name from guest table

The easiest way would be to get all and then count top 6 from the dataTable.

//create connection, command (cmd)
DataTable table = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(table);

//select top 6:
//you can even sort datra if you want!!
for(int i = 0; i < table.Rows.Count; i++)
{
   if(i < 6)
   {
        //show data where ever...
   }
   else
        break;
}

i can't do that since i need it to design a chart

So you would like to create a sql Query?

yes !!

...can i use count and top 6 in the same sql command ??

Sure you can.

select top 6 roomid, count(GuestId) 
from tblGuest a
inner join tblReservation b
on a.GuestId = b.GuestId
group by b.RoomId

That will give you a count of guests for a room. Obviously a simplistic query, you'll have to make your own adjustments to make it "real", but the technique works.

...how can i use inner joins to link guest id in reservation table with guest name from guest table

The query above demonstrates how to code an inner join. Just get rid of the group by and the count and the top 6, and you'll get what you need, like so:

select b.roomid, a.guestname 
from tblGuest a
inner join tblReservation b
on a.GuestId = b.GuestId

thanks for the help !!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.