Ever tried to perform nested joins in SQL. It can get pretty messy after a few joins...
On a previous project I had to join five tables with a combination of inner and outer joins. Click on the "Read more..." link to see the code I used in a my project.
OleDbCommand myCommand = new OleDbCommand(
"SELECT am.modell_id, m.tekst, m.modell_variant, am.sort_order, m.beskrivelse, m.antall, " +
"b.bilde_id, b.bildetype_id, b.bilde_navn, mb.brukertype_id, mb.enhetspris, mb.enhetspris_rabatt, mb.fraktpris, " +
"v.varetype_id, m.varenummer, v.varetype_tekst, v.varetype_rabatt, v.culture_code " +
"FROM artikkel_modell AS am " +
"INNER JOIN (modell_brukertype AS mb " +
"INNER JOIN (varetype AS v " +
"INNER JOIN (modell AS m " +
"LEFT OUTER JOIN bilde AS b " +
"ON (b.bilde_id=m.bilde_id)) " +
"ON (v.varetype_id=m.varetype_id)) " +
"ON (mb.modell_id=m.modell_id)) " +
"ON (m.modell_id=am.modell_id) " +
"WHERE (mb.brukertype_id=@brukertype_id) AND (am.artikkel_id=@artikkel_id) AND (m.slettet=@slettet) " +
"ORDER BY am.sort_order ASC", myConnection);
myCommand.CommandType = CommandType.Text;
myCommand.Parameters.AddWithValue("@brukertype_id", brukertype_id);
myCommand.Parameters.AddWithValue("@artikkel_id", artikkel_id);
myCommand.Parameters.AddWithValue("@slettet", slettet);
The call to this SELECT statement is as follows:
public static ArtikkelModellInfoList GetArtikkelModellList(Int32 artikkel_id, Int32 brukertype_id, bool slettet)
Now the SQL actually will retrieve all items from the "artikkel_modell" database having the specified "artikkel_id". The "modell_id" from this table is then used to get information from the table "modell_bruketype" and we now only retrieve information that matches the "modell_id" and the specified "brukertype_id". This is done by using an INNER JOIN betwteen modell.modell_id and artikkel_modell.modell_id and modell_brukertype.modell_id. We also want information from the "varetype" table, so we perform an INNER JOIN on this table as well using modell.varetype_id and varetype.varetype_id. Finally, we want information from the "Bilde" (Image) table, but since some modell entries do not have bilde_id's, we must perform and OUTER JOIN on this table.
Happy programming :)