Join 3 tables using their ids

Question

Hi, I need a sql query which retrieves information from different tables and describe them in a datagrid. These are my tables.

tablename: register

-reg_id(PK)

-date

-timebegin

-begin_photo_id

-timeend

-end_photo_id

-pupils_id

tablename: pupils

-pupils_id(PK)

-name

-surname

-mid_name

-group_id

tablename: photos

-photo_id(PK)

-photo

Query meaning: Select date, timebegin, timeend from register; Select photo from photos where photo_id=begin_photo_if and photo_id=end_photo_id which is related to register table; Select concat(name, surname, mid_name) as fullname from pupils on pupils.pupils_id=register.pupils_id. That means instead register.pupils_id must be fullname which is taken from pupils. And last condition is show them where group_id=combobox.selectedvalue.

Result column names should be:

-fullname

-date

-timebegin

-photo

-timeend

-photo

where group_id=combobox_value

So I need some help, please.

Answer 1

Not exact code, but try something like this.

 

string Query = @"
Select concat(name, surname, mid_name) [fullname],
       date,
       timebegin,
       PStart.photo,
       timeend,
       PEnd.photo
from pupils
inner join register on register.pupils_id = pupils.pupils_id
left join photos PStart with (nolock) on PStart.photo_id=begin_photo_id
left join photos PEnd with (nolock) PEnd.onphoto_id=end_photo_id
where group_id = @group_id";

SqlCommand cmd= new SqlCommand(query,yourConnection);
SqlParameter param = new SqlParameter("group_id",SqlDbType.Varchar);
param.Value = combobox.selectedvalue.tostring();
cmd.Parameters.Add(param);
var result = cmd.ExecuteReader();

 

Answer 2

Thanks