"Eamon Straughn" <blackmanvan***@aol.com> wrote in message
news:e%23g6N7JsFHA.2540@TK2MSFTNGP09.phx.gbl...
> SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
> AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
> Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years, (12 -
> (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months FROM
> AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND AppFnd.apId
> =
> Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
> AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
> (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
>
> The query above is the query i am uisng to retrieve matching results from
> 3
> tables, that returns all the rows pass through the "@UName" parameter. But
> I
> have a problem, If the user makes more than one request then there is more
> than one request from that user. I have tried using the TOP(1) but it only
> returns the top row for the entire table whereas all i want is the top row
> from each friend.
>
> example of output currently.....
> friend                    rated you                from                age
> status
> sean                       a gud friend            uk
> 18
> false
> sean                       a close friend          uk
> 18
> false
> sean                       a penpal                 uk
> 18
> false
> joanne                   a gud friend            uk                    18
> false
> joanne                   a gud friend            uk                    18
> false
> joanne                   a gud friend            uk                    18
> false
>
> the user added to many request for the user to approve
>
> example of output desired......
> friend                    rated you                from                age
> status
> sean                       a gud friend            uk
> 18
> false
> joanne                    a gud friend            uk                    18
> false
> one row per user
>
>

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:OLCej0MsFHA.2212@TK2MSFTNGP15.phx.gbl...
> Hi
>
> http://www.aspfaq.com/etiquette.asp?id=5006 shows the most useful way to
> post DDL and sample data (as insert statements). You would have to equate
> the rated you value to a ranking e.g.
>
> Rating
>
> RatedYou Value
> a gud friend    1
> a close friend    2
> a penpal         3
>
> You can then select the minimum (in this example) rating value for the
> corresponding rating from each friend.
>
> John
>
> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
> news:e%23g6N7JsFHA.2540@TK2MSFTNGP09.phx.gbl...
> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years, (12 -
> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months FROM
> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
AppFnd.apId
> > =
> > Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
> >
> > The query above is the query i am uisng to retrieve matching results
from
> > 3
> > tables, that returns all the rows pass through the "@UName" parameter.
But
> > I
> > have a problem, If the user makes more than one request then there is
more
> > than one request from that user. I have tried using the TOP(1) but it
only
> > returns the top row for the entire table whereas all i want is the top
row
> > from each friend.
> >
> > example of output currently.....
> > friend                    rated you                from
age
> > status
> > sean                       a gud friend            uk
> > 18
> > false
> > sean                       a close friend          uk
> > 18
> > false
> > sean                       a penpal                 uk
> > 18
> > false
> > joanne                   a gud friend            uk
18
> > false
> > joanne                   a gud friend            uk
18
> > false
> > joanne                   a gud friend            uk
18
> > false
> >
> > the user added to many request for the user to approve
> >
> > example of output desired......
> > friend                    rated you                from
age
> > status
> > sean                       a gud friend            uk
> > 18
> > false
> > joanne                    a gud friend            uk
18
> > false
> > one row per user
> >
> >
>
>

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:OLCej0MsFHA.2212@TK2MSFTNGP15.phx.gbl...
> Hi
>
> http://www.aspfaq.com/etiquette.asp?id=5006 shows the most useful way to
> post DDL and sample data (as insert statements). You would have to equate
> the rated you value to a ranking e.g.
>
> Rating
>
> RatedYou Value
> a gud friend    1
> a close friend    2
> a penpal         3
>
> You can then select the minimum (in this example) rating value for the
> corresponding rating from each friend.
>
> John
>
> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
> news:e%23g6N7JsFHA.2540@TK2MSFTNGP09.phx.gbl...
> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years, (12 -
> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months FROM
> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
AppFnd.apId
> > =
> > Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
> >
> > The query above is the query i am uisng to retrieve matching results
from
> > 3
> > tables, that returns all the rows pass through the "@UName" parameter.
But
> > I
> > have a problem, If the user makes more than one request then there is
more
> > than one request from that user. I have tried using the TOP(1) but it
only
> > returns the top row for the entire table whereas all i want is the top
row
> > from each friend.
> >
> > example of output currently.....
> > friend                    rated you                from
age
> > status
> > sean                       a gud friend            uk
> > 18
> > false
> > sean                       a close friend          uk
> > 18
> > false
> > sean                       a penpal                 uk
> > 18
> > false
> > joanne                   a gud friend            uk
18
> > false
> > joanne                   a gud friend            uk
18
> > false
> > joanne                   a gud friend            uk
18
> > false
> >
> > the user added to many request for the user to approve
> >
> > example of output desired......
> > friend                    rated you                from
age
> > status
> > sean                       a gud friend            uk
> > 18
> > false
> > joanne                    a gud friend            uk
18
> > false
> > one row per user
> >
> >
>
>

"Eamon Straughn" <blackmanvan***@aol.com> wrote in message
news:OePY2vSsFHA.2064@TK2MSFTNGP09.phx.gbl...
> correction forgot the 3rd table
> DDL....
> <-------------------------------THE FIRST
> TABLE----------------------------------->
> USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
>
> CREATE TABLE [dbo].[Friends](
>
> [frId] [int] IDENTITY(1,1) NOT NULL,
>
> [Message] [nvarchar](250) COLLATE Latin1_General_CI_AI NULL,
>
> [Friend] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
>
> [Rating] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
>
> [apId] [int] NULL,
>
> [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
>
> CONSTRAINT [PK_Friends] PRIMARY KEY CLUSTERED
>
> <-------------------------------THE SECOND
> TABLE----------------------------------->
>
> USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
>
> CREATE TABLE [dbo].[AppFnd](
>
> [afId] [int] IDENTITY(1,1) NOT NULL,
>
> [approve] [bit] NULL,
>
> [Friend] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
>
> [apId] [int] NULL,
>
> [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
>
> CONSTRAINT [PK_AppFnd] PRIMARY KEY CLUSTERED
>
> <-------------------------------THE THIRD
> TABLE-----------------------------------> [NOT FULL TABLE]
>
> USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
>
> CREATE TABLE [dbo].[Basic](
>
> [bId] [int] IDENTITY(1,1) NOT NULL,
>
> [Dob [datetime] NULL,
>
> [Country] [nvarchar](250) COLLATE Latin1_General_CI_AI NULL,
>
> [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
>
> CONSTRAINT [PK_Basic] PRIMARY KEY CLUSTERED
>
> <----------------------------THE SELECT
> QUERY---------------------------------->
>
> SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
> AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
> Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years, (12 -
> (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months FROM
> AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND AppFnd.apId
> = Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
> AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
> (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
>
> <--------------------FILL THE TABLES---------------------->
> INSERT the following into the AppFnd Table
> this table is the table only the logged in user will see. @Uname and will
> approve the friend
>
> Approve      Friend         apId                  UName
> false             Eamon        356668           Louise
> false             John            8955566           Patrick
> true              Monique       788855            Ash
> false             Eamon        7888999            Louise
>
> INSERT the following into the Friends Table
> this table is the table only the friend will see after they have been
> approved from the person in the Uname column in the AppFnd table
>
> Message      Friend           Rating        apId                  UName
> i love her      Louise     A best m8       356668            Eamon
> my m8          Patrick    A pen pal        8955566           John
> my wife         Ash        A close m8      788855             Monique
> could be      Louise     A best m8       7888999            Eamon
>
> INSERT the following into the Basic Table
> this table is a root table of the database which has all the users
> personal
> details
>
> Country      Dob          UName
> UK      21/11/1984     Eamon
> UK      03/01/1980     John
> USA    16/02/1986     Ash
> France  29/11/1985    Patrick
> UK      20/07/1984     Ash
> UK      13/12/1982     Louise
>
> <--------------------DESIRED RESULTS---------------------->
> See how there are two results with the same UName in the AppFnd how do i
> remove the duplicates where the select query doesn't retrieve any two
> friend
> and uname from both tables and returns only unique top rows for each user.
>
>
>
> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:OLCej0MsFHA.2212@TK2MSFTNGP15.phx.gbl...
>> Hi
>>
>> http://www.aspfaq.com/etiquette.asp?id=5006 shows the most useful way to
>> post DDL and sample data (as insert statements). You would have to equate
>> the rated you value to a ranking e.g.
>>
>> Rating
>>
>> RatedYou Value
>> a gud friend    1
>> a close friend    2
>> a penpal         3
>>
>> You can then select the minimum (in this example) rating value for the
>> corresponding rating from each friend.
>>
>> John
>>
>> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
>> news:e%23g6N7JsFHA.2540@TK2MSFTNGP09.phx.gbl...
>> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
>> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
>> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years, (12 -
>> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months FROM
>> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
> AppFnd.apId
>> > =
>> > Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
>> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
>> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
>> >
>> > The query above is the query i am uisng to retrieve matching results
> from
>> > 3
>> > tables, that returns all the rows pass through the "@UName" parameter.
> But
>> > I
>> > have a problem, If the user makes more than one request then there is
> more
>> > than one request from that user. I have tried using the TOP(1) but it
> only
>> > returns the top row for the entire table whereas all i want is the top
> row
>> > from each friend.
>> >
>> > example of output currently.....
>> > friend                    rated you                from
> age
>> > status
>> > sean                       a gud friend            uk
>> > 18
>> > false
>> > sean                       a close friend          uk
>> > 18
>> > false
>> > sean                       a penpal                 uk
>> > 18
>> > false
>> > joanne                   a gud friend            uk
> 18
>> > false
>> > joanne                   a gud friend            uk
> 18
>> > false
>> > joanne                   a gud friend            uk
> 18
>> > false
>> >
>> > the user added to many request for the user to approve
>> >
>> > example of output desired......
>> > friend                    rated you                from
> age
>> > status
>> > sean                       a gud friend            uk
>> > 18
>> > false
>> > joanne                    a gud friend            uk
> 18
>> > false
>> > one row per user
>> >
>> >
>>
>>
>
>

> > <-------------------------------THE FIRST
> > TABLE----------------------------------->
> > USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
> >
> > CREATE TABLE [dbo].[Friends](
> >
> > [frId] [int] IDENTITY(1,1) NOT NULL,
> >
> > [Message] [nvarchar](250) COLLATE Latin1_General_CI_AI NULL,
> >
> > [Friend] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
> >
> > [Rating] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
> >
> > [apId] [int] NULL,
> >
> > [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> >
> > CONSTRAINT [PK_Friends] PRIMARY KEY CLUSTERED
> >
> > <-------------------------------THE SECOND
> > TABLE----------------------------------->
> >
> > USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
> >
> > CREATE TABLE [dbo].[AppFnd](
> >
> > [afId] [int] IDENTITY(1,1) NOT NULL,
> >
> > [approve] [bit] NULL,
> >
> > [Friend] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
> >
> > [apId] [int] NULL,
> >
> > [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> >
> > CONSTRAINT [PK_AppFnd] PRIMARY KEY CLUSTERED
> >
> > <-------------------------------THE THIRD
> > TABLE-----------------------------------> [NOT FULL TABLE]
> >
> > USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
> >
> > CREATE TABLE [dbo].[Basic](
> >
> > [bId] [int] IDENTITY(1,1) NOT NULL,
> >
> > [Dob [datetime] NULL,
> >
> > [Country] [nvarchar](250) COLLATE Latin1_General_CI_AI NULL,
> >
> > [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> >
> > CONSTRAINT [PK_Basic] PRIMARY KEY CLUSTERED
> >
> > <----------------------------THE SELECT
> > QUERY---------------------------------->
> >
> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years, (12 -
> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months FROM
> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
AppFnd.apId
> > = Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
> >
> > <--------------------FILL THE TABLES---------------------->
> > INSERT the following into the AppFnd Table
> > this table is the table only the logged in user will see. @Uname and
will
> > approve the friend
> >
> > Approve      Friend         apId                  UName
> > false             Eamon        356668           Louise
> > false             John            8955566           Patrick
> > true              Monique       788855            Ash
> > false             Eamon        7888999            Louise
> >
> > INSERT the following into the Friends Table
> > this table is the table only the friend will see after they have been
> > approved from the person in the Uname column in the AppFnd table
> >
> > Message      Friend           Rating        apId                  UName
> > i love her      Louise     A best m8       356668            Eamon
> > my m8          Patrick    A pen pal        8955566           John
> > my wife         Ash        A close m8      788855             Monique
> > could be      Louise     A best m8       7888999            Eamon
> >
> > INSERT the following into the Basic Table
> > this table is a root table of the database which has all the users
> > personal
> > details
> >
> > Country      Dob          UName
> > UK      21/11/1984     Eamon
> > UK      03/01/1980     John
> > USA    16/02/1986     Ash
> > France  29/11/1985    Patrick
> > UK      20/07/1984     Monique
> > UK      13/12/1982     Louise

> > <--------------------DESIRED RESULTS---------------------->
> > See how there are two results with the same UName in the AppFnd how do i
> > remove the duplicates where the select query doesn't retrieve any two
> > friend
> > and uname from both tables and returns only unique top rows for each
user.
> >

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:#04lATUsFHA.2212@TK2MSFTNGP15.phx.gbl...
> Hi
>
> Your DDL does now work  follow the instructions in the link carefully, you
> did not provide usable data either. There are two entries for ASH in Basic
> therefore you can how assume it is the Primary key.
>
> insert into appfnd ( approve, Friend, apId, UName )
> select 0,         'Eamon',        356668           ,'Louise'
> UNION ALL select 0   ,          'John',            8955566
> ,'Patrick'
> UNION ALL select 1,           'Monique',       788855            ,'Ash'
> UNION ALL select 0,            'Eamon',        7888999
,'Louise'
>
> INSERT INTO Friends ( Message,      Friend,           Rating,        apId
,
> UName )
> SELECT 'i love her',      'Louise'   ,  'A best m8',       356668,
> 'Eamon'
> UNION ALL SELECT 'my m8'   ,       'Patrick',    'A pen pal'  ,
> 8955566,           'John'
> UNION ALL SELECT 'my wife'  ,       'Ash',        'A close m8',
788855,
> 'Monique'
> UNION ALL SELECT 'could be'  ,    'Louise',     'A best m8',
7888999,
> 'Eamon'
>
> INSERT INTO Basic ( Country, Dob, UName )
> SELECT 'UK',      '19841121',     'Eamon'
> UNION ALL SELECT 'UK',      '19800103',     'John'
> UNION ALL SELECT 'USA',    '19860216',     'Ash'
> UNION ALL SELECT 'France',  '19851129',    'Patrick'
> UNION ALL SELECT 'UK',      '19821213',     'Louise'
>
> If you create the Rating table such as:
> CREATE TABLE Rating ( Rating nvarchar(50) COLLATE Latin1_General_CI_AI not
> null , Rank int not null CONSTRAINT PK_Rating PRIMARY KEY )
>
> With data:
> INSERT INTO Rating ( Rating , rank )
> SELECT 'A best m8', 1
> UNION ALL SELECT 'A close m8', 2
> UNION ALL SELECT 'A pen pal', 3
>
> This can then be used to make sure that they only add specific ratings.
You
> can then get the best rating using:
>
> SELECT F.Uname, F.Friend, Min(R.rank)
> FROM Friends F
> JOIN Rating R on R.Rating = F.Rating
> GROUP BY F.Uname,F.Friend
>
> This can be combined with the other tables to get the information:
>
> SELECT A.Uname, A.Friend, C.Rating, DATEDIFF(yy,B.dob,getdate()) AS Age,
> B.Country
> FROM ( SELECT F.Uname, F.Friend, Min(R.rank) AS Rank
> FROM Friends F
> JOIN Rating R on R.Rating = F.Rating
> GROUP BY F.Uname,F.Friend ) A
> JOIN Rating C ON A.Rank = C.Rank
> JOIN Basic B ON A.Friend = B.UName
>
> Add a where clause to restict it where necessary.
>
> John
>
> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
> news:OePY2vSsFHA.2064@TK2MSFTNGP09.phx.gbl...
> > correction forgot the 3rd table
> > DDL....

> >
> >
> > "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> > news:OLCej0MsFHA.2212@TK2MSFTNGP15.phx.gbl...
> >> Hi
> >>
> >> http://www.aspfaq.com/etiquette.asp?id=5006 shows the most useful way
to
> >> post DDL and sample data (as insert statements). You would have to
equate
> >> the rated you value to a ranking e.g.
> >>
> >> Rating
> >>
> >> RatedYou Value
> >> a gud friend    1
> >> a close friend    2
> >> a penpal         3
> >>
> >> You can then select the minimum (in this example) rating value for the
> >> corresponding rating from each friend.
> >>
> >> John
> >>
> >> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
> >> news:e%23g6N7JsFHA.2540@TK2MSFTNGP09.phx.gbl...
> >> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
> >> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
> >> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years,
(12 -
> >> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months
FROM
> >> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
> > AppFnd.apId
> >> > =
> >> > Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
> >> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
> >> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
> >> >
> >> > The query above is the query i am uisng to retrieve matching results
> > from
> >> > 3
> >> > tables, that returns all the rows pass through the "@UName"
parameter.
> > But
> >> > I
> >> > have a problem, If the user makes more than one request then there is
> > more
> >> > than one request from that user. I have tried using the TOP(1) but it
> > only
> >> > returns the top row for the entire table whereas all i want is the
top
> > row
> >> > from each friend.
> >> >
> >> > example of output currently.....
> >> > friend                    rated you                from
> > age
> >> > status
> >> > sean                       a gud friend            uk
> >> > 18
> >> > false
> >> > sean                       a close friend          uk
> >> > 18
> >> > false
> >> > sean                       a penpal                 uk
> >> > 18
> >> > false
> >> > joanne                   a gud friend            uk
> > 18
> >> > false
> >> > joanne                   a gud friend            uk
> > 18
> >> > false
> >> > joanne                   a gud friend            uk
> > 18
> >> > false
> >> >
> >> > the user added to many request for the user to approve
> >> >
> >> > example of output desired......
> >> > friend                    rated you                from
> > age
> >> > status
> >> > sean                       a gud friend            uk
> >> > 18
> >> > false
> >> > joanne                    a gud friend            uk
> > 18
> >> > false
> >> > one row per user
> >> >
> >> >
> >>
> >>
> >
> >
>
>

"Eamon Straughn" <blackmanvan***@aol.com> wrote in message
news:e7qZePWsFHA.1032@TK2MSFTNGP12.phx.gbl...
> #thank you mate just learnt something new there but i hope i am not
> bugging
> you as my desired results aren't achieved.
> # its has come to my attention you think i am trying to make a rating
> system
> whereas i am trying to remove any duplicates from the query which are
> filtered by the @UName (UserName) & @approve(accepted / rejected). so i
> only
> return one row like the following seeing that user Eamon has added Louise
> a
> friend twice so therefore two rows of louise will show as eamon's friend.
>
> #afid   approve   friend    apid     uname   message rating country year
> month
>
> 1       true        louise   356668  eamon   i love her ,,      ,,
> ,,      ,,
> 4       true        louise  7888999  eamon   could be ,,      ,,
> ,,
> ,,
>
> #dont worry about the rating and the rest all i want is that only
>
> afid   approve   friend    apid     uname   message rating country year
> month
> 1       true        louise   356668  eamon   i love her ,,      ,,
> ,,      ,,
>
> #is retireve instead of the two rows of the same friend and it shows other
> users like
>
> afid   approve   friend    apid     uname   message rating country year
> month
> 1       true        louise   356668  eamon   i love her ,,      ,,
> ,,      ,,
> 101   true        matt     256894  eamon   kool ,,      ,,          ,,
> ,,
> 15     true        leeanne  20118  eamon   examples ,,      ,,          ,,
> ,,
>
> but never retrieves a second row from that friend
> and i have also made a mistake in the basic table i corrected it and this
> table is ok just the first two
> thank you so much i learnt something new but would appreciate any help you
> could give me on this particular issue
>
>
>
>> > <-------------------------------THE FIRST
>> > TABLE----------------------------------->
>> > USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
>> >
>> > CREATE TABLE [dbo].[Friends](
>> >
>> > [frId] [int] IDENTITY(1,1) NOT NULL,
>> >
>> > [Message] [nvarchar](250) COLLATE Latin1_General_CI_AI NULL,
>> >
>> > [Friend] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
>> >
>> > [Rating] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
>> >
>> > [apId] [int] NULL,
>> >
>> > [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
>> >
>> > CONSTRAINT [PK_Friends] PRIMARY KEY CLUSTERED
>> >
>> > <-------------------------------THE SECOND
>> > TABLE----------------------------------->
>> >
>> > USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
>> >
>> > CREATE TABLE [dbo].[AppFnd](
>> >
>> > [afId] [int] IDENTITY(1,1) NOT NULL,
>> >
>> > [approve] [bit] NULL,
>> >
>> > [Friend] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
>> >
>> > [apId] [int] NULL,
>> >
>> > [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
>> >
>> > CONSTRAINT [PK_AppFnd] PRIMARY KEY CLUSTERED
>> >
>> > <-------------------------------THE THIRD
>> > TABLE-----------------------------------> [NOT FULL TABLE]
>> >
>> > USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
>> >
>> > CREATE TABLE [dbo].[Basic](
>> >
>> > [bId] [int] IDENTITY(1,1) NOT NULL,
>> >
>> > [Dob [datetime] NULL,
>> >
>> > [Country] [nvarchar](250) COLLATE Latin1_General_CI_AI NULL,
>> >
>> > [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
>> >
>> > CONSTRAINT [PK_Basic] PRIMARY KEY CLUSTERED
>> >
>> > <----------------------------THE SELECT
>> > QUERY---------------------------------->
>> >
>> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
>> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
>> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years, (12 -
>> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months FROM
>> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
> AppFnd.apId
>> > = Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
>> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
>> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
>> >
>> > <--------------------FILL THE TABLES---------------------->
>> > INSERT the following into the AppFnd Table
>> > this table is the table only the logged in user will see. @Uname and
> will
>> > approve the friend
>> >
>> > Approve      Friend         apId                  UName
>> > false             Eamon        356668           Louise
>> > false             John            8955566           Patrick
>> > true              Monique       788855            Ash
>> > false             Eamon        7888999            Louise
>> >
>> > INSERT the following into the Friends Table
>> > this table is the table only the friend will see after they have been
>> > approved from the person in the Uname column in the AppFnd table
>> >
>> > Message      Friend           Rating        apId                  UName
>> > i love her      Louise     A best m8       356668            Eamon
>> > my m8          Patrick    A pen pal        8955566           John
>> > my wife         Ash        A close m8      788855             Monique
>> > could be      Louise     A best m8       7888999            Eamon
>> >
>> > INSERT the following into the Basic Table
>> > this table is a root table of the database which has all the users
>> > personal
>> > details
>> >
>> > Country      Dob          UName
>> > UK      21/11/1984     Eamon
>> > UK      03/01/1980     John
>> > USA    16/02/1986     Ash
>> > France  29/11/1985    Patrick
>> > UK      20/07/1984     Monique
>> > UK      13/12/1982     Louise
>
>> > <--------------------DESIRED RESULTS---------------------->
>> > See how there are two results with the same UName in the AppFnd how do
>> > i
>> > remove the duplicates where the select query doesn't retrieve any two
>> > friend
>> > and uname from both tables and returns only unique top rows for each
> user.
>> >
>
> "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> news:#04lATUsFHA.2212@TK2MSFTNGP15.phx.gbl...
>> Hi
>>
>> Your DDL does now work  follow the instructions in the link carefully,
>> you
>> did not provide usable data either. There are two entries for ASH in
>> Basic
>> therefore you can how assume it is the Primary key.
>>
>> insert into appfnd ( approve, Friend, apId, UName )
>> select 0,         'Eamon',        356668           ,'Louise'
>> UNION ALL select 0   ,          'John',            8955566
>> ,'Patrick'
>> UNION ALL select 1,           'Monique',       788855            ,'Ash'
>> UNION ALL select 0,            'Eamon',        7888999
> ,'Louise'
>>
>> INSERT INTO Friends ( Message,      Friend,           Rating,        apId
> ,
>> UName )
>> SELECT 'i love her',      'Louise'   ,  'A best m8',       356668,
>> 'Eamon'
>> UNION ALL SELECT 'my m8'   ,       'Patrick',    'A pen pal'  ,
>> 8955566,           'John'
>> UNION ALL SELECT 'my wife'  ,       'Ash',        'A close m8',
> 788855,
>> 'Monique'
>> UNION ALL SELECT 'could be'  ,    'Louise',     'A best m8',
> 7888999,
>> 'Eamon'
>>
>> INSERT INTO Basic ( Country, Dob, UName )
>> SELECT 'UK',      '19841121',     'Eamon'
>> UNION ALL SELECT 'UK',      '19800103',     'John'
>> UNION ALL SELECT 'USA',    '19860216',     'Ash'
>> UNION ALL SELECT 'France',  '19851129',    'Patrick'
>> UNION ALL SELECT 'UK',      '19821213',     'Louise'
>>
>> If you create the Rating table such as:
>> CREATE TABLE Rating ( Rating nvarchar(50) COLLATE Latin1_General_CI_AI
>> not
>> null , Rank int not null CONSTRAINT PK_Rating PRIMARY KEY )
>>
>> With data:
>> INSERT INTO Rating ( Rating , rank )
>> SELECT 'A best m8', 1
>> UNION ALL SELECT 'A close m8', 2
>> UNION ALL SELECT 'A pen pal', 3
>>
>> This can then be used to make sure that they only add specific ratings.
> You
>> can then get the best rating using:
>>
>> SELECT F.Uname, F.Friend, Min(R.rank)
>> FROM Friends F
>> JOIN Rating R on R.Rating = F.Rating
>> GROUP BY F.Uname,F.Friend
>>
>> This can be combined with the other tables to get the information:
>>
>> SELECT A.Uname, A.Friend, C.Rating, DATEDIFF(yy,B.dob,getdate()) AS Age,
>> B.Country
>> FROM ( SELECT F.Uname, F.Friend, Min(R.rank) AS Rank
>> FROM Friends F
>> JOIN Rating R on R.Rating = F.Rating
>> GROUP BY F.Uname,F.Friend ) A
>> JOIN Rating C ON A.Rank = C.Rank
>> JOIN Basic B ON A.Friend = B.UName
>>
>> Add a where clause to restict it where necessary.
>>
>> John
>>
>> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
>> news:OePY2vSsFHA.2064@TK2MSFTNGP09.phx.gbl...
>> > correction forgot the 3rd table
>> > DDL....
>
>> >
>> >
>> > "John Bell" <jbellnewspo***@hotmail.com> wrote in message
>> > news:OLCej0MsFHA.2212@TK2MSFTNGP15.phx.gbl...
>> >> Hi
>> >>
>> >> http://www.aspfaq.com/etiquette.asp?id=5006 shows the most useful way
> to
>> >> post DDL and sample data (as insert statements). You would have to
> equate
>> >> the rated you value to a ranking e.g.
>> >>
>> >> Rating
>> >>
>> >> RatedYou Value
>> >> a gud friend    1
>> >> a close friend    2
>> >> a penpal         3
>> >>
>> >> You can then select the minimum (in this example) rating value for the
>> >> corresponding rating from each friend.
>> >>
>> >> John
>> >>
>> >> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
>> >> news:e%23g6N7JsFHA.2540@TK2MSFTNGP09.phx.gbl...
>> >> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
>> >> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS
>> >> > Expr1,
>> >> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years,
> (12 -
>> >> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months
> FROM
>> >> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
>> > AppFnd.apId
>> >> > =
>> >> > Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
>> >> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
>> >> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
>> >> >
>> >> > The query above is the query i am uisng to retrieve matching results
>> > from
>> >> > 3
>> >> > tables, that returns all the rows pass through the "@UName"
> parameter.
>> > But
>> >> > I
>> >> > have a problem, If the user makes more than one request then there
>> >> > is
>> > more
>> >> > than one request from that user. I have tried using the TOP(1) but
>> >> > it
>> > only
>> >> > returns the top row for the entire table whereas all i want is the
> top
>> > row
>> >> > from each friend.
>> >> >
>> >> > example of output currently.....
>> >> > friend                    rated you                from
>> > age
>> >> > status
>> >> > sean                       a gud friend            uk
>> >> > 18
>> >> > false
>> >> > sean                       a close friend          uk
>> >> > 18
>> >> > false
>> >> > sean                       a penpal                 uk
>> >> > 18
>> >> > false
>> >> > joanne                   a gud friend            uk
>> > 18
>> >> > false
>> >> > joanne                   a gud friend            uk
>> > 18
>> >> > false
>> >> > joanne                   a gud friend            uk
>> > 18
>> >> > false
>> >> >
>> >> > the user added to many request for the user to approve
>> >> >
>> >> > example of output desired......
>> >> > friend                    rated you                from
>> > age
>> >> > status
>> >> > sean                       a gud friend            uk
>> >> > 18
>> >> > false
>> >> > joanne                    a gud friend            uk
>> > 18
>> >> > false
>> >> > one row per user
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

"John Bell" <jbellnewspo***@hotmail.com> wrote in message
news:#487tQYsFHA.2880@TK2MSFTNGP12.phx.gbl...
> Hi
>
> They are not duplicates if they are different ratings; if they were use of
> the DISTINCT keyword would remove duplicates; therefore you have to have
> some rule to eliminate one or the other; it may be the most recent which
> will then be the max(frid) as the should be allocated in an ascending
order;
> you can still use the method I showed even without the rating table.
>
> John
>
>
> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
> news:e7qZePWsFHA.1032@TK2MSFTNGP12.phx.gbl...
> > #thank you mate just learnt something new there but i hope i am not
> > bugging
> > you as my desired results aren't achieved.
> > # its has come to my attention you think i am trying to make a rating
> > system
> > whereas i am trying to remove any duplicates from the query which are
> > filtered by the @UName (UserName) & @approve(accepted / rejected). so i
> > only
> > return one row like the following seeing that user Eamon has added
Louise
> > a
> > friend twice so therefore two rows of louise will show as eamon's
friend.
> >
> > #afid   approve   friend    apid     uname   message rating country year
> > month
> >
> > 1       true        louise   356668  eamon   i love her ,,      ,,
> > ,,      ,,
> > 4       true        louise  7888999  eamon   could be ,,      ,,
> > ,,
> > ,,
> >
> > #dont worry about the rating and the rest all i want is that only
> >
> > afid   approve   friend    apid     uname   message rating country year
> > month
> > 1       true        louise   356668  eamon   i love her ,,      ,,
> > ,,      ,,
> >
> > #is retireve instead of the two rows of the same friend and it shows
other
> > users like
> >
> > afid   approve   friend    apid     uname   message rating country year
> > month
> > 1       true        louise   356668  eamon   i love her ,,      ,,
> > ,,      ,,
> > 101   true        matt     256894  eamon   kool ,,      ,,          ,,
> > ,,
> > 15     true        leeanne  20118  eamon   examples ,,      ,,
,,
> > ,,
> >
> > but never retrieves a second row from that friend
> > and i have also made a mistake in the basic table i corrected it and
this
> > table is ok just the first two
> > thank you so much i learnt something new but would appreciate any help
you
> > could give me on this particular issue
> >
> >
> >
> >> > <-------------------------------THE FIRST
> >> > TABLE----------------------------------->
> >> > USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
> >> >
> >> > CREATE TABLE [dbo].[Friends](
> >> >
> >> > [frId] [int] IDENTITY(1,1) NOT NULL,
> >> >
> >> > [Message] [nvarchar](250) COLLATE Latin1_General_CI_AI NULL,
> >> >
> >> > [Friend] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
> >> >
> >> > [Rating] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
> >> >
> >> > [apId] [int] NULL,
> >> >
> >> > [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> >> >
> >> > CONSTRAINT [PK_Friends] PRIMARY KEY CLUSTERED
> >> >
> >> > <-------------------------------THE SECOND
> >> > TABLE----------------------------------->
> >> >
> >> > USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
> >> >
> >> > CREATE TABLE [dbo].[AppFnd](
> >> >
> >> > [afId] [int] IDENTITY(1,1) NOT NULL,
> >> >
> >> > [approve] [bit] NULL,
> >> >
> >> > [Friend] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
> >> >
> >> > [apId] [int] NULL,
> >> >
> >> > [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> >> >
> >> > CONSTRAINT [PK_AppFnd] PRIMARY KEY CLUSTERED
> >> >
> >> > <-------------------------------THE THIRD
> >> > TABLE-----------------------------------> [NOT FULL TABLE]
> >> >
> >> > USE [E:\DATABASE PROJECTS\APP_DATA\BUILD2.MDF]
> >> >
> >> > CREATE TABLE [dbo].[Basic](
> >> >
> >> > [bId] [int] IDENTITY(1,1) NOT NULL,
> >> >
> >> > [Dob [datetime] NULL,
> >> >
> >> > [Country] [nvarchar](250) COLLATE Latin1_General_CI_AI NULL,
> >> >
> >> > [UName] [nvarchar](50) COLLATE Latin1_General_CI_AI NOT NULL,
> >> >
> >> > CONSTRAINT [PK_Basic] PRIMARY KEY CLUSTERED
> >> >
> >> > <----------------------------THE SELECT
> >> > QUERY---------------------------------->
> >> >
> >> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
> >> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
> >> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years,
(12 -
> >> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months
FROM
> >> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
> > AppFnd.apId
> >> > = Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
> >> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
> >> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
> >> >
> >> > <--------------------FILL THE TABLES---------------------->
> >> > INSERT the following into the AppFnd Table
> >> > this table is the table only the logged in user will see. @Uname and
> > will
> >> > approve the friend
> >> >
> >> > Approve      Friend         apId                  UName
> >> > false             Eamon        356668           Louise
> >> > false             John            8955566           Patrick
> >> > true              Monique       788855            Ash
> >> > false             Eamon        7888999            Louise
> >> >
> >> > INSERT the following into the Friends Table
> >> > this table is the table only the friend will see after they have been
> >> > approved from the person in the Uname column in the AppFnd table
> >> >
> >> > Message      Friend           Rating        apId
UName
> >> > i love her      Louise     A best m8       356668            Eamon
> >> > my m8          Patrick    A pen pal        8955566           John
> >> > my wife         Ash        A close m8      788855             Monique
> >> > could be      Louise     A best m8       7888999            Eamon
> >> >
> >> > INSERT the following into the Basic Table
> >> > this table is a root table of the database which has all the users
> >> > personal
> >> > details
> >> >
> >> > Country      Dob          UName
> >> > UK      21/11/1984     Eamon
> >> > UK      03/01/1980     John
> >> > USA    16/02/1986     Ash
> >> > France  29/11/1985    Patrick
> >> > UK      20/07/1984     Monique
> >> > UK      13/12/1982     Louise
> >
> >> > <--------------------DESIRED RESULTS---------------------->
> >> > See how there are two results with the same UName in the AppFnd how
do
> >> > i
> >> > remove the duplicates where the select query doesn't retrieve any two
> >> > friend
> >> > and uname from both tables and returns only unique top rows for each
> > user.
> >> >
> >
> > "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> > news:#04lATUsFHA.2212@TK2MSFTNGP15.phx.gbl...
> >> Hi
> >>
> >> Your DDL does now work  follow the instructions in the link carefully,
> >> you
> >> did not provide usable data either. There are two entries for ASH in
> >> Basic
> >> therefore you can how assume it is the Primary key.
> >>
> >> insert into appfnd ( approve, Friend, apId, UName )
> >> select 0,         'Eamon',        356668           ,'Louise'
> >> UNION ALL select 0   ,          'John',            8955566
> >> ,'Patrick'
> >> UNION ALL select 1,           'Monique',       788855            ,'Ash'
> >> UNION ALL select 0,            'Eamon',        7888999
> > ,'Louise'
> >>
> >> INSERT INTO Friends ( Message,      Friend,           Rating,
apId
> > ,
> >> UName )
> >> SELECT 'i love her',      'Louise'   ,  'A best m8',       356668,
> >> 'Eamon'
> >> UNION ALL SELECT 'my m8'   ,       'Patrick',    'A pen pal'  ,
> >> 8955566,           'John'
> >> UNION ALL SELECT 'my wife'  ,       'Ash',        'A close m8',
> > 788855,
> >> 'Monique'
> >> UNION ALL SELECT 'could be'  ,    'Louise',     'A best m8',
> > 7888999,
> >> 'Eamon'
> >>
> >> INSERT INTO Basic ( Country, Dob, UName )
> >> SELECT 'UK',      '19841121',     'Eamon'
> >> UNION ALL SELECT 'UK',      '19800103',     'John'
> >> UNION ALL SELECT 'USA',    '19860216',     'Ash'
> >> UNION ALL SELECT 'France',  '19851129',    'Patrick'
> >> UNION ALL SELECT 'UK',      '19821213',     'Louise'
> >>
> >> If you create the Rating table such as:
> >> CREATE TABLE Rating ( Rating nvarchar(50) COLLATE Latin1_General_CI_AI
> >> not
> >> null , Rank int not null CONSTRAINT PK_Rating PRIMARY KEY )
> >>
> >> With data:
> >> INSERT INTO Rating ( Rating , rank )
> >> SELECT 'A best m8', 1
> >> UNION ALL SELECT 'A close m8', 2
> >> UNION ALL SELECT 'A pen pal', 3
> >>
> >> This can then be used to make sure that they only add specific ratings.
> > You
> >> can then get the best rating using:
> >>
> >> SELECT F.Uname, F.Friend, Min(R.rank)
> >> FROM Friends F
> >> JOIN Rating R on R.Rating = F.Rating
> >> GROUP BY F.Uname,F.Friend
> >>
> >> This can be combined with the other tables to get the information:
> >>
> >> SELECT A.Uname, A.Friend, C.Rating, DATEDIFF(yy,B.dob,getdate()) AS
Age,
> >> B.Country
> >> FROM ( SELECT F.Uname, F.Friend, Min(R.rank) AS Rank
> >> FROM Friends F
> >> JOIN Rating R on R.Rating = F.Rating
> >> GROUP BY F.Uname,F.Friend ) A
> >> JOIN Rating C ON A.Rank = C.Rank
> >> JOIN Basic B ON A.Friend = B.UName
> >>
> >> Add a where clause to restict it where necessary.
> >>
> >> John
> >>
> >> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
> >> news:OePY2vSsFHA.2064@TK2MSFTNGP09.phx.gbl...
> >> > correction forgot the 3rd table
> >> > DDL....
> >
> >> >
> >> >
> >> > "John Bell" <jbellnewspo***@hotmail.com> wrote in message
> >> > news:OLCej0MsFHA.2212@TK2MSFTNGP15.phx.gbl...
> >> >> Hi
> >> >>
> >> >> http://www.aspfaq.com/etiquette.asp?id=5006 shows the most useful
way
> > to
> >> >> post DDL and sample data (as insert statements). You would have to
> > equate
> >> >> the rated you value to a ranking e.g.
> >> >>
> >> >> Rating
> >> >>
> >> >> RatedYou Value
> >> >> a gud friend    1
> >> >> a close friend    2
> >> >> a penpal         3
> >> >>
> >> >> You can then select the minimum (in this example) rating value for
the
> >> >> corresponding rating from each friend.
> >> >>
> >> >> John
> >> >>
> >> >> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
> >> >> news:e%23g6N7JsFHA.2540@TK2MSFTNGP09.phx.gbl...
> >> >> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
> >> >> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS
> >> >> > Expr1,
> >> >> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years,
> > (12 -
> >> >> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months
> > FROM
> >> >> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
> >> > AppFnd.apId
> >> >> > =
> >> >> > Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
> >> >> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
> >> >> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
> >> >> >
> >> >> > The query above is the query i am uisng to retrieve matching
results
> >> > from
> >> >> > 3
> >> >> > tables, that returns all the rows pass through the "@UName"
> > parameter.
> >> > But
> >> >> > I
> >> >> > have a problem, If the user makes more than one request then there
> >> >> > is
> >> > more
> >> >> > than one request from that user. I have tried using the TOP(1) but
> >> >> > it
> >> > only
> >> >> > returns the top row for the entire table whereas all i want is the
> > top
> >> > row
> >> >> > from each friend.
> >> >> >
> >> >> > example of output currently.....
> >> >> > friend                    rated you                from
> >> > age
> >> >> > status
> >> >> > sean                       a gud friend            uk
> >> >> > 18
> >> >> > false
> >> >> > sean                       a close friend          uk
> >> >> > 18
> >> >> > false
> >> >> > sean                       a penpal                 uk
> >> >> > 18
> >> >> > false
> >> >> > joanne                   a gud friend            uk
> >> > 18
> >> >> > false
> >> >> > joanne                   a gud friend            uk
> >> > 18
> >> >> > false
> >> >> > joanne                   a gud friend            uk
> >> > 18
> >> >> > false
> >> >> >
> >> >> > the user added to many request for the user to approve
> >> >> >
> >> >> > example of output desired......
> >> >> > friend                    rated you                from
> >> > age
> >> >> > status
> >> >> > sean                       a gud friend            uk
> >> >> > 18
> >> >> > false
> >> >> > joanne                    a gud friend            uk
> >> > 18
> >> >> > false
> >> >> > one row per user
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

"Eamon Straughn" <blackmanvan***@aol.com> wrote in message
news:e%23g6N7JsFHA.2540@TK2MSFTNGP09.phx.gbl...
> SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
> AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
> Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years, (12 -
> (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months FROM
> AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND AppFnd.apId
> =
> Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
> AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
> (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
>
> The query above is the query i am uisng to retrieve matching results from
> 3
> tables, that returns all the rows pass through the "@UName" parameter. But
> I
> have a problem, If the user makes more than one request then there is more
> than one request from that user. I have tried using the TOP(1) but it only
> returns the top row for the entire table whereas all i want is the top row
> from each friend.
>
> example of output currently.....
> friend                    rated you                from                age
> status
> sean                       a gud friend            uk
> 18
> false
> sean                       a close friend          uk
> 18
> false
> sean                       a penpal                 uk
> 18
> false
> joanne                   a gud friend            uk                    18
> false
> joanne                   a gud friend            uk                    18
> false
> joanne                   a gud friend            uk                    18
> false
>
> the user added to many request for the user to approve
>
> example of output desired......
> friend                    rated you                from                age
> status
> sean                       a gud friend            uk
> 18
> false
> joanne                    a gud friend            uk                    18
> false
> one row per user
>
>

"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
news:e8y62RQsFHA.260@TK2MSFTNGP11.phx.gbl...
> Check this query for ideas.  It gets only one (the last one) from the
> derived table and the correlated subquery in the select.  If it doesn't
make
> sense. ask again :)
>
> select  customers.customerId, onlyOne.orderId, onlyOne.orderDate
> from  customers
>     left outer join ( select *
>    from (  select  OrderID, CustomerID, EmployeeID, OrderDate,
>      RequiredDate, ShippedDate, ShipVia, Freight,
>      ShipName, ShipAddress, ShipCity, ShipRegion,
>      ShipPostalCode, ShipCountry,
>       (select count(*)
>        from   orders o2
>        where  orders.customerId = o2.customerId
>          and orders.orderDate <= o2.orderDate) as orderCount
>     from  orders) as orders
>    where orderCount = 1) as onlyOne
>   on customers.customerId = onlyOne.customerId
>
>
>
>
>
>
> --
> --------------------------------------------------------------------------

"Eamon Straughn" <blackmanvan***@aol.com> wrote in message
news:%23f3CbYSsFHA.1168@TK2MSFTNGP11.phx.gbl...
>i don't actually get this i'm really a newbie learning as i go along can't
> understand the last bits
>
>
> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> news:e8y62RQsFHA.260@TK2MSFTNGP11.phx.gbl...
>> Check this query for ideas.  It gets only one (the last one) from the
>> derived table and the correlated subquery in the select.  If it doesn't
> make
>> sense. ask again :)
>>
>> select  customers.customerId, onlyOne.orderId, onlyOne.orderDate
>> from  customers
>>     left outer join ( select *
>>    from (  select  OrderID, CustomerID, EmployeeID, OrderDate,
>>      RequiredDate, ShippedDate, ShipVia, Freight,
>>      ShipName, ShipAddress, ShipCity, ShipRegion,
>>      ShipPostalCode, ShipCountry,
>>       (select count(*)
>>        from   orders o2
>>        where  orders.customerId = o2.customerId
>>          and orders.orderDate <= o2.orderDate) as orderCount
>>     from  orders) as orders
>>    where orderCount = 1) as onlyOne
>>   on customers.customerId = onlyOne.customerId
>>
>>
>>
>>
>>
>>
>> --
>> --------------------------------------------------------------------------
> --
>> Louis Davidson - http://spaces.msn.com/members/drsql/
>> SQL Server MVP
>> "Arguments are to be avoided: they are always vulgar and often
> convincing."
>> (Oscar Wilde)
>>
>> "Eamon Straughn" <blackmanvan***@aol.com> wrote in message
>> news:e%23g6N7JsFHA.2540@TK2MSFTNGP09.phx.gbl...
>> > SELECT AppFnd.afId, AppFnd.approve, AppFnd.Friend, AppFnd.apId,
>> > AppFnd.UName, Friends.Message, Friends.Rating, Friends.apId AS Expr1,
>> > Basic.Country, DATEDIFF(d, Basic.Dob, GETDATE()) / 364 AS Years, (12 -
>> > (DATEPART(m, Basic.Dob) - DATEPART(m, GETDATE()))) % 12 AS Months FROM
>> > AppFnd INNER JOIN Friends ON AppFnd.UName = Friends.Friend AND
> AppFnd.apId
>> > =
>> > Friends.apId AND AppFnd.Friend = Friends.UName INNER JOIN Basic ON
>> > AppFnd.Friend = Basic.UName WHERE (AppFnd.UName = @Uname) AND
>> > (Friends.Friend = @Uname) AND (AppFnd.approve = @approve)"
>> >
>> > The query above is the query i am uisng to retrieve matching results
> from
>> > 3
>> > tables, that returns all the rows pass through the "@UName" parameter.
> But
>> > I
>> > have a problem, If the user makes more than one request then there is
> more
>> > than one request from that user. I have tried using the TOP(1) but it
> only
>> > returns the top row for the entire table whereas all i want is the top
> row
>> > from each friend.
>> >
>> > example of output currently.....
>> > friend                    rated you                from
> age
>> > status
>> > sean                       a gud friend            uk
>> > 18
>> > false
>> > sean                       a close friend          uk
>> > 18
>> > false
>> > sean                       a penpal                 uk
>> > 18
>> > false
>> > joanne                   a gud friend            uk
> 18
>> > false
>> > joanne                   a gud friend            uk
> 18
>> > false
>> > joanne                   a gud friend            uk
> 18
>> > false
>> >
>> > the user added to many request for the user to approve
>> >
>> > example of output desired......
>> > friend                    rated you                from
> age
>> > status
>> > sean                       a gud friend            uk
>> > 18
>> > false
>> > joanne                    a gud friend            uk
> 18
>> > false
>> > one row per user
>> >
>> >
>>
>>
>
>