CODEWITHSUNDEEP
c#sqldatabasejoin
Scorps
Scorps

Reputation: 405

SQL - Retrieving Data from two related tables based on a value entered by a user

I am trying to get data from two separate but related tables (They have a foreign key between them) based on the value that the user enters. If the value is entered into textBox11 for example, my SQL statement is:

SQLCommandText = "select Invoice_Name.Inv_Nu, Invoice_Name.Price, Invoice_Name.IDate, AddInfo.Case_Nu, AddInfo.PaperName, AddInfo.Page_Nu, AddInfo.Case_Type, AddInfo.Size from Invoice_Name full outer join AddInfo on Invoice_Name.L_Name = " + "'" + textBox11.Text + "'";

This doesn't seem to work properly, I can't seem to find out why.

The two tables I have are:

Invoice_Name
------------
Inv_Nu
L_Name
Price
IDate

AddInfo
-------
Inv_Nu
Case_Nu
PaperName
PageNumber
Case_Type
Size

I want the user to enter the L_Name and the program to bring back all the info in BOTH tables where the L_Name is equal to that value. The foreign key here is the Inv_Nu taken from the Invoice_Name table.

Test data: 

Invoice_Name: 
Inv_Nu | L_Name | Price | IDate
01064 | Rami Isleem | 215 | 19/6/2013

AddInfo:
Inv_Nu | Case_Nu | PaperName | Page_Nu | Case_Type | Size
01064 | 100/20 | Alra'i | 21 | abc | 2x8

Upvotes: 0

Views: 101

Answers (1)

manji
manji

Reputation: 47978

You have to join the 2 tables on Inv_NU and filter on Invoice_Name.L_Name = textBox11.Text:

"select Invoice_Name.Inv_Nu, Invoice_Name.Price, Invoice_Name.IDate, 
        AddInfo.Case_Nu, AddInfo.PaperName, AddInfo.Page_Nu, 
        AddInfo.Case_Type, AddInfo.Size 
   from Invoice_Name 
   left join AddInfo on Invoice_Name.Inv_NU = AddInfo.Inv_NU
  where Invoice_Name.L_Name = " + "'" + textBox11.Text + "'";

Instead of FULL OUTER JOIN, use a LEFT JOIN because you're filtering Invoice_Name rows then joining AddInfo rows.

Upvotes: 2

Related Questions