Hi,

I have two tables

SUBSCRIPTION_TYPES
-----------------------------------
ID INTEGER
NAME VARCHAR(255)

and

SUBSCRIPTIONS
-------------------------
iD INTEGER
SUBSCRIPTION_ID INTEGER
USER_ID INTEGER

If a row appears in the SUBSCRIPTIONS table, it means a user has a
subscription to a particular item. If no appears for that user, he
has no subscription. I'm trying to write a query that, for a given
user, will return 1 or 0 based on whether or not the user has a
subscription. How would I do this?

I'm using MySQL 5.0.

Thanks, - Dave

laredotornado@zipmail.com wrote:
> Hi,
>
> I have two tables
>
> SUBSCRIPTION_TYPES
> -----------------------------------
> ID INTEGER
> NAME VARCHAR(255)
>
> and
>
> SUBSCRIPTIONS
> -------------------------
> iD INTEGER
> SUBSCRIPTION_ID INTEGER
> USER_ID INTEGER
>
> If a row appears in the SUBSCRIPTIONS table, it means a user has a
> subscription to a particular item. If no appears for that user, he
> has no subscription. I'm trying to write a query that, for a given
> user, will return 1 or 0 based on whether or not the user has a
> subscription. How would I do this?
>
> I'm using MySQL 5.0.
>
> Thanks, - Dave
>
If yo have a user table, an I assume you do, you could us something like
this:
select u.name,if(s.id is null,0,1) from user u left join subscriptions s
on u.id = s.user_id;

Adam

On Tue, 10 Jul 2007 14:45:44 -0700, "laredotornado@zipmail.com"
<laredotornado@zipmail.com> wrote:

>Hi,
>
>I have two tables
>
>SUBSCRIPTION_TYPES
>-----------------------------------
>ID INTEGER
>NAME VARCHAR(255)
>
>and
>
>SUBSCRIPTIONS
>-------------------------
>iD INTEGER
>SUBSCRIPTION_ID INTEGER
>USER_ID INTEGER
>
>If a row appears in the SUBSCRIPTIONS table, it means a user has a
>subscription to a particular item. If no appears for that user, he
>has no subscription. I'm trying to write a query that, for a given
>user, will return 1 or 0 based on whether or not the user has a
>subscription. How would I do this?
>
>I'm using MySQL 5.0.
>
>Thanks, - Dave

It seems you have all information needed in SUBSCRIPTIONS, no need for
a join here...

Something like :

SELECT IF(count(*)>0, 1, 0)
FROM `subscriptions`
WHERE `user_id`='...';