xhenxhe wrote:
> I'm wondering if something like this can be done in mysql...
>
> I have two tables I want to pull data from. One table will contain a
> unique user_id which I could pull out like:
>
> select user_id from table;
>
> that's basically all I would need from that table. The second table
> contains a user id field, but can have several duplicate user_id's in
> the table. Essentially I would need:
>
> select distinct user_id from table2 where user_id != owner_id;
>
> Then between the results of the two queries, I would like to remove any
> duplicate user_id's. I can do this easy enough in PHP, but I would
> prefer to increase my mysql skills. Is it possible to somehow combine
> these two queries into one and pull out the uniques using distinct?

Sure, but is it just a list of distinct user_id's what you really want?
Or do you want the full row corresponding to one of the occurrances of
a given user_id in table2?

In other words, you can do this:

SELECT DISTINCT table1.user_id
FROM table1 JOIN table2 USING (user_id)
WHERE table2.user_id != table2.owner_id

Instead of using DISTINCT, you can also filter for those entries that
only occur once:

SELECT table1.user_id
FROM table1 JOIN table2 USING (user_id)
WHERE table2.user_id != table2.owner_id
GROUP BY table1.user_id
HAVING COUNT(*) = 1

(or HAVING COUNT(*) > 1 if you want to return the user_id's for which
there are duplicates)

What if there are user_id values in table2 that don't occur in table1,
or vice versa? You'd need to use an outer join to make sure you
retrieve these.

If you want to get other attributes from table2 besides just the
user_id, the query gets more complex.

Regards,
Bill K.