Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    18

    Unanswered: Two different pieces of data from same table

    The tables:
    TBLPROVIDERS holds the info on game providers
    TBLGAMES holds information on the main game
    TBLGAMESFORMAT holds information on the various formats the games exist in.

    I have a simple view to list the PROVIDERNAME and TITLE of all our current downloadable games (which are FORMATID = 1). What I need to add is an additional column that shows the value 'web' if that GAMEID also has an entry in the TBLGAMESFORMAT table with a FORMATID = 2.

    SELECT dbo.TBLPROVIDERS.PROVIDERNAME, dbo.TBLGAMES.TITLE
    FROM dbo.TBLGAMESFORMAT INNER JOIN
    dbo.TBLGAMES ON dbo.TBLGAMESFORMAT.GAMEID = dbo.TBLGAMES.GAMEID INNER JOIN
    dbo.TBLPROVIDERS ON dbo.TBLGAMESFORMAT.GFDEVELOPER = dbo.TBLPROVIDERS.PROVIDERID
    WHERE (dbo.TBLGAMESFORMAT.FORMATID = 1)

    Is there anyone out there that has a simple solution?

    Thanks,
    Doug

  2. #2
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    Quickly this might work

    SELECT dbo.TBLPROVIDERS.PROVIDERNAME, dbo.TBLGAMES.TITLE
    max(case when dbo.TBLGAMESFORMAT.FORMATID = 2 then 'WEB' else null end) as Othercolumn
    FROM dbo.TBLGAMESFORMAT INNER JOIN
    dbo.TBLGAMES ON dbo.TBLGAMESFORMAT.GAMEID = dbo.TBLGAMES.GAMEID INNER JOIN
    dbo.TBLPROVIDERS ON dbo.TBLGAMESFORMAT.GFDEVELOPER = dbo.TBLPROVIDERS.PROVIDERID
    WHERE (dbo.TBLGAMESFORMAT.FORMATID in (1,2))
    group by dbo.TBLPROVIDERS.PROVIDERNAME, dbo.TBLGAMES.TITLE

    this would work if any format 1 has any format 2 and if there is only a format 2

    if you want any format 1 that also has a format 2

    add a having clause.

    You could also use a sub query if so inclined.

Posting Permissions

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