CODEWITHSUNDEEP
mysqlsql
Josh Foskett
Josh Foskett

Reputation: 4121

Check if one and/or the other table contains a specific value in same query?

I'm attempting to check if either of my two tables contains a specific value in the content_key column. One table is for public content (content), and the other is for submissions (content_submissions). When a user submits content (YouTube video id's, etc) to us, I want to check if it has already been submitted to us, whether it be in the public table or the private (submissions) table. This way we don't end up having accepting duplicates of the same content during the submission process.

The two tables would look like this.

content

*----*-------------*
| id | content_key |
*----*-------------*    
| 1  | sOWewQO3hpg |
*----*-------------*
| 2  | e2CJgQjH5lE |
*----*-------------*

content_submissions

*----*-------------*
| id | content_key |
*----*-------------*    
| 1  | BOtcpPVNvhA |
*----*-------------*

So when the user submits the key e2CJgQjH5lE or BOtcpPVNvhA (at different times), I want the query to tell me that the key is already in the database.

The reason I don't simply have an active column in the content table instead of having two table is because it will significantly increase the id each time content is not accepted, making the urls a bit random, and not sequential.

Upvotes: 0

Views: 3211

Answers (4)

John Woo
John Woo

Reputation: 263913

you can combined the rows of both table using UNION

SELECT COUNT(content_key)
FROM
    (
        SELECT content_key
        FROM content
        UNION
        SELECT content_key
        FROM content_submissions
    ) a
WHERE content_key = 'valHere'

It will simply return 0 if the value your are looking for doesn't exists otherwise the total number of records that matches will be shown.

SQLFiddle Demo

Hope this helps.

enter image description here

Upvotes: 4

LaGrandMere
LaGrandMere

Reputation: 10379

select id from content
where content.content_key="myKey"
UNION
select id from content_submissions
content_submissions.content_key="myKey"

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166606

If you were to use

SELECT *
FROM content
WHERE content_key = value
UNION
SELECT *
FROM content_submissions
WHERE content_key = value

that should do it.

From 13.2.8.4. UNION Syntax

UNION is used to combine the result from multiple SELECT statements into a single result set.

The default behavior for UNION is that duplicate rows are removed from the result.

Upvotes: 0

podiluska
podiluska

Reputation: 51514

SELECT COUNT(*)
FROM
    (Select content_key from content union select content_key from content_submissions) v
WHERE content_key = 'new key'

Upvotes: 1

Related Questions