CODEWITHSUNDEEP
c#asp.netsqlsql-server
Mana
Mana

Reputation: 1929

joining two tables to be bound on a gridview

I was getting confused on how to build the SQL query when it come to joining two tables so that I could bind them later to a gridview.

Basically I have two tables,

tablename: family
 __________________________
|Status |lastName|firstName|
|-------|--------|---------|
|1      |11      |111      |
|2      |22      |222      |
|3      |33      |333      |
''''''''''''''''''''''''''''
            +

tablename: familyStatus
 _____________________
|id        | Notes    |
|----------|----------|
|1         | 1111     |
|2         | 2222     |
|3         | 3333     |
'''''''''''''''''''''''

           =

Joined-tables: family+familyStatus [this is what i want]
 ____________________________
|firstName | lastName |Notes |
|----------|----------|------|
|111       | 11       |1111  |
|222       | 22       |2222  |
|333       | 33       |3333  |
''''''''''''''''''''''''''''''

what your seeing here is that the Status from table family and id from familyStatus is the same key, status and id will be hidden on the gridview.

Lets just assume for now that this is how i get the family table.

SELECT Status, firstName, lastName
FROM family
WHERE firstName= @fN
AND lastName = @lN

What do I need to add here so that the Notes from family table also gets joined?

Upvotes: 2

Views: 3000

Answers (2)

Lamak
Lamak

Reputation: 70638

A join:

SELECT F.firstName, F.lastName, FS.Notes
FROM family F
INNER JOIN familyStatus FS
    ON F.[Status] = FS.id
WHERE F.firstName= @fN
AND F.lastName = @lN

Upvotes: 6

IrishChieftain
IrishChieftain

Reputation: 15253

Add a Status foreign key to the familyStatus table and try the following:

SELECT firstName, lastName, Notes
FROM family JOIN familyStatus
ON family.Status =  familyStatus.Status
WHERE firstName= @fN
AND lastName = @lN

Upvotes: 1

Related Questions