Odd query from multiple tables (brainbuster)

[xhenxhe]

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?

Thanks in advance!

[Bill Karwin]

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.

[xhenxhe]

Thanks Bill. I guess I should be more specific so the answer can be
more helpful to me :) So I'll be very specific...

I have a freelance site at http://www.nerdlance.com. Each project has a
"project clarification board" (PCB). Whenever there is a new post on
the PCB, I would like to send an email to everyone that has 1) posted a
bid on the project and 2) Anyone that has posted to the PCB. So it is
possible that someone can post to the PCB withought placing a bid.
Obviously, I don't want the message to be sent to the same person
twice, so I can't have duplicates. Also, I wouldn't want the email to
go to the person that just posted to the PCB, or the project owner, as
his/her message will be slightly different.

The data I'm retrieving will come from two tables. Table 1 is "bids"
and table 2 is "projectclarify". Among other fields, "bids" has the
following: bid_id, user_id, & project_id. "projectclarify" has the
these fields: clarify_id, project_id, from_id (from_id is the user_id
that posted the project).

So, is it possible to join these tables like you mentioned if the
user_id field has a different name in the second table?

An example to look at would be http://www.nerdlance.com/p67, which has
a few bids and a couple of PCB posts, though no examples of a PCB post
without a bid.

Like I mentioned, I can figure out ways to do this with two queries,
then manage the data with PHP, but I'd like to know if its possible to
get all the info I need with just one query.

Thanks!
-Dustin

[xhenxhe]

Oh, I think I figured it out. I just join my two queries with UNION :)

select b.user_id
from bids b
where b.project_id=95 -- current project
union
select pc.from_id as user_id
from projectclarify pc
where pc.project_id=95 -- current project
and pc.from_id != 269 -- project owner id
and pc.from_id != 2 -- poster_id