Combing two simple select statements. There must be a way?

Hi

I have two select statements that work correctly. They are as
follows:

SELECT DISTINCT user.* FROM user_tab AS user INNER JOIN user_pref_tab
AS pref ON user.id=pref.userid and pref.questionid = '20'

SELECT DISTINCT user.* FROM user_tab AS user LEFT JOIN user_pref_tab
AS pref ON user.id=pref.userid WHERE pref.userid IS NULL

The first simply returns all rows that have the same userid in the
user and preferences table that also have the column "questionid" as a
value of 20.

The second just returns every row that doesn't have a matching userid
in both tables.

The problem is I desperately need to combine these two statements in
to ONE. It seems like it should be possible but I have tried about a
100 different combination of joins and nested selects and can't get the
result I want.

Can anyone help me here? Thanks - Ukrbend

<ukr_bend@yahoo.com> wrote in message
news:1143157218.419817.99930@e56g2000cwe.googlegro ups.com...
> SELECT DISTINCT user.* FROM user_tab AS user INNER JOIN user_pref_tab
> AS pref ON user.id=pref.userid and pref.questionid = '20'
>
> SELECT DISTINCT user.* FROM user_tab AS user LEFT JOIN user_pref_tab
> AS pref ON user.id=pref.userid WHERE pref.userid IS NULL
>
> [help me combine the two queries above]

Does this do what you want?

SELECT u.*
FROM user_tab AS u LEFT OUTER JOIN user_pref_tab AS p
ON u.id = p.userid AND p.questionid = '20'

Regards,
Bill K.

Thanks for the help Bill. Unfortunately that statement returns all the
rows, even the ones that have a matching userid between the two tables.
But again, thanks for the post.

<ukr_bend@yahoo.com> wrote in message
news:1143157218.419817.99930@e56g2000cwe.googlegro ups.com...
> Hi
>
> I have two select statements that work correctly. They are as
> follows:
>
> SELECT DISTINCT user.* FROM user_tab AS user INNER JOIN user_pref_tab
> AS pref ON user.id=pref.userid and pref.questionid = '20'
>
> SELECT DISTINCT user.* FROM user_tab AS user LEFT JOIN user_pref_tab
> AS pref ON user.id=pref.userid WHERE pref.userid IS NULL
>
> The first simply returns all rows that have the same userid in the
> user and preferences table that also have the column "questionid" as a
> value of 20.
>
> The second just returns every row that doesn't have a matching userid
> in both tables.
>
> The problem is I desperately need to combine these two statements in
> to ONE. It seems like it should be possible but I have tried about a
> 100 different combination of joins and nested selects and can't get the
> result I want.

And what result would that be?


--
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

<ukr_bend@yahoo.com> wrote in message
news:1143190349.799462.135730@g10g2000cwb.googlegr oups.com...
> Thanks for the help Bill. Unfortunately that statement returns all the
> rows, even the ones that have a matching userid between the two tables.
> But again, thanks for the post.

Can you describe in more detail -- or show an example -- of what the desired
results would be of combining the two queries into one? There are multiple
ways it can be done. We need some more clues in order to suggest the right
solution.

Here's another possibility, simply jamming the two queries together in a
UNION:

SELECT user1.* FROM user_tab AS user1 INNER JOIN user_pref_tab
AS pref1 ON user1.id=pref1.userid and pref1.questionid = '20'
UNION
SELECT user2.* FROM user_tab AS user2 LEFT JOIN user_pref_tab
AS pref2 ON user2.id=pref2.userid WHERE pref2.userid IS NULL

Also it would help if you told us what brand and version of RDBMS you're
using. The solution may be different depending on which SQL features are
supported by the RDBMS.

Regards,
Bill K.

"Bill Karwin" <bill@karwin.com> wrote in message
news:e01b920dp9@enews2.newsguy.com...
> Also it would help if you told us what brand and version of RDBMS you're
> using. The solution may be different depending on which SQL features are
> supported by the RDBMS.

Woops! Of course, you must be using MySQL since this is the MySQL
newsgroup. I thought I was replying in the comp.databases newsgroup and I
posted too quickly. My mistake.

Anyway, it would still be helpful to know which version of MySQL you're
using.

Regards,
Bill K.