www.webdeveloper.com
Results 1 to 7 of 7

Thread: MAX function

  1. #1
    Join Date
    Nov 2006
    Posts
    229

    MAX function

    Gang,

    I am trying to query two tables using the Max Function and Inner Join.

    The data exists in the database but I keep getting a "NO ROWS RETURNED"
    when I run the statement.

    Below is the query I am struggling with. Thanks.

    Code:
    SELECT * FROM FishAngler as F
    INNER JOIN BigFish on F.FishId = BigFish.FishId
    Where F.Country = 'South Africa'
    and BigFish in (select top 1 max(BigFish)
       from BigFish
      Where BigFish.BigFish=(Select Max(BigFish) from BigFish))

  2. #2
    Join Date
    Aug 2009
    Posts
    593
    Besides the query being a little confusing, I see that you are using the BigFish table in places without using any field, like Max(BigFish.Id) from BigFish?

  3. #3
    Join Date
    Nov 2006
    Posts
    229
    I cleaned up the query a little bit ...actually BigFish is a Column name in the BigFish table as well. I ran it again but, still same results.

    I am trying to get the Max(BIGFISH) value from the BIG FISH TABLE and also get all of the persons' information from the FISHANGLER TABLE.

    THIS SHOULD DISPLAY ONE ROW OF DATA.

    SELECT * FROM FishAngler as F
    INNER JOIN BigFish on F.FishId = BigFish.FishId
    Where F.Country = 'South Africa'
    and BigFish.BigFish in (select top 1 max(BigFish.BigFish)
    from BigFish)

  4. #4
    Join Date
    Nov 2006
    Posts
    229
    Ok...the reason why I am not getting a row of data is, the BIGFISH TABLE is not a child table to the FISHANGLER table.

    Can I still extract the data for one person by creating an inner join between 3 tables?

    BIGFISH TABLE + FISHTABLE + FISHANGLER TABLE

  5. #5
    Join Date
    Nov 2006
    Posts
    229
    Actually, the FishId is the foriegn key in the BIGFISH table and the Primary key is in the FISHANGLER table.

    Here is another query:

    Select * from FISHANGLER
    Inner Join BigFish on FishAngler.FishId = BigFish.FishId
    Where BigFish.BigFish = (Select Max(BigFish) from BigFish Where FishAngler.Country = 'South Africa')

    Here is another query:

    Select TOP 1 MAX(BIGFISH) from BIGFISH
    Inner Join FISHANGLER on BIGFISH.FishId = FISHANGLER.FishId
    Where FISHANGLER.FishId = (Select * from FISHANGLER Where Country = 'United States' and BigFish.FishId = FishAngler.FishId)

  6. #6
    Join Date
    Nov 2006
    Posts
    229
    PROBLEM SOLVED.

    Used the Group By, ORDER BY MAX(BIGFISH.BIGFISH) and the INER JOIN. Long query.

  7. #7
    Join Date
    Aug 2009
    Posts
    593
    Just so it's easier to read in the future you should give all your tables aliase names:

    Select TOP 1 MAX(BF.BIGFISH) from BIGFISH BF
    Inner Join FISHANGLER FA on BF.FishId = FA.FishId
    Where FA.FishId = (Select * from FISHANGLER FA Where FA.Country = 'United States' and BF.FishId = FA.FishId)

    Just so you can be specific where your values are coming from, otherwise you could be pulling a BigFish from the wong pond?

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles