How to use select join

[skeptic_rob]

---

guys, i need ur help how to use join in vb ...especially in query?

[dodie]

Hi, my work is with database department

I can help you explain basics of join. Usually we call it equi-join.
When you join multiple tables. It could be: 1 is to 1, 1 is to many, or many is to 1.

The standard of equi-join number of tables minus 1:
For example if you have 6 tables, you should perform equi-join in 5 conditions. You can perform more than 5 conditions if want it to be more strict.

Example of 3 tables, with 2 equi-join conditions.
this EQUI-JOIN serving employee table as the center point.

SELECT emp.employee_id, dept.department_id , comp.company_id
FROM 
company as comp,
employee as emp,
department as dept
WHERE comp.company_id = emp.company_id -- equi join company ID
AND  dept.deptartment_id= emp.department_id -- equi join department ID

[MalditohoN]

guys, i need ur help how to use join in vb ...especially in query?

bro, e specify kuno unsay imo buhaton... ang sample code ni dodie ok naman na... but it depends kung unsay program imo buhaton sa imo vb w/ respect sa query...

[skeptic_rob]

Dim RS As New ADODB.Recordset

RS.Open " select ID1,FIRSTNAME FROM PEOPLE c1 inner join STUDENT s2 where s2.ID1 = c1.ID", conn, adOpenDynamic, adLockOptimistic
With RS
RS!ID = TXTID.Text
RS!STUDENTNAME = TXTSTUDENTNAME.Text
'RS.Update
End With
In ani bro ako man g try ug test dli lagi mo work ..wla lagi changes sa database

[reyarita]

Dim RS As New ADODB.Recordset

RS.Open " select ID1,FIRSTNAME FROM PEOPLE c1 inner join STUDENT s2 where s2.ID1 = c1.ID", conn, adOpenDynamic, adLockOptimistic
With RS
RS!ID = TXTID.Text
RS!STUDENTNAME = TXTSTUDENTNAME.Text
'RS.Update
End With
In ani bro ako man g try ug test dli lagi mo work ..wla lagi changes sa database

isuwat daw tanan tables and mga fields sa table.

im trying msaccess para sa mga join table.
sayon rana sa msaccess.

[fixyourself]

Dim RS As New ADODB.Recordset

RS.Open " select ID1,FIRSTNAME FROM PEOPLE c1 inner join STUDENT s2 where s2.ID1 = c1.ID", conn, adOpenDynamic, adLockOptimistic
With RS
RS!ID = TXTID.Text
RS!STUDENTNAME = TXTSTUDENTNAME.Text
'RS.Update
End With
In ani bro ako man g try ug test dli lagi mo work ..wla lagi changes sa database

Of course walai changes sa db, because you are executing a select query, not an update/create statement..

JOINS are useful for creating views and executing one query involving 2 or more tables instead of multiple queries.


About your select statement above; That only selects the columns ID1 and FirstName from table PEOPLE providing that the predicate statement in your join is true. However the data to be stored in your recordset is just a virtual table (some sort of view), the result of your query.

Im not an expert in vb6; but maybe that didnt save because that table never really existed, its only a virtual table.

By the way, you're using a SQL statement to retrieve data, why not use an sql statement for update? Its supposed to be that way.

Connection.ExecuteNonQuery, sa ako ng nahinumdoman..

skeptic_rob, you're from UC?

[Cybernaut]

Dim RS As New ADODB.Recordset

RS.Open " select ID1,FIRSTNAME FROM PEOPLE c1 inner join STUDENT s2 where s2.ID1 = c1.ID", conn, adOpenDynamic, adLockOptimistic
With RS
RS!ID = TXTID.Text
RS!STUDENTNAME = TXTSTUDENTNAME.Text
'RS.Update
End With
In ani bro ako man g try ug test dli lagi mo work ..wla lagi changes sa database

Hehehe.. murag ang imong gusto mahitabo kay either INSERT or UPDATE but sa imong query you are using SELECT mao nga dili jud na mo work..

[dodie]

Hehehe.. murag ang imong gusto mahitabo kay either INSERT or UPDATE but sa imong query you are using SELECT mao nga dili jud na mo work..

bitaw noh hahahahahah
mag istorya sa multiple tables para UPDATE kaysa SELECT.

Food for Thought

WARNING!!!!
JOIN, LEFT JOIN , "traditional condition" .. performs differently. I rather suggest the traditional condition for equi-joins.

Let's get back to basics this time... about UPDATE.
previously I exampled this.

SELECT emp.employee_id, dept.department_id , comp.company_id
FROM 
company as comp,
employee as emp,
department as dept
WHERE comp.company_id = emp.company_id -- equi join company ID
AND  dept.deptartment_id= emp.department_id -- equi join department ID

If we want to perform UPDATE to Multiple tables. We could do this. Re-using same table syntax, and conditions.

UPDATE
company as comp,
employee as emp,
department as dept
SET   employe.somethingcolumn = somthingtable.somethingcolumn
WHERE comp.company_id = emp.company_id -- equi join company ID
AND  dept.deptartment_id= emp.department_id -- equi join department ID

[sanggano_boy]

programmers should always apply the most simple solutions to a problem. add lang ko.

if you want your queries to be flexible in most db server use the basic in joining tables like join, left join, right join, union --> for merging more than one dataset into one.

- To use sql update statement
UPDATE table SET field1=actual value WHERE condition here



FREE advertising: Company, Job openings & resume
@ Cebujobline.com

[fixyourself]

@Dodiebax

bout your food for thought.

WARNING!!!!
JOIN, LEFT JOIN , "traditional condition" .. performs differently. I rather suggest the traditional condition for equi-joins.

That depends on what you are trying to do, your equi-join is like an inner join statement, RIGHT and LEFT joins have their purpose also.

There are things your equi-join can't do, so please do take off the warning. Those JOIN statements are useful..

lol..