How to write Simple.Data query to join two tables?

Question

I need to write Simple.data queries for following SQL queries can you help me ?

SELECT
    Table1.UserID,
    Table1.we, Table1.ba, Table1.re,
    Table1.rtes, Table1.datae, Table1.void,
    Table1.deletee
FROM
    Table1
INNER JOIN 
    Table1 ON UserID.UserID = Table2.UserID  
WHERE
    Table2.clinicId = 11

I try it following way

db.Table1.FindAll()
         .Where(db.Table1.UserID == db.Table2.FindAll(db.Table2.ClinicID = 11).Select(db.Table2.UserID));

but it does not work. I use mysql 4.0

score 2 · Accepted Answer · answered Jan 22 '14 at 05:57

db.Table2.FindAllByClinicId(11)
  .Select(
    db.Table2.Table1.UserId,
    db.Table2.Table1.we,
    db.Table2.Table1.ba,
    db.Table2.Table2.re,
    db.Table2.Table1.rtes,
    db.Table2.Table1.datae,
    db.Table2.Table1.void,
    db.Table2.Table1.deletee);

That should end up sending this to the database:

SELECT Table1.UserId
     , Table1.we
     , Table1.ba
     , Table1.re
     , Table1.rtes
     , Table1.datae
     , Table1.void
     , Table1.deletee
FROM Table1
     INNER JOIN Table2 ON Table1.UserId = Table2.UserId
WHERE Table2.ClinicId = 11

Thanks .can you tell me how can i select * from Table1 in your answer — user3044294, Jan 22 '14 at 06:03

score 0 · Answer 2 · answered Jan 22 '14 at 05:15

0

you are joining with same table. so you need to join two different tables or join same table giving alias.

SELECT Table1.UserID, Table1.we, Table1.ba, Table1.re, Table1.rtes, Table1.datae,
Table1.void, Table1.deletee FROM Table1 INNER JOIN Table2 ON 
UserID.UserID = Table2.UserID
where Table2.clinicId=11

OR

using alias for same table.

SELECT t1.UserID, t1.we, t1.ba, t1.re, t1.rtes, t1.datae, t1.void, t1.deletee FROM 
Table1 as t1 INNER JOIN Table1 as t2 ON UserID.UserID = t2.UserID
    where t2.clinicId=11

answered Jan 22 '14 at 05:15

Suraj Shrestha

1,790
1
25
51

+1 for using alias , they come handy when u have a lot of tables – z atef Jan 22 '14 at 05:28
i dont need sql quries i need Simple.Data quries – user3044294 Jan 22 '14 at 05:35

z atef · Answer 3 · 2014-01-22T05:25:51.087

-1

This should work, you should just say " table1 inner join tabloe2". && condition Table1.UserID = Table2.UserID where Table2.clinicId=11

  SELECT Table1.UserID, Table1.we, Table1.ba, Table1.re, Table1.rtes, Table1.datae,                
  Table1.void, Table1.deletee  
  FROM Table1
  INNER JOIN Table2 
  ON Table1.UserID = Table2.UserID
  where Table2.clinicId=11

edited Jan 22 '14 at 05:25

answered Jan 22 '14 at 05:18

z atef

7,138
3
55
50

i dont need sql quries i need Simple.Data quries – user3044294 Jan 22 '14 at 05:36

How to write Simple.Data query to join two tables?

3 Answers3