Joining 2 queries to make 3rd query in code

[ChrisN]

I have a SQL7 database. I don't particularly want to store queries (views) in the database itself so I was wondering, is it possible to create two sub querys then join them together in order to get the correct result set in VB. The result I need cannot be done in one query (At least I think not!!)

TIA,
Chris

 

[softwarescience]

Hello

Your request is a bit vague.

What exactly do you want to do?
a) a union (two disparate query returning one resultset)
b) a query being conditioned on a sub query
c) string concatenation

Let us know what you're after.


ps if it is item 'c' then all you need is;

MyWholeSql = MySql1 & MySql2

 

[ChrisN]

Hi,

Sorry for the vagueness of my original post.

It is the option b that you have stated, I need two queries to be 'JOIN' together (i.e. related by a common field).

Cheers,
Chris

 

[softwarescience]

Righty,

Its a bit tricky without an example but here goes;

Qry1 = "SELECT fld1, fld2 FROM Tbl1 WHERE Fld3 = 'Bob'"
Qry2 = "SELECT fld1 FROM Tbl2 Where Fld3 = 'BErt'"
CombQry = qry1 & " AND Fld1 IN (" & Qry2 & &quot

IE You are conditioning Qry1 on Fld1 using the results of Qry2

It this what you're after?

 

[sunaj]

If your query cannot be made as a JOIN, check out the UNION keyword.

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'