For each blog entry viewed in my web app, a (IPNumber, Date, BlogID) row is inserted into table "BlogViews"
How can I fetch the BlogID which has been most often visited, that is has the most number of rows?
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Thanks in advance,
Mark
how do i query a table and if it doesn't have a value for a field return something anyway?
e.g.
(table 1)
id title
--------
1 a
2 b
3 c
4 (null)
e.g. if null return 'not assigned'
?
Given the following data how do I make a SQL query that returns only 1 row per product?
The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }).
The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created.
Example Data:
HistoryID ProductID Name Color DateEffective RowTimeStamp
(auto-number PK)
1 1 Wheel Red 2/1/2008 2/1/2008
2 1 Wheel Blue 3/5/2008 3/1/2008
3 1 Wheel Orange 3/5/2008 3/2/2008
4 1 Wheel Black 1/1/2010 3/3/2008
5 2 Knob Blue 3/2/2008 3/2/2008
6 2 Knob Green 3/3/2008 3/3/2008
Query should return:
3 1 Wheel Orange 3/5/2008 3/2/2008
5 2 Knob Green 3/3/2008 3/3/2008
The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes.
Can anyone help?
I don't know why this is so difficult. What I want to do is take a table name as a parameter to build a query and get an integer value from the result of the query. But from all of the research I have been doing, Dynamic SQL is bad in SQL server because of SQL Injections. But my users are not going to be supplying the table names.
Things I have learned:
- SQL Functions cannot use Exec to execute query strings.
- SQL Functions can return a concatenated string that could be used by a stored procedure to Exec the query string.
So how can I write a stored procedure that will
1. take a parameter
2. Pass the parameter to a function that will return a string
3. Execute that string as SQL
4. Get a return value from that SQL statement
5. Then finally, from a View, how can I pass a parameter to the stored procedure and get the returned value from the stored procedure to be used as a field in the View?
Numbers 3, 4, and 5 are where I am really stuck. I guess I don't know the proper syntax and limitations of SQL Server.
I have
Customer table, tblCust: ID; Name
1 Peter2 Mary
Product table, tblProduct: ID; Name
1 Banana2 Orange3 Apple
Order tblOrder, tblOrder: CustID; ProductID; Amount
1 ;2 ;$20 – means Peter ordered $20 oranges
How do I write the SQL query so that the values in tblProduct become column, currently I have 20 items in that table. So, it will return something like this according to the information that I provide above?
Name Banana Orange Apple
Peter 0 20 0
Can Somebody please show me how to acheive this, using the order details in Northwinddatabase or any other good example. as much details as possible. Many Thanks!
View 6 Replies View RelatedI have written this sample query to search a full-text indexed table and return the results. If the word occurs more than once I want it to return as a new record and the results show a short summary of the location. I was using 'like', but the full table scans were really slowing it down. Can performance be improved for the following (The results returned by my query are accurate)
Query
DECLARE @searchString nvarchar(255);
DECLARE @searchStringWild nvarchar(275);
SET @searchString = 'first';
SET @searchStringWild = UPPER('"' +@searchString+ '*"');
SELECT id, '...' + SUBSTRING(searchResults.MatchedCell, searchResults.StartPosition, searchResults.EndPosition - searchResults.StartPosition) + '...' as Result
[Code] ....
When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
View 4 Replies View RelatedI had a server with SQL Server 7.0
I installed a named instance of SQL Server 2000 and then i passed all my DB
of the 7.0 instance to the 2000 instance.
Then i removed the 7.0 instance, that was the default instance.
So at the moment there is only the 2000 version, but it isn't the default
instance
Can the 2000 instance become the default instance? (So that clients can
connect to it simply through computer name, and not creating an alias)
thanks
Fede
I have a database design question. There're lots of ways to rome they say, and I want to hear what you think of this way.
Government supplies wheel chairs (and thinks alike) to people who need them. They stay in possesion of the (local) government and are distrubuted by a company X.
So we have Tools (Wheelchairs) and Users (of wheel chairs). The life-cycle of a wheel chair is that more than one user while use it over time.
I want to keep track of which users used a instance of a wheelchair.
No there's a developer who likes to put this in one table. (the chair and it's user) in a way like this
UID, WheelChairId, UserId, OwnerId, SerialNumber, BeginDateTime, EndDateTime, SomeOtherColumns
The UID is unique, the WheelChairId is a GUID which is Unique per wheelchair, but can have mutliple records in the table with no overlap.
If one of the values of the columns is changed a new record is made with the same wheelchair and a new begin date (the closed record gets an Enddate). So history is made automaticaly. By using the right query's I can see what users uses the chair in what period of time. But also changed ownerships and other changes in Someother columns overtime.
Is this a good or a common practice? Why use it, or stay away from it?
Henri
~~~~
There's no place like 127.0.0.1
I know how to use Linked server query to retrieve data from two different sql on premise instances.I would like to try the same on sql server instances hosted on azure.When I connect to sql instance, I don't see ServerOBjects->Linked server. I just see Database and security.Is this possible on sql azure, if so how can we achieve it
View 4 Replies View Related
Hi
Please help! I'm really stuck. I have a table (AssessorAllocations) that stores the data of students who have submitted assessments for marking by an Assessor. Each time an assessment is submitted by a student my program must determine which Assessor has the smallest workload and then assign the current assessment to that Assessor. My problem is how do I determine which Assessor has the smallest number of assessments assigned to him/her.
The table columns are:
AssessorRegistrationNo
LearnerRegistrationNo
QualficationName
SubjectName
AssessmentName
DateAssigned
Marked
DateMarked
Price
So I'm looking for something like this:
Select AssessorRegistrationNo from AssessorAllocations where {Select the Assessor who has the smallest number of AssessmentNames assigned to him/her where Marked='0' and DateAssigned<>''}
Thanks
Neil
I have to count the number of instances of each word in the Comments column throughout the table using SQL Server. Eg:
Row# Comments
1 I like working on SQL
2 I enjoy sleeping and like watching TV
So the output should be :
Word Count
I 2
like 2
working 1
on 1
SQL 1
.......
Any way to get this count using MS SQL?
One of my varchar columns in a table has multiple key words enclosed in a pattern of special characters.
Eg: William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world's pre-eminent [##dramatist##]. He is often called England's national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain. His plays have been translated into every major living language and are performed more often than those of any other [##playwright##].
I need to write to query to find all distinct key words that are enclosed within [## and ##]. My query should yield the following results from the string in the example above
[##actor##]
[##dramatist##]
[##playwright##] -- 2 occurrances, but I need it only once in my result set
[##poems##]
[##poet##] -- 2 occurrances, but I need it only once in my result set
[##sonnets##]
[##verses##]
[##writer##]
I need to run this on a large table, so I am looking for the best possible way to minimize any performance issues.
Just give you sample code, I have provided below 2 separate snippets, one with table variable and another with temp table.
DECLARE @MyTable TABLE (MyString VARCHAR (8000))
INSERT @MyTable VALUES ('William Shakespeare was an English [##poet##], [##playwright##], and [##actor##], widely regarded as the greatest [##writer##] in the English language and the world''s pre-eminent [##dramatist##]. He is often called England''s national [##poet##] and the "Bard of Avon". His extant works, including some collaborations, consist of about 38 plays, 154 [##sonnets##], two long narrative [##poems##], and a few other [##verses##], of which the authorship of some is uncertain.
[code].....
I have a problem where I have 2 compare 2 records from the same table. This part looks easy but the problem is for a User there can be multiple records and I have 2 compare each record with its previous instance based on the timestamp. Not only I have to compare I have to perform some analysis. Below is the Table script and sample output.
Givens: All SQL Server 2008 or 2012 tools at your disposal.
Production database contains the following tables (simplified for example: constraints ignored, etc.) associated with a racing video game’s server.
-- A player of our game
-- Table greater than 10 million rows
CREATE
TABLE [dbo].[User]
(
[UserId]
[bigint] NOT
NULL
,[country]
[int] NULL
-- User’s home country
,[name]
[nvarchar](15)
NULL -- User’s displayable name (‘John’, ‘Bill’)
,[subscriptionTier]
[int] NULL
)
-- 0 == free, 1 == paid, for instance
Assume that rows get written into the event tables at a rate of 1,000 a minute,are never updated once written and currently are only read on a replica/reporting server.
Question Background: Write up a single query that would return the following: List of users and whose “TotalMoneyEarned” value ever grew (between logon events) at a rate of more than 1,000 per minute (we’d consider these suspicious and flag them for later investigation).
For instance, if the sample data were:
-- example of [Events.UserLogon] data -- not the query output we want
EventId UserId TotalMoneyEarned LogonDate
----------- -------------------- ---------------- -----------------------
1 1 1000 2010-10-16 00:19:56.460
2 1 1500 2010-10-16 00:20:56.460
3 1 3000 2010-10-16 00:21:56.460
4 1 10000 2010-10-16 00:29:56.460
Event 1 is okay because there’s nothing to compare it against
Event 2 is okay because the TotalMoneyEarned only grew 500 in a minute
Event 3 should be flagged, as the value grew 1500 in a minute
Event 4 is okay, as it grew 7,000 in 8 minutes (< 1000 per minute)
Query Output (your query should return data in a format like this):
User Flagged Logon Time Rate Since Last Logon (money/minute)
John 2010-10-16 00:21:56 1500
Dave 2010-10-16 00:30:50 3200
Bill 2010-10-16 00:35:23 1000
It is likely that you will need to create sample data for both the User and [Events.Logon] tables. We are looking for a single query that returns data like what is represented in Query Output.
Here is the scenario,
I have 2 stored procedures, SP1 and SP2
SP1 has the following code:
declare @tmp as varchar(300)
set @tmp = 'SELECT * FROM
OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'',
''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )'
EXEC (@tmp)
SP2 has the following code:
SELECT *
FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this)
Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used.
Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used.
Functions - My last resort is to use a table valued function as shown:
FUNCTION MyFunction
( )
RETURNS @retTable
(
@Field1 int,
@Field2 varchar(50)
)
AS
BEGIN
-- the problem here is that I need to call SP1 and assign it's resulting data into the
-- @retTable variable
-- this statement is incorrect, but it's meaning is my goal
INSERT @retTableSELECT *FROM SP1
RETURN
END
I have a function that returns a table from a comma-delimited string.
I want to take this a step further and create a function that will return a set of tablenames in a table based on a 'group' parameter which is a simple integer...1->9, etc.Obviously, what I am doing is not working out.
CREATE FUNCTION dbo.fnReturnTablesForGroup
(
@whichgroup int
)
RETURNS @RETTAB TABLE (
TABLENAME VARCHAR(50)
[code]....
I'm having a bit of a trouble explaining what I'm trying to do here.
I have 3 "source" tables and a "connecting" table that I'm going to use
tblContacts - with contactID, ContactName etc
tblGroups - with GroupID, GroupName
tblSubGroups - with SubGroupID, GroupID and SubGroupName (groupID is the ID for the parent Group from tblGroups)
They are related in a table called
tblContactsGroupConnection - with ContactID, GroupID and SubGroupID
One contact can be related to many subgroups.
What I want is a list of all contacts, with their IDs, names and what groups they are related to:
ContactID, ContactName, [SubGroupName1, SubGroupName2, SubGroupName3]
ContactID, ContactName, [SubGroupName1, SubGroupName3]
ContactID, ContactName, [SubGroupName3]
I'm sure there's a simple solution to this, but I can't find it. Any help appreciated. :)
Kirikiri
I have looked everywhere but cannot find the solutions. I have two tables.
Table1 = tblvacation (10 records)
fields vacation_id, land, destination
Table2 = tblimages (1000 records)
fields image_id, vacation_id, folder, name
Both are related through vacation_id. Every vacation however has many pictures. I need a query where the result would give me back all records (vacations) from Table 1 with only 1 record from Table 2(one image per vacation).
Hi,
I got a problem.
I installed Microsoft SQL Server Management Studio Express 2005 version.
And I created a Compact database.
I created an connection in SSMSE to connect the database and opened a query form.
then, i run the following sql:
Select * from Table1
It returned 3 records to me.
After that, I used program to insert record into this table.
Then i ran this sql again, it still show me 3 records.
I closed the query form, and re-created a new query form, then run the sql, it returned 4 records to me.
Why? It's very strange and difficult to operate, right?
Is there anyone know how to make the SSMSE to return whole records without any close query form and re-create query form operation?
Thanks a lot!
And Merry X'max!!!
Hi
How can I know whether a query or a stored procedure is successfully executed? I mean like in delete case?
here is an example
sql_delete = "DELETE FROM UserData where U_ID='" & tempID & "'"
rstIDChk.Open sql_delete, cnn, adOpenStatic, adLockOptimistic
How can I make sure that the record is deleted so that I can proceed with other jobs? How can I catch it in the program like from VB? Is there any return like true or false in SQL? :(
Tks alot..
Hi! create table testReturn(id int identity(100,1),name varchar(10)) How can I return the value of identity column after inserting the value. Dim objConn As SqlConnection Dim SQLCmd As SqlClient.SqlCommand Dim ds As New DataSet Dim strsql As String Try objConn = New SqlConnection objConn.ConnectionString = _ "Network Library=DBMSSOCN;" & _ "Data Source=localhost;" & _ "Initial Catalog=mydb;" & _ "User ID=userid;" & _ "Password=pass" objConn.Open() strsql = "insert into testReturn values ('a')" SQLCmd = New SqlClient.SqlCommand(strsql, objConn) Dim rowsAffected As Integer = 0 rowsAffected = SQLCmd.ExecuteNonQuery Dim rv As String rv = SQLCmd.Parameters(0).Value.ToString() Response.Write(rv) Catch ex As Exception Response.Write(ex.ToString) End Try
View 5 Replies View RelatedHii I am Varun i have a problem with the dynamic stored procedure
This is my stored procedureALTER PROCEDURE dbo.sp_TimeTableAdjustment1
(@TeacherID_OnLeave numeric(9),
@DateFrom datetime ,@DateTo datetime , @UserID numeric(9)
)
AS
declare @flag as numeric(9)
declare @year as varchar(4)
set @year=(select batch from batchmaster where iscurrent=1 and isdeleted=0)
if( @year=null or len(@year)=0)
set @year = year(getdate())exec ('if not exists(select * from timetableadjustments_'+@year+' where datefrom='''+@datefrom+''' and dateto='''+@dateto+''' and teacherid_onleave='+@teacherid_onleave+')
begin
insert into TimeTableAdjustments_'+@year+' (TeacherID_OnLeave,DateFrom,DateTo,UserID) values ('+@TeacherID_OnLeave+','''+@DateFrom+''','''+@DateTo+''','+@UserID+');
end')
else
set @flag=(select timetableadjustmentid from timetableadjustments_2007 where datefrom=@datefrom and dateto=@dateto and teacherid_onleave=@teacherid_onleave)
return @flag
--exec('select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'
--')
--return @flag
--exec('@flag=select timetableadjustmentid from timetableadjustments_'+@year+' where datefrom='''+@DateFrom+''' and dateto='''+@DateTo+''' and teacherid_onleave='+@TeacherID_OnLeave+'')
This has got to be a simple one, but I could not find out how
I have this simple proc for test purposes only...
ALTER PROCEDURE dbo.RotoTest( @strSSN VARCHAR(11), @blnUseACHDate BIT = 0, @intInvestorId int = 0)ASreturn 5
Thats right it does nothing but return 5 and thats because I removed all the real code to simplify my question.
When I hit this proc in SQL Query Analyzer..
exec RotoTest '123-45-6789', 0, 1 No return value is displayed in Query Analyzer.
When I try something like this
exec RotoTest '123-45-6789', 0, 1, intRetValue Output (in this situation I get wrong number of arguments)
How can I get a return value displayed on my screen in SQL QUERY ANALYZER with out modifying the proc? I do not want to modify the proc in anyway (because I can't), I just want to display the return value
Hi
I have written a query for viewing the results of an on-line survey. I have three tables involved in this query: answers, answerpossibilities and users. So I use a few joins and made this query:
ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --')) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc
The above query works fine. The data returned by this query is shown in a gridview. When an answerpossibilty was never chosen it shows 0 as times chosen. So that's fine. But the problem is, only answers of users who completed the survey should be shown. In the users table there's a field user_completed. So the query should check whether this field is 1 (true).
ALTER PROCEDURE dbo.GeefAntwoordenMeerkeuze ( @question_id int ) AS SET NOCOUNT ON; SELECT answerpossibilities.answerpossibility_content AS[Answerpossiblity], COUNT(answers.answers_id) AS [Times chosen] FROM answers right OUTER JOIN answerpossibilities ON answers.answerpossibility_id = answerpossibilities.answerpossibility_id left join users on answers.user_id = users.user_id WHERE ((answerpossibilities.question_id = @question_id AND nswerpossibilities.answerpossibility_content!='-- choose answer --') and users.user_completed = 1) GROUP BY nswerpossibilities.answerpossibility_content ORDER BY [Times chosen] desc
Using this query only answers of users who completed the survey are shown but answer possibilities that were never chosen are no longer shown with 0 as times chosen. The gridview simply doesn't show them anymore.
Thanks for helping me!
Something went wrong by posting this message I guess, all blank lines were gone.. maybe because I used Safari on my iMac
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?
The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
i want a query which returns all the date between 2 dates . its like an calender.....for example i selected 2-1-2006(dd-mm-yyyy) to 18-03-2006 ....it should returns like this
2-1-2006
3-1-2006
4-1-2006
.
.
.
16-03-2006
17-03-2006
18-03-2006
Hello,I'm trying to something that just works in Oracle, but does not in SqlServer.How can I get the percentage of two counts to be returned on each rowof the query?select count(sid), /* all not the not null */count(*),(count(sid) /count(*) ) as percent_not_null,4 as four,(3/4) as three_over_fourfrom dbo.sysusers7082040Incredibly, it changes even 3/4 into a zero!For efficiency, I want the percentage returned in the query.And to not use variables and coding. Efficiency,both of the server, and of my time.Note: I am using dbo.sysusers as an EXAMPLE only. My realquery will be on user defined application tables.What is the solution please?
View 4 Replies View Relatedi need to retrieve the most recent timestamped records with uniquenames (see working query below)what i'm having trouble with is returning the next-most-recent records(records w/ id 1 and 3 in this example)i also need to return the 3rd most recent, 4th, 5th and 6th most recent- i figure if i can get the 2nd working, 3rd, 4th, etc will be cakethanks,brett-- create and populate tabledrop table atestcreate table atest(id int not null, name char(10), value char(10),timestamp datetime)insert into atest values (1,'a','2','1/1/2003')insert into atest values (2,'a','1','1/1/2004')insert into atest values (3,'b','2','1/1/2003')insert into atest values (4,'b','3','1/1/2002')insert into atest values (5,'b','1','1/1/2004')-- select most recent records with distinct "name"sselect a.* from atest as awhere a.id = (select top 1 b.id from atest as bwhere b.name = a.nameorder by timestamp desc )/*query results for above query (works like a charm)2a 1 2004-01-01 00:00:00.0005b 1 2004-01-01 00:00:00.000*/
View 6 Replies View RelatedThis is my problem, i have 2 tables: one table that hold data, having id as identity, say table (id, content), the other table have 2 columns: (id, number). The second table number column refer to the id in the first table. i want to build a query that get the data from the first table that correspond to a specific id in the second table, not only this i want to get the previous, the current and the next item.
For example: if table_data, table_info is first and second tables, something that can do it is :
DECLARE @i int
SELECT * FROM table_data WHERE id in (@i -1, @i +1, @i)
The problem here if @i, @i+1 or @i-1 doesn't exist the column will not be returned, i want to get a result similar to
id, content
25 null
26 null
27 #some content#
or
34 #content1#
35 #content2#
36 #content3#
so my problem is that nulls doesn't appear, i thought about using OUTER JOIN, but the problem is that outer join take tables, not (@i -1, @i +1, @i), so if only i can make somehow the outer join use these values, i think it works.
Any help please, and thanks in advance
Hi
I have encountered some problems creating MDX query.
There are two input parameters on report, both dropdown list and from query.
The first parameter will check "State", and the second parameter "Store Name" depends on previous' result.
If there's no "State" found, I manage to show a "No Data" in the "State" droplist.
But with "No Data" in first parameter, the second parameter simply is not enabled.
I want the second parameter shows " No Data" as well but not succeed.
Here's code
...
SELECT {[Measures].[A1], [Measures].[B1], [Measures].[C1]} ON COLUMNS ,
[Store].[Store Name].ALLMEMBERS ON ROWS
FROM ( SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube])
...
The query need to look at dataset that contains [store name] on rows, and some measures on columns.
It also restricted by parameter @State.
Store Name that satisfies @State will be displayed.
But if nothing from SELECT ( STRTOSET(@State) ) ON COLUMNS FROM [Cube], there's nothing in result.
I'd rather like show something rather than nothing.
I try codes like
...
MEMBER [Store].[Store].[NA] AS '"N/A"'
IIF(<Parameter empty>,
(SELECT ... ON COLUMNS, [Store].[Store].[NA] ON ROWS FROM ...),
(SELECT ...<Original statement >)
)
...
It shows "Subselect support only Column axis".
Any one has an idea?
Please help me out
Many thanks!
Mr. L
I have table "Clients" who have associated records in table "Mailings"
I want to populate a gridview using a single query that grabs all the info I need so that I may utilize the gridview's built in sorting.
I'm trying to return records containing the next upcoming mailing for each client.
The closest I can get is below:
I'm using GROUP BY because it allows me to return a single record for each client and the MIN part allows me to return the associated record in the mailings table for each client that contains the next upcoming 'send_date'
SELECT MIN(dbo.tbl_clients.client_last_name) AS exp_last_name, MIN(dbo.tbl_mailings.send_date) AS exp_send_date, MIN(dbo.tbl_mailings.user_id) AS exp_user_id, dbo.tbl_clients.client_id, MIN(dbo.tbl_mailings.mailing_id) AS exp_mailing_idFROM dbo.tbl_clients INNER JOIN
dbo.tbl_mailings ON dbo.tbl_clients.client_id = dbo.tbl_mailings.client_idWHERE (dbo.tbl_mailings.user_id = 1000)GROUP BY dbo.tbl_clients.client_id
The user_id set at 1000 part is what makes it rightly pull in all clients for a particular user. Problem is, by using the GROUP BY statement I'm just getting the lowest 'mailing_id' number and NOT the actual entry associated with mailing item I want to return. Same goes for the last_name field. Perhaps I need to have a subquery within my WHERE clause?Or am I barking up the wrong tree entirely..