Nested query, sub-query, join or what?

[mdsoren]

Here's my problem - I need to build a single recordset from multiple tables based on the input of one record ID. Given this ID I pull the "main" record from table one, search tables 2, 3, and 4 for linked records based on the ID already provided. Upto here I am fine and have the query working. But what I have to do is add to this recordset is add more fields based on the initial query results. Is that clear as mud?

What I have is company data split into 4 tables. One of those tables is an "addressbook" with ALL the address info in the database, companies as well as individuals. Once I have retrieved all the info on a single company from all the tables I need to take one piece of it, a contact name, and retrieve that contact names' address info from the "addressbook" table. So I am referencing the "addressbook" twice, but need to retrieve 2 different records from it.

Some other help I have recvd suggested using "ALIAS'ing" to call the same table twice. And that ALMOST works. What I need to get from my query is one solitary record, but instead I am getting 6! The exact same company data repeated 6 times with the only difference being the contact name and address. There it is returning ALL 6 individuals in the test data who populate the contact field amongst all the company records.

What I need is one company record + contact address info all summed up into a single record (this is for a data report in VB and the report can only handle one data source.) This data report is keyed on the company ID and should return one record and generate a single page report.

Can this be done or not!?

HELP!!!

 

[cmmrfrds]

To get the 2nd record out of the address table make it a correlated subquery. The subquery is on the same level as the other fields and just get 1 value.

Simple example.
select fld1, fld2, fld3,
(select 2ndfiedl from addressbook X
where X.thekey = A.thekey) as my2ndfield,
fld4, fld5
from addressbook A

 

[mdsoren]

THANK YOU cmmrfrds!!!

That did it, I figured since I could "visualize it" that it was possible, but I didn't know how.

The (SELECT...) AS XXX within the the main SELECT did the trick perfectly.

THANKS AGAIN, I was struggling for 2 days with that and now I can sleep

MDS

 

[cmmrfrds]

Good work, I am glad you were able to adapt my example.