MAX function

[ASPSQLVB]

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.

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

[thraddash]

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?

[ASPSQLVB]

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)

[ASPSQLVB]

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

[ASPSQLVB]

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)

[ASPSQLVB]

PROBLEM SOLVED.

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

[thraddash]

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?