CODEWITHSUNDEEP
c#sql
saad
saad

Reputation: 57

Nested select on three tables

Hi I have three tables like this

////////////////////////////////////////////
PlaceCodename   |     DeviceConfig   |   DevInOut
----------      |     ------------   |   --------
Place_name      |     IP   (PK)      |   IP
Place_code (PK) |     Place_code     |   Action
                |     TypeofUseCode  |   Cardserial (PK)
                |     deevNum        |   date   (PK)
                                     |   time    (PK)

For each Cardserial there are some IPs which can be repeated. It mean the primary key in DevInOut is the combination of (date,time and Cardserial).

I want to give Cardserial and based on the IP ,get Place_code from DeviceConfig and based on that get Place_name from PlaceCodename table.

I wrote this query but it doesn't work:

SqlDataAdapter a = new SqlDataAdapter("select Place_code from DeviceConfig inner join        (select IP from DevInOut where Cardserial = '" + textBox12.Text + "') tb on
DeviceConfig.IP = tb.IP)", con);
SqlCommandBuilder comdBuilder = new SqlCommandBuilder(a);
DataTable t = new DataTable();
t.Locale = System.Globalization.CultureInfo.InvariantCulture;
a.Fill(t);
bindingSource1.DataSource = t; 
........

I even tried data table it didn't work either.How can I solve my problem?

Upvotes: 0

Views: 64

Answers (2)

user240141
user240141

Reputation: 

Select Place_Name 
From PlaceCodename  N 
     INNER JOIN DeviceConfig   D 
                 on N.Place_code = D.Place_code     
     INNER JOIN DevInOut IO 
                 ON D.IP = IO.IP 
WHere IO.Cardserial ='YOUSERIAL'

Upvotes: 0

King King
King King

Reputation: 63367

You said you want the Place_name but your code shows that you select Place_code instead. You just need a normal WHERE to solve this:

SqlDataAdapter a = new SqlDataAdapter(string.Format("SELECT Place_name FROM PlaceCodeName, DeviceConfig, DevInOut WHERE Cardserial = '{0}' AND PlaceCodeName.Place_code = DeviceConfig.Place_code AND DeviceConfig.IP = DevInOut.IP",textBox12.Text), con);

Upvotes: 1

Related Questions