[Resolved] Do I need a UNION here?

[sbbrown]

Hey guys;

I am trying to get a DMX recordset query to work unsuccessfully. If DMX can't do a complex query, I guess I'll have to hand code it.

I have 2 tables - events2 and contacts2. The index on events2 is event_id and it also has a contact_id field. The contacts2 table has three fields, the index contact_id, last_name and first_name.

I can call a record from an events table (events2) based upon its event_id and thats all fine. But I am also want to display information about the contact person by using the contact_id field from the events2 table by connecting it with the contact_id field from the contacts2 table. In DMX it generates two separate queries so the query to connect events2.contact_id = contacts2.contact_id results in all contacts for all events being displayed. I just need the contact based upon the unique event_id.

Do I need a UNION here? I am hoping not - the mysql server I'm using is 3.2.xx.

Thanks.

[czambran]

Yap, you do. It would be something like this:

Select event_id, last_name, first_name from events2,contacts2 where events2.contact_id=contacts2.contact_id and event_id=$event_id_i_wanna_now_more_info_of

[sbbrown]

Looks like I need a subselect statement for this problem. The Mysql version of the server I am on is 3.2.2 - subselects weren't supported until 3.2.4. Several texts recommended using joins but I get the same result every time. I'm going to try this on another server with a later version of mysql to see if solves the problem.

Thanks for your help..