mysql: join 2 different tables into 1

[Xeel]

Sup ;)

Ok, this is an update. I've got my question answered. But let's do a quiz from this.

Let's say you have two similar tables. In one we save text keys, the other is for saving text keys we can't use. Id fields are just to access our key easily in the future. All text keys are unique.

table#1 (registered keys):
+-------+-------------+
| idKey | varchar(12) |
+-------+-------------+
| txKey | varchar(15) |
+-------+-------------+

table#2 (blacklisted keys):
+-------+-------------+
| idKey | varchar(12) |
+-------+-------------+
| txKey | varchar(15) |
+-------+-------------+

So we want to create a new text key. And before we can save it we need to know if there is any key with the same name that already exists (to avoid duplication), and if it's one of the blacklisted keys.

Question: How it's done in one query?

[Leon.Kennedy]

Answer: Use a union query. Pull all of the Registered txKeys that match the newly created key, and union that with pulling all of the Blacklisted txKeys that match the new created key. Make sure that each portion of the union has a created field called Match that populates with either Registered or Blacklisted when/if any records are returned (you should only get 1 record returned maximum). Then in your code (outside of the query) do a check against the number of records in the returned recordset. If < 1, create the key. If = 1, determine if registered or blacklisted and display appropriate message. Yes?

[Xeel]

The query please =)

[Leon.Kennedy]

strSelect="SELECT Count(table1.txKey) AS Count,'Registered' AS Match
FROM table1 GROUP BY 'Registered' WHERE table1.txKey = '" & strNewKey
& "' UNION ALL SELECT Count(table2.tkKey) AS Count,'Blacklisted' AS Match
FROM table2 GROUP BY 'Blacklisted' WHERE table2.txKey = '" & strNewKey
& "'"


This should work, for the most part. Please note that you need to eliminate the line breaks in the code above for this to work. The line breaks were added to make it more readable. I don't have your variable names, or the table names. If this is incomplete or not functioning, let me know and I'll take another gander at it.

[Xeel]

I've tried to run the code, made several changes, and with no result. Could you use more canonical SQL? I'm using MySQL for the task for ex.

Ok, I've corrected it. Yep, nice solution, pretty elegant. Not only we know if the new candidate already exists, we also know where. =)

SELECT COUNT(test1.txKey) AS 'Count','Registered' AS 'Match'
FROM test1 WHERE test1.txKey = 'keyName' GROUP BY 'Registered'

UNION ALL

SELECT COUNT(test2.txKey) AS 'Count','Blacklisted' AS 'Match'
FROM test2 WHERE test2.txKey = 'keyName' GROUP BY 'Blacklisted'

My solution was much uglier. It just gave me any resultset in the case of keyName existance in any of the tables (which served me anyways):

SELECT A.txKey, B.txKey

FROM

(SELECT txKey FROM test1) AS A,
(SELECT txKey FROM test2) AS B

WHERE A.txKey = 'keyName' OR B.txKey = 'keyName'