Query

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
Here is my Data Tables
tbl_PURCHASE

branch_ID
PO_ID
Supplier_ID
Order_Date
Item_No
Item_Price
Item_Quantity
Modified_Date
Modified_By

tbl_SUPPLIER

Supplier_ID
Supplier_Name

when i click to search the PO_ID then
Fill in Four Text fields with
branch_ID , PO_ID, Supplier_ID, Supplier_Name

and then

in the DataGrid display
Order_Date, Item_No, Item_Price, Item_Quantity


i think i have to set the table ... but i dun know how to set it !!!

VB.NET:
  Private Sub GetUserDetails()

		objDS.Clear()
		objDS = objUser.Search_tblUser("Select p.Order_Date, p.Item_No, p.Item_Price, p.Item_Quantity, s.Supplier_Name from tbl_PURCHASE p, tbl_SUPPLIER s where p.SUPPLIER_ID = s.SUPPLIER_ID and p.PO_ID = '" & txtPOID.Text & "'")
		Try

			dgPOrderItems.DataSource = objDS.Tables(0)

			If objDS.Tables(0).Rows.Count > 0 Then

				With objDS.Tables(0)

					txtPOBranchID.Text = objFormat.FormatString(.Rows(0).Item("Branch_ID"))
					txtPOID.Text = objFormat.FormatString(.Rows(0).Item("PO_ID"))
					txtPOSupplierID.Text = objFormat.FormatString(.Rows(0).Item("Supplier_ID"))
					txtPOsuppliername.Text = objFormat.FormatString(.Rows(0).Item("s.Supplier_Name"))

				End With

			End If

		Catch ex As Exception

		End Try

	End Sub

But it is not work at ALL ...
i had tired the INNER JOIN ... but i'm sure that i totally dun know how to user INNER JOIN !!

PLEASE HELP ME !!!!!!!!!!!!!
 

mzim

Well-known member
Joined
Jun 3, 2004
Messages
187
Location
Other side of the rock
Programming Experience
1-3
are you using MSSQL database?
if so try to read the books online it is part of package in SQL. dunno if access database has also books online.
here's what i've read in books online.
Using Inner Joins

An inner join is a join in which the values in the columns being joined are compared using a comparison operator.

In the SQL-92 standard, inner joins can be specified in either the FROM or WHERE clause. This is the only type of join that SQL-92 supports in the WHERE clause. Inner joins specified in the WHERE clause are known as old-style inner joins.

This Transact-SQL query is an example of an inner join:

USE pubs
SELECT *
FROM authors AS a INNER JOIN publishers AS p
ON a.city = p.city
ORDER BY a.au_lname DESC
This inner join is known as an equi-join. It returns all the columns in both tables, and returns only the rows for which there is an equal value in the join column.

Here is the result set:

au_id au_lname au_fname phone address city
----------- -------- -------- ------------ --------------- --------
238-95-7766 Carson Cheryl 415 548-7723 589 Darwin Ln. Berkeley
409-56-7008 Bennet Abraham 415 658-9932 6223 Bateman St. Berkeley

state zip contract pub_id pub_name city state country
----- ----- -------- ------ --------------------- -------- ----- -------
CA 94705 1 1389 Algodata Infosystems Berkeley CA USA
CA 94705 1 1389 Algodata Infosystems Berkeley CA USA

(2 row(s) affected)
In the result set, the city column appears twice. Because there is no point in repeating the same information, one of these two identical columns can be eliminated by changing the select list. The result is called a natural join. You can restate the preceding Transact-SQL query to form a natural join. For example:

USE pubs
SELECT p.pub_id, p.pub_name, p.state, a.*
FROM publishers p INNER JOIN authors a
ON p.city = a.city
ORDER BY a.au_lname ASC, a.au_fname ASC
Here is the result set:

pub_id pub_name state au_id au_lname au_fname
------ --------------- -------- ----------- -------- -------- 1389 Algodata Infosystems CA 409-56-7008 Bennet Abraham
1389 Algodata Infosystems CA 238-95-7766 Carson Cheryl

phone address city state zip contract
--------------- ------------- -------- ----- ----- ---------
415 658-9932 6223 Bateman St. Berkeley CA 94705 1
415 548-7723 589 Darwin Ln. Berkeley CA 94705 1

(2 row(s) affected)
In this example, publishers.city does not appear in the results.

Joins Using Operators Other Than Equal

You can also join values in two columns that are not equal. The same operators and predicates used for inner joins can be used for not-equal joins. For more information about the available operators and predicates that can be used in joins, see Using Operators in Expressions and WHERE.

This Transact-SQL example is of a greater-than (>) join which finds New Moon authors who live in states that come alphabetically after Massachusetts, where New Moon Books is located.

USE pubs
SELECT p.pub_name, p.state, a.au_lname, a.au_fname, a.state
FROM publishers p INNER JOIN authors a
ON a.state > p.state
WHERE p.pub_name = 'New Moon Books'
ORDER BY au_lname ASC, au_fname ASC
Here is the result set:

pub_name state au_lname au_fname state
---------------- ------- -------------------- -------------------- -----
New Moon Books MA Blotchet-Halls Reginald OR
New Moon Books MA del Castillo Innes MI
New Moon Books MA Greene Morningstar TN
New Moon Books MA Panteley Sylvia MD
New Moon Books MA Ringer Albert UT
New Moon Books MA Ringer Anne UT

(6 row(s) affected)
Joins Using the Not-equal Operator

The not-equal join (< >) is rarely used. As a general rule, not-equal joins make sense only when used with a self-join. For example, this not-equal Transact-SQL join and self-join are used to find the categories with two or more inexpensive (less than $15) books of different prices:

USE pubs
SELECT DISTINCT t1.type, t1.price
FROM titles t1 INNER JOIN titles t2
ON t1.type = t2.type
AND t1.price <> t2.price
WHERE t1.price < $15 AND t2.price < $15


mk:@MSITStore:C:%5CProgram%20Files%5CMicrosoft%20SQL%20Server%5C80%5CTools%5CBooks%5Cacdata.chm::/Basics/note.gif Note The expression NOT column_name = column_name is equivalent to column_name < > column_name.

This Transact-SQL example uses a not-equal join combined with a self-join to find all rows in the titleauthor table in which two or more rows have the same title_id but different au_id numbers (that is, books with more than one author):

USE pubs
SELECT DISTINCT t1.au_id, t1.title_id
FROM titleauthor t1 INNER JOIN titleauthor t2
ON t1.title_id = t2.title_id
WHERE t1.au_id <> t2.au_id
ORDER BY t1.au_id
Here is the result set:

au_id title_id
----------- --------
213-46-8915 BU1032
267-41-2394 BU1111
267-41-2394 TC7777
409-56-7008 BU1032
427-17-2319 PC8888
472-27-2349 TC7777
672-71-3249 TC7777
722-51-5454 MC3021
724-80-9391 BU1111
724-80-9391 PS1372
756-30-7391 PS1372
846-92-7186 PC8888
899-46-2035 MC3021
899-46-2035 PS2091
998-72-3567 PS2091
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
You can also use the Query Builder provided by Visual Studio to generate the SQL statement visually. To do this, drag a DataAdapter to a form. This opens the Data Adapter Configuration Wizard. Choose or create the connection, Select the 'Use SQL statement' option, then click the Query Builder button. This will allow you to add the tables, create relations, and add parameters (criteria).
 

rex028

Well-known member
Joined
Nov 18, 2004
Messages
50
Programming Experience
Beginner
i'm using MS Access ....

it doesn't allow me using INNER JOIN !!
so frustrated !!

what can i do ???

another Question ...

how will the SQL statement to be if i want to insert some value into two table ????
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
MS Access does use INNER JOIN. You can use the query designer in Access to generate the SQL statements visually as well as the query builder in Visual Studio.

Try using one or the other, or both. Let us know if you have specific problems/questions.

You'll probably need two INSERT statements to insert values into two tables.
 
Top Bottom