Professional Web Applications Themes

I know this involves LEFT JOINs somehow - MySQL

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...

  1. #1

    Default I know this involves LEFT JOINs somehow

    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 Guest

  2. #2

    Default Re: I know this involves LEFT JOINs somehow

    com wrote: 
    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
    Adam Guest

  3. #3

    Default Re: I know this involves LEFT JOINs somehow

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

    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`='...';
    subtenante Guest

Similar Threads

  1. Does CFSQL Not Do Left Joins?
    By PeytonT in forum Coldfusion Database Access
    Replies: 4
    Last Post: March 4th, 08:02 PM
  2. Beginners question on LEFT JOINS
    By John Meyer in forum MySQL
    Replies: 3
    Last Post: December 25th, 01:33 AM
  3. Query problem - multiple left joins??
    By RuBot in forum Coldfusion Database Access
    Replies: 1
    Last Post: October 28th, 07:48 PM
  4. [PHP] Are left joins more efficient?
    By Pete James in forum PHP Development
    Replies: 1
    Last Post: July 25th, 05:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139