hello expertes
i have 2 tables , both having same table structure (i.e. same fields ) but having different Records. First Table for Male Candidate and another for Female Candidate.
both tables have 2 fields namely :- name and marks. Now i wanna write sql statement using adodb to perform the following:-
i wanna display all names(male and female) who secured at least 10 or higher.
Note:- i dont wanna use 2 recordsets for performing such a task . . .
Jump to PostYou want a UNION rather than a JOIN. Example:
SELECT * FROM Males WHERE Mark > 50 UNION SELECT * FROM Females WHERE Mark > 50will return one recordset
What code do you have so far?
You will be making use of inner join statement if you have primary keys set to your tables fields.
i opt the following to perform the task
con.Provider = "Microsoft.Jet.OLEDB.4.0"
con.Open App.Path & "\db.mdb"
rec.Open "Here i want select query to retrieve data from 2 tables", con, 2, 3
While (rec.EOF = False)
Print rec.Fields(0); Space(10); rec.Fields(1)
Wend
con.Close
Set con = Nothing
i didnt set primary key on table and is it necessary to have primary keys to solve the problem ?
and i not much familiar with joins . . .
You want a UNION rather than a JOIN. Example:
SELECT * FROM Males WHERE Mark > 50
UNION
SELECT * FROM Females WHERE Mark > 50
will return one recordset
Thank you Jim, simple and easy as always. Should have put more thought into it. ;)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.