1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

many to many databse

Discussion in 'C#' started by lovelyhelp, Apr 29, 2006.

  1. #1
    I am using sql 2000. I have a scenario is 1 user can buy many or 0 'package'.
    and 1 'package' can have 0 or many users. If the user didnt buy the 'package' then he/she will not allow to use the package.

    I have a headache in my sqlcommand
    I am using user.identity.name to get my username

    my database is
    table t_package: p_id, p_name
    table t_user:u_id,u_name
    table t_userpackage:p_id, u_id

    how shouldmy database look like.

    the function is, when user click on the list. sqlcommand will check whether have the specific user pay for the specific package. if yes then program will continue if not error msg will shown.
    if i use dataList control to show my item.

    How is all my program will look like?
    lovelyhelp, Apr 29, 2006 IP
  2. jfilley

    jfilley Peon

    Likes Received:
    Best Answers:
    Trophy Points:
    Not really sure what your specific question it
    How is all my program will look like?

    You database structure looks good...
    your sql statement to check if the user has payed for the package would be

    select up.p_id
    from t_user u
    left outer join t_userpackage up on u.u_id = up.u_id
    where u.u_id = @UserName

    For best performance and security, create it as a stored proc. Pass in @UserName as a parameter and use and ExecuteScaler on the command object. This will return a single value. NULL if that package doesn't exist for that user OR the packageID if it does exist.
    jfilley, May 2, 2006 IP
  3. DanInManchester

    DanInManchester Active Member

    Likes Received:
    Best Answers:
    Trophy Points:
    I would have thought you want an inner join?
    Simply inner join the tables and do a count of the package ID for where the username equals the current user and the package is of the one specified.

    Count will return 0 if the user does not have the package

    SELECT Count(P_id) as PackageCount
    FROM t_user INNER JOIN
    t_userpackage ON t_user.u_id = t_userpackage.u_id
    WHERE t_user.u_name LIKE @Username
    AND t_userpackage.p_id = @PackageID

    The example assumes you are using the Package ID rather than the package name. If you wanted to use the package name simply inner join the package table and make the parameter for the name instead of the id.
    DanInManchester, May 10, 2006 IP