Jump to content
Larry Ullman's Book Forums

Search Two Tables For One Value


Recommended Posts

I feel like my question is really simple and I'm being really dense about it, but nevertheless, here goes:

 

I have two types of users on a site. Each has their own table in the DB, and the columns in the tables overlap some, but not 100%.

When a new user registers an account (regardless of which type of user they are), I want to search both tables to see if the email address they have entered is already registered in either of the two tables.

 

However, when I run the following query, I always get 0 results, even though I know the email address exists in one of the two tables:

 

SELECT * FROM table1 AS t1, table2 AS t2 WHERE t1.email = 'email-address' OR t2.email = 'email-address';

 

So my question is, is it possible to check two tables (with different structures, but both with an email column) in one query for a single entered email address?

Thank you.

Link to comment
Share on other sites

The problem is you're doing an INNER JOIN here. You could do an OUTER JOIN, but that'd only work in one direction.

 

It'd probably be easiest to use a VIEW that just lists the email addresses used in the two tables. Or change your database structure.

Link to comment
Share on other sites

Well, changing the structure of the DB/tables is not a realistic option, as the tables have unique structures (and that's the way they need to be).

Also, I thought about it for a while, and while an OUTER JOIN may be possible, no viable solution immediately came to me.

 

Luckily, I remembered the UNION keyword, which immediately solved my problem. The following query worked wonders:

SELECT 1 FROM table1 WHERE email = 'email-address'
UNION
SELECT 1 FROM table2 WHERE email = 'email-address';

If you have a solution using an outer join, Larry, I'd love to hear it.

Thank you.

Link to comment
Share on other sites

Using an outer join will turn out to be tricky. I thought it was viable at first too, but could not really find a way to make it work. The problem is that it only works one way, as Larry stated. I wasted 10 minutes before I picked up on that. There's a lesson to be learnt there. Haha.

 

You could probably make it work with outer joins, but I would guess you would need controll structures for that to work. I can't really see the point in putting so much logic into a query though. Your solution seems very reasonably to me, Jon.

Link to comment
Share on other sites

Hi,

 

I'm on the MySQL chapters now and I'm getting a bit confused here. :blink:

I understand the union solution and i can see how it's the best due to it's efficiency.

 

But just wondered why an inner join like this would not render results for you:

SELECT t1.column, t2.column FROM table1 as t1
INNER JOIN table2 as t2 
ON t1.email = t2.email
WHERE t1.email = "email-address"
;


?

 

All the Best,

Stephen

Link to comment
Share on other sites

INNER JOINS only return records when there's a match made across one or more rows in both tables. In this case, the email address could be present in one of the tables, but never both, and possibly neither. An INNER JOIN will never return a record, then. 

Link to comment
Share on other sites

I thought HartleySan meant if a user had already registered an account in both tables with the same email address -- so that there would be a match across both tables for email address .

But I misunderstood, It makes more sense HartleySan wanted to see, before the new account registered, if this email address already existed in either table.

So as this is the case, I can see now why the INNER JOIN won't work here.

 

Why can't the same email address be used in both tables. Is it because UNIQUE works across an entire database and not just an individual table?

 

But to confirm that I understand what I was trying to achieve (erroneously), let's say the same email address was already used to make an account in both tables[if that's possible in MySQL terms], would this INNER JOIN work then?

Link to comment
Share on other sites

I don't want the same email address in both tables because the email address is the thing that makes users unique on the site, and it's one of two pieces of information that users use to log in with.

 

Furthermore, even though there are two different types of users, they all log in through the same interface, so each email address registered in the DB needs to be unique across both tables.

With that said, (as far as I know) MySQL has no way of enforcing a unique value across two tables, so I need to handle this logic myself through PHP, thus my original question.

 

To answer your last question, yes, if the same email address were registered in both tables, then an inner join could be used to join the two tables on that like value.

Link to comment
Share on other sites

 Share

×
×
  • Create New...