asp.net sql join

[sarabonn]

Hallo Everyone,

I am using sql dataset and tableadapter to fill data into the gridview using object data source. i have 3 database table

statistik
------------
date client container charges password
26.04.10 testing 20 100 test1
25.04.10 testing 40 100 test1
25.04.10 newtester 40 150 sre

contact
------------
matchcode firstname lastname customerpassword
testhamb jen hansen test1
newtest tester sre
testhamb anna fletcher testing

Address
------------

matchcode client city

testhamb testing hamburg
testbrem testing bremen
newtest newtester Bonn

Now i have to populate resultset into gridview like this

Date client city container charges

26.04.10 testing hamburg 20 100 test1
25.04.10 testing hamburg 40 100 test1
25.04.10 newtester Bonn 40 150 sre

first i have to match with the password and then take the matchcode and then match with the matchcode in the address table. how can do this any idea ?.. left outer join is not working out.

[Frinavale]

Are you having problems displaying the data or getting it?

-Frinny

[sarabonn]

@Frinavale
Hallo Frinavale,

I am having problem getting the data.. i need populate like this

Now i have to populate resultset into gridview like this

Date client city container charges

26.04.10 testing hamburg 20 100 test1
25.04.10 testing hamburg 40 100 test1
25.04.10 newtester Bonn 40 150 sre
25.04.10 newteste 20 200
like this

[semomaniz]

This is what you need to do.

Table 1 : A with primary key id
Table 2 : B with foreign key id
Table 3: C with foreign key id.

here is the sql statement

Select * from A 
    join B on B.id = A.id 
    join C on C.id = A.id
where A.id = 1234  
 

now using the above format you can retrieve the data from the database and then fill the dataset and then bind it to gridview

Also i would recommend to replace * with the actual field names.

[Frinavale]

I haven't had to query a database in a long time but you could build your SQL query as such

SELECT table1.col1, table1.col2, table2.col1, table2.col2 
FROM table1, table2 
WHERE table1.somecol = 'xyz' AND table2.somecol = 'xyz'

So you would have something like:

SELECT statistik.date, statistik.client, address.city, statistik.container, statistik.charges
FROM statistik, address
WHERE statistik.password = 'thePassword' AND address.password = 'thePassword'

-Frinny

[Frinavale]

Semomaniz's answer is probably the better solution ;)
Like I said it's been a long time since I've queried a database and I'm pretty rusty.