CODEWITHSUNDEEP
sql
Nico
Nico

Reputation: 237

SQL Join 3 tables

I'm trying to join 3 tables in 1 SQL query but I'm really a 'noob' with the join query, this is what I've 'created' so far :

SELECT m.Mod_ID as Modelnr, m.Mod_Naam as Modelnaam, m.Mod_Omschrijving as Omschrijving, m.Taal_ID as Taal, m.User_ID as Ontwerper 
FROM Model as m 
INNER JOIN Login as l ON m.User_ID = l.User_ID 
INNER JOIN Taal as t ON m.Taal_ID = t.Taal_ID
WHERE m.User_ID = '" + userid + "'

The DB was created inside Visual studio (asp.net c#), the Tables : Model, Taal and Login.

Tables & Relations : http://i52.tinypic.com/2upxmbk.jpg

Code behind to fill the gridview :

SqlCommand objCommand = new SqlCommand("SELECT m.Mod_ID as Modelnr, m.Mod_Naam as Modelnaam, m.Mod_Omschrijving as Omschrijving, m.Taal_ID as Taal, m.User_ID as Ontwerper from Model as m INNER JOIN Login as l ON m.User_ID = l.User_ID INNER JOIN Taal as t ON t.Taal_ID = t.Taal_ID WHERE m.User_ID = '" + userid + "' ", con);
dr = objCommand.ExecuteReader();
gvModel.DataSource = dr;
gvModel.HeaderStyle.HorizontalAlign = HorizontalAlign.Left;
gvModel.DataBind();

Trying to show Model in a gridview with the corresponding language from Taal (Taal.Taal) and user from Login (Login.U_Naam).

What I'm getting atm (only 3 rows in Model inside the DB) : http://i55.tinypic.com/2wbrcd2.jpg

But I'm still getting just number ID's in my gridview. Anyone who can help me out ?

thanks !

Upvotes: 1

Views: 485

Answers (3)

Vineet1982
Vineet1982

Reputation: 7918

Try to use this command without inner join

Select
    m.*, l.*, t.*
from
    Model as m,
    Login as l,
    Tall as t
Where
    (m.User_ID = l.User_ID and m.Taal_ID = t.Taal_ID)
    and
    (m.User_ID = '" + userid + "');

Upvotes: 0

Jon
Jon

Reputation: 16728

It looks to me like your query is doing exactly what it's supposed to - the problem is in the "select" part of your query - everything is coming from the Model table.

After you do the "from" and the joins, the data that you're selecting looks like this:

|         these columns come from the Model table          |                          these come from the User table                              |   Taal table   |
| Mod_ID | Mod_Naam | Mod_Omschrijving | Taal_ID | User_ID | User_ID | Username | Password | U_Naam | U_Voornaam | U_Geboorte | U_Dienst | (....) | Taal_ID | Taal |

...you can think of it kind of like "one big table". So you want to select things from the User and Taal tables too - otherwise you made the database do all the work of joining them for nothing. I'm pretty sure that what you want is:

SELECT m.Mod_ID as Modelnr, m.Mod_Naam as Modelnaam, m.Mod_Omschrijving as Omschrijving,
    t.Taal as Taal, l.U_Naam as Ontwerper -- <-- this is the only bit that's different
FROM Model as m 
INNER JOIN Login as l ON m.User_ID = l.User_ID 
INNER JOIN Taal as t ON m.Taal_ID = t.Taal_ID
WHERE m.User_ID = @user_ID

I also fixed the SQL injection problem you have, to call this in .NET, do this:

SqlCommand objCommand = new SqlCommand("SELECT .... etc", con);
objCommand.Parameters.AddWithValue("@user_ID", userid);
dr = objCommand.ExecuteReader();
// as before

Upvotes: 0

Rainman
Rainman

Reputation: 101

Are you testing this statement in a SQL tool directly against the database or are you testing it directly in your application? Try it against the database first before you put it in your application.

What DBMS are you using?

Upvotes: 1

Related Questions