CODEWITHSUNDEEP
c#sql
Nico
Nico

Reputation: 237

2 SQL commands combined?

I've got 2 SQL commands which I need to populate a gridview. First I select every ID which the current user had filled out successfully with the following SQL command:

"SELECT Mod_ID FROM Toewijzing WHERE User_ID = '" + Session["userid"].ToString() + "' AND Toe_Status = '" + "ja" + "' "

Afterwards, for every ID found in the db I've got to get it's name, language, etc etc (different table) using following SQL command:

"SELECT Mod_ID, Mod_Naam, Mod_Omschrijving, Taal_ID, User_ID from Model WHERE Mod_ID = '" + dr["Mod_ID"].ToString() + "' "

To populate my gridview I can only use 1 SQL command. Is there any way to put these 2 commands into just 1 command?

Code used to populate the gridview:

        dr = cmd.ExecuteReader();
        gvIngevuld.DataSource = dr;
        gvIngevuld.DataBind();
        con.Close();

cmd being the SQL command.

Upvotes: 0

Views: 734

Answers (5)

dexter.ba
dexter.ba

Reputation: 292

"SELECT
    T.Mod_ID, M.Mod_Naam, M.Mod_Omschrijving, M.Taal_ID, M.User_ID
FROM Toewijzing T, Model M
WHERE
    T.User_ID = '" + Session["userid"].ToString() + "'
    AND T.Toe_Status = '" + "ja" + "' "
    AND M.Mod_ID = T.Mod_ID"

Use this query where you join these two tables based on Mod_ID attribute in both tables. You can also join multiple tables following this pattern.

Upvotes: 1

Hector Sanchez
Hector Sanchez

Reputation: 2317

SELECT Mod_ID, Mod_Naam, Mod_Omschrijving, Taal_ID, User_ID 
from Model
WHERE Mod_ID IN
(
    SELECT Mod_ID 
    FROM Toewijzing 
    WHERE User_ID = "'" + Session["userid"].ToString() + "'"
    AND Toe_Status = "'" + "ja" + "'"
)   

Or this way

SELECT  M.Mod_ID, M.Mod_Naam, M.Mod_Omschrijving, M.Taal_ID, M.User_ID 
from Model M, Toewijzing T
WHERE User_ID = "'" + Session["userid"].ToString() + "'" 
AND Toe_Status = "'" + "ja" + "'"
AND M.Mod_ID  = T.Mod_ID 

Regards.

Upvotes: 1

Shaun Bowe
Shaun Bowe

Reputation: 10008

"SELECT m.Mod_ID, m.Mod_Naam, m.Mod_Omschrijving, m.Taal_ID, t.User_ID 
   FROM Toewijzing t
   JOIN Model m on t.Mod_id = m.Mod_ID
  WHERE t.User_ID = '" + Session["userid"].ToString() + "' AND Toe_Status = 'ja' "

Upvotes: 1

Trevor Tippins
Trevor Tippins

Reputation: 2847

Perhaps you could try this:

SELECT T.Mod_ID, M.Mod_Naam, M.Mod_Omschrijving, M.Taal_ID, M.User_ID
FROM Toewijzing T
LEFT JOIN Model M
ON T.Mod_ID = M.Mod_ID
WHERE T.User_ID = Session["userid"] AND T.Toe_Status = "ja"

If you want all users in one go, just leave off the T.User_ID condition.

Upvotes: 3

Kromagg
Kromagg

Reputation: 69

You could make use of the JOIN statement to work around this issue.

Upvotes: 1

Related Questions