[Resolved] MySQL result set problem

[perpetualshaun]

I'm trying to pull back two result sets into one so that I can then display the results in an HTML table. Here's my dilemma:

I run this query and I get six matches:
SELECT contacts_215.contact_id AS contact_id, contacts_215.account_id AS account_id, contacts_215.account AS account, contacts_215.first_name AS first_name, contacts_215.last_name AS last_name, contacts_215.address AS address, contacts_215.address_2 AS address_2, contacts_215.city AS city, contacts_215.state AS state, contacts_215.zip AS zip FROM contacts_215 WHERE 1 AND contacts_215.zip=45342 LIMIT 0, 30

Then I run this query and get four matches:
SELECT accounts_215.account_id AS account_id, accounts_215.account AS account, accounts_215.address AS address, accounts_215.address_2 AS address_2, accounts_215.city AS city, accounts_215.state AS state, accounts_215.zip AS zip FROM accounts_215 WHERE accounts_215.zip=45342

I tried a few different JOIN possibilities, but basically I'm looking for ONE query that will pull back the 10 matches from both queries into one result set. Any ideas?

Thanx,

Shaun

[ultraslacker]

If contacts_215.account_id is the foreign key, then the JOIN should be straightforward.

SELECT ....
FROM contacts_215 c215
INNER JOIN accounts_215 a215
ON (c215.account_id = a215.account_id)
WHERE c215.zip = 45342 AND
a215.zip = 45342

Seeing zip as an integer is odd, as their are zips beginning with '0'.

[perpetualshaun]

I tried the query suggested below that it didn't quite work. I tried to simplify the query to see if I can get the right contact_id and account_id values back from the query that I'm trying to use:

SELECT c215.contact_id, a215.account_id FROM contacts_215 c215
INNER JOIN accounts_215 a215
ON (c215.account_id = a215.account_id)
WHERE c215.zip = '45342' OR
a215.zip = '45342' LIMIT 0, 30

That is giving me back 9 results (instead of 10) and some of the results in the account_id column are NOT part of the result set that I get back when I search just the account table by itself for the zip code specified.

Does anyone have any ideas of another approach I can take?

[ultraslacker]

JOINs are different from UNIONs - the rows returned in a JOIN will differ from those returned in the separate queries. For instance, the INNER JOIN in my example requires that the rows returned have corresponding account_id rows in both tables. Perhaps you require an OUTER JOIN.

I recommend that you read up on sql JOINs, as these are fundamental to relational databases.