To Return Distinct Column Using INNER JOIN
Apr 23, 2008
Hello all,
I am using INNER JOIN to connect 2 tables together but I wish it to return distinct columns instead of repeating itself !
eg.
Current output would be:
UserID Name UserID OrderID
1 John 1 5
2 Bob 2 6
I want it to be:
UserID Name OrderID
1 John 5
2 Bob 6
I need to use SELECT * as there are many many columns and wish to save time :)
Cheers,
James
View 3 Replies
ADVERTISEMENT
Apr 25, 2015
I'm creating a sql stored procedure inside this proc it returns some information about the user, i.e location, logged in, last logged in, etc I need to join this on to the photos table and return the photo which has been set as the profile picture, if it hasn't been set then return the first top 1 if that makes sense?
The user has the option to upload photos so there might be no photos for a particular user, which I believe I can fix by using a left join
My photos table is constructed as follows:
CREATE TABLE [User].[User_Photos](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[UserId] [bigint] NOT NULL,
[PhotoId] [varchar](100) NOT NULL,
[IsProfilePic] [bit] NULL,
[Code] ....
Currently as it stands the proc runs but it doesn't return a particular user because they have uploaded a photo so I need to some how tweak the above to return null if a photo isn't present which is where I'm stuck.
View 3 Replies
View Related
Mar 29, 2007
suppose i have aDataset with 11 rows. field1 with 5 rows of aaa, 6 rows of "bbb"
I want's some thing like
field1 rowcount
aaa 5
bbb 6
View 1 Replies
View Related
Jul 13, 2006
Hello !
for MS SQL 2000
SELECT tableA.idA, tableB.idB,tableB.ColX,tableA.ColA,
tableA.ColB, tableA.ColC
FROM tableB RIGHT OUTER JOIN tableA ON tableB.idB = tableA.idA
I want to do a DISTINCT on
tableB.ColX, tableA.ColA, tableA.ColB
to return only the rows tableB.ColX, tableA.ColA, tableA.ColB which are differents
thank you for helping
View 14 Replies
View Related
Aug 22, 2007
Hi,
How do I ensure that DISTINCT values of r.GPositionID are returned from the below??
Code Snippet
SELECT CommentImage AS ViewComment,r.GPositionID,GCustodian,GCustodianAccount,GAssetType
FROM @GResults r
LEFT OUTER JOIN
ReconComments cm
ON cm.GPositionID = r.GPositionID
WHERE r.GPositionID NOT IN (SELECT g.GPositionID FROM ReconGCrossReference g)
ORDER BY GCustodian, GCustodianAccount, GAssetType;
Thanks.
View 11 Replies
View Related
May 5, 2008
I am trying to create a query that will find all the records that have the same value multiple times in the a column called phonenumber.
How do i return disticnt records having count greater than 1
View 5 Replies
View Related
Oct 17, 2006
Hi
I am trying to write a query that will return a full record with a particular distinct field (the rest of the record being the first such record that includes the distinct field).
For example, for the following:
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
Grape Y Green
Grapefruit N Yellow
I would want to return (assuming Colour was the distinct field):
Fruit Like? Colour
Apple Y Green
Orange N Orange
Banana Y Yellow
How do I do this? I've tried using a join (of all different kinds) with a subquery that uses SELECT DISTINCT but this doesn't seem to work. I've tried GROUP BY but none of the aggregate functions seem to just take the first found field.
Thanks for any help you can offer.
View 11 Replies
View Related
Aug 17, 2006
I need to somehow filter the results of my stored procedure to return the distinct "OrderNum" values. I'm using SQL database and I'm look for a way to alter the results from the stored procedure. My stored procedure rptOpenOrderLines currently returns all invoices (items under a OrderNum). I want to somehow filter those results to return one and only one of those "OrderNum" variables from the invoices. The tricky part is that I need to somehow find a way to do this without going into my database and directly altering the SQL stored procedure. I would be happy for any recommendations/ideas. Thanks!
View 3 Replies
View Related
Jul 23, 2005
I was curious...Is there a way to select distinct on a combination of some fields andthe for each record returned also get the other fields of anarbitrarily chosen record matching the fields in the distinct record.For example, if I have a select distinct on say three fields:SELECT DISTINCT Code1, Code2, Code3but the table also has other fields, maybe Foo1 and Foo2, and I wantFoo1 and Foo2 to also be displayed. Since there may be multiplerecords that match a particular Code1, Code2, Code3, then I just wantone of those to be arbitrarily chosen.
View 3 Replies
View Related
Mar 22, 2006
I have a table in my MS SQL 2000 database called News which has a field caled NewsDate. This is a standard Date field which stores the info in this format: 3/1/2001.
I want to create a query that returns one row for each year that there is a story.
For example, if I had this data...
3/1/2001, 6/27/2003. 9/17/2003, 1/1/2006, 4/5/2006
the query would return this result:
2001
2003
2006
This is the query I've started with:
SELECT DISTINCT NewsDate FROM News ORDER BY NewsDate DESC
What modifier can I apply to the NewsDate field to extract JUST the year from the table? If this were ASP I would try something like Year(Date), but, of course, I can't do that here.
Is this even possible? I've been looking up date functions, but haven't found anything that will work in a select statement. ANY and ALL advice will be greatly appreciated.
View 1 Replies
View Related
Dec 15, 2014
I have a view which joins 2 tables together in a left outer join.
Now, the left outer join table (lets call this SalesData) may or may not have records. As months and years go by, records will be inserted or updated.
I am trying to find a query which will do the following:
Bring back all records where the branch matches an input value and also:
- bring back NULL records for the YR and MO column
- bring back values which match the input params for YR and MO column.
this works as expected:
quote:
SELECT wmtecp.*
FROM v_stores wmtecp
RIGHT OUTER JOIN v_Stores roj ON wmtecp.ID = roj.ID
WHERE wmtecp.TM = @p1 AND 1=1 AND roj.YR IS NULL AND roj.MO IS NULL OR wmtecp.MO = 11 AND wmtecp.YR = 2014
This is right outer joining the view to itself and prefer it this way.
Here is the thing: I want to bring back only distinct records (distinct by store ID I guess).
We are bringing back result set which contains NULL and NON null values but for both result sets the same storeID is returned, I do not want this but to only bring back the same record once either WITH values or WITHOUT values.
View 1 Replies
View Related
Mar 5, 2007
Hello, first post since I can usually find answers by reading the forums. I've searched the internet up and down and for some reason I can't get this query to work properly.
I have two tables:
ticket
ticket_id
ticket_to
ticket_from
message
message_id
ticket_id
message
There can be several messages per ticket, connected by the ticket_id. I'd like to only pull only the most recent message, as well as the results from the ticket table. Currently I'm getting all messages per ticket.
Here's what I have:
SELECT distinct ticket.ticket_to, ticket.ticket_from, message.ticket_id, message.message
FROM tickets
INNER JOIN message ON tickets.ticket_id = message.ticket_id
GROUP BY message.ticket_id, message, ticket_to, ticket_from
Any help would be greatly appreciated! Thanks much.
View 3 Replies
View Related
Jul 20, 2005
Hi everyoneHave a problem I would areally appreciate help with.I have 3 tables in a standard format for a Bookshop, egProductsCategoriesCategories_Productsthe latter allowing me to have products in multiple categories.Everthing works well except for one annoying little thing.When an individual product (which is in more than one topcategory) is addedto the Shopping Cart it displays twice, because in my select statement Ihave the Category listed. I realise I could remove the TopCategory from thestatement and that makes my DISTINCT work as I wanted, but Id prefer to havethe TopCategory as it saves me later having to another SQL query (Im alreadydoing one to allow me not to list category in the Statement .... but If Ican overcome this one ... then I can remove this as well).Here is my table structure (the necessary bits)productsidProduct int....categoriesidcategory intidParentCategory inttopcategory int...categories_productsidCatProd intidProduct intidCategoryWhen I run a query such asSELECT DISTINCT a.idProduct, a.description,a.descriptionLong,a.listPrice,a.price,a.smallImageUrl,a.stock, a.fileName,a.noShipCharge,c.topcategoryFROM products a, categories_products b, categories cWHERE active = -1 AND homePage = -1AND a.idProduct = b.idProductAND c.idcategory=b.idcategoryAND prodType = 1 ORDER BY a.idProduct DESCThis will return all products as expected, as well as any products which arein more than one TopCategory.Any ideas how to overcome this would be greatly appreciated.CheersCraig
View 14 Replies
View Related
Feb 14, 2008
Hi again,
I have this SQL (part of a stored procedure) where I do LEFT JOIN. SELECT callingPartyNumber, AlertingName, originalCalledPartyNumber, finalCalledPartyNumber,
dateTimeConnect,
dateTimeDisconnect,
CONVERT(char(8), DATEADD(second, duration, '0:00:00'), 108) AS duration,
clientMatterCode
FROM CDR1.dbo.CallDetailRecord t1
LEFT JOIN CDR2.dbo.NumPlan t2 ON t1.callingPartyNumber=t2.DNorPattern
WHERE
(t1.callingPartyNumber LIKE ISNULL(@callingPartyNumber, t1.callingPartyNumber) + '%') AND
(t1.originalCalledPartyNumber LIKE ISNULL(@originalCalledPartyNumber, t1.originalCalledPartyNumber) + '%') AND
(t1.finalCalledPartyNumber LIKE ISNULL(@finalCalledPartyNumber, t1.finalCalledPartyNumber) + '%') AND
(t1.clientMatterCode LIKE ISNULL(@clientMatterCode, t1.clientMatterCode) + '%') AND
(@callerName is NULL OR t2.AlertingName LIKE '%' + @callerName + '%') AND
(t1.duration >= @theDuration) AND
((t1.datetimeConnect) >= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeConnect)), t1.datetimeConnect)) AND
((t1.dateTimeDisconnect) <= ISNULL(convert(bigint,
datediff(ss, '01-01-1970 00:00:00', @dateTimeDisconnect)), t1.dateTimeDisconnect))
The problem is that if the t2 has more than one entry for the same DNorPattern, it pulls the record more than once. So say t1 has a callingPartyNumber = 1000. t2 has two records for this number. It will pull it more than once. How do I get the Unique value.
What I am trying to get is the AlertingName (name of the caller) field value from t2 based on DNorPattern (which is the phone number).
If this is not clear, please let me know.
Thanks,
Bullpit
View 24 Replies
View Related
Feb 24, 2015
SELECT first.name,first.country, second.name, second.string
FROM
first LEFT OUTER JOIN second
ON
first.name = second.name
WHERE
first.date='2015/02/24'
This query means all record from second table and matching record from first table. Now my question is that on 24 Feb 2015 I have duplicate names in second table and I want distinct names from second table and then its matching values from first table. Now my query is showing all duplicate values from second table and its matching record from first table.
View 5 Replies
View Related
Nov 16, 2006
I've been trying to get this to work right.
The db table has 3 fields: id, vwr, and reqType.
I need all DISTINCT vwr's from the table. (vwr's can repeat)
This gives me all rows, not distinct...
select distinct d.id, d.vwr, d.reqType from tblVWR AS dinner join tblVWR ton d.vwr = t.vwr
Any suggestions?
Thanks,
Zath
View 2 Replies
View Related
Jun 25, 2007
Hi guys,
Just trying to select a set of Articles from a SQL Server Database. The Articles all have a Category ID which is stored in another table (as an Article could be in more than one Category). I want to select the Top 3 Articles in a Category. At the moment I have as my SQL;
"SELECT TOP 3 f.ArticleID, f.Heading, f.Summary, f.WrittenDate, f.ArticleURL FROM feedTable f LEFT JOIN Categories c ON f.ArticleID = c.ArticleID WHERE c.CategoryID=" + CategoryID + " AND c.ArticleID<>" + id + " ORDER BY c.CategoryID"
Which seems to work to an extent in that I do get three articles in the same Category appearing. However, there are sometimes duplicates appearing, so I need to incorporate a DISTINCT clause to the above. I'm not sure where to put this in though. Any ideas?
Thanks.
View 6 Replies
View Related
Jul 28, 2007
I want to select all usernames from tlbUsers which practice a certain sport.I also have a table UsersAndSports:UserID intSportID intI therefore need an inner join statement:select username from(SELECT ROW_NUMBER() OVER (ORDER BY au.LastActivityDate DESC) as RowNum,ud.username from tblusers usinner join tblUsersAndSports uas on uas.usercode=us.usercode and (uas.sportID=3 or uas.sportID=4)WHERE us.UserName <>'' )as MemberInfo WHERE RowNum between (@startRowIndex+1) AND (@startRowIndex+@maximumRows)The problem lies in the usage of the ROW_NUMBER command. I need that in order to effectively page through my records, but it also makes thatI cant make a distinct selection as each RowNum is unique....:SIn case the user would practice 2 sports, the query would return 2 rows...if I place a distinct in front of the username as such:select distinct username fromThe query would return each user only once...BUTBUTBUT: my startrowindex and maximumrows apply to the results that can be found in the MemberInfo selection..so lets say my startrowindex would be 0 and maximumrow 5if my 1st query (without distinct) was to return:johnjohnjohnjohnmikemikerobmy 2nd query (with distinct) would return:johnmikewhereas I want it to return:johnmikerobWhat can I do?
View 2 Replies
View Related
Jul 17, 2015
I have a SQL Query issue you can find in SQL Fiddle
SQL FIDDLE for Demo
My query was like this
For Insert
Insert into Employee values('aa', 'T', 'qqq')
Insert into Employee values('aa' , 'F' , 'qqq')
Insert into Employee values('bb', 'F' , 'eee')
Insert into Employee values('cc' , 'T' , 'rrr')
Insert into Employee values('cc' , 'pp' , 'aaa')
Insert into Employee values('cc' , 'Zz' , 'bab')
Insert into Employee values('cc' , 'ZZ' , 'bac')
For select
select col1,MAX(col2) as Col2,Max(Col3) as Col3
from Employee
group by Col1
I supposed to get last row as
cc Zz bab
Instead I am getting
cc Zz rrr
which is wrong
View 8 Replies
View Related
Aug 3, 2006
I am writing a script to create a audit trigger on any table. I am getting duplicate rows inserted into my audit table, only for the primary key columns. Anybody see why?
Right now I am debugging an Insert, so I think you can ignore the "U" update part of the Where clause.
....starts with other code to determine columns and primary key fields for selected table....
--get number of columns
select
@rowId = min(RowId),
@MaxRowId = max(RowId)
from #tblFieldNames
-- Loop through fields and build Sql string
while @RowId <= @MaxRowId
BEGIN
SELECT @fieldname = colName FROM #tblFieldNames WHERE RowId = @RowId
SELECT @sql = 'insert tblAuditAdmin (TableAltered, [Action], FieldName, OldValue, NewValue, UpdateDate, UpdateNumber, UserName)'
SELECT @sql = @sql + ' select distinct''' + @TableName + ''''
SELECT @sql = @sql + ',''' + @TriggerType + ''''
SELECT @sql = @sql + ',''' + @fieldname + ''''
SELECT @sql = @sql + ',convert(varchar(1000),d.' + @fieldname + ')'
SELECT @sql = @sql + ',convert(varchar(1000),i.' + @fieldname + ')'
SELECT @sql = @sql + ',''' + @UpdateDate + ''''
SELECT @sql = @sql + ', 1'
SELECT @sql = @sql + ',''' + @UserName + ''''
SELECT @sql = @sql + ' FROM #ins i FULL OUTER JOIN #del d'
SELECT @sql = @sql + @pkJoinClause
SELECT @sql = @sql + ' WHERE (''' + @TriggerType + ''' = ''I'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''D'')'
SELECT @sql = @sql + ' OR (''' + @TriggerType + ''' = ''U'' AND '
SELECT @sql = @sql + '((i.' + @fieldname + ' <> d.' + @fieldname + ')'
SELECT @sql = @sql + ' OR (''' + @fieldname + ''' in (Select colName from #primaryKeyFields))'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NULL AND d.' + @fieldname + ' is NOT null)'
SELECT @sql = @sql + ' OR (i.' + @fieldname + ' IS NOT NULL AND d.' + @fieldname + ' is null)))'
EXEC (@sql)
set @RowId = @RowId + 1
END
View 5 Replies
View Related
Feb 6, 2008
Ok, so I need to count the Distinct records from column 1 in which there is not a true value in any of the records for that distinct column 1 number. Here is a short example of my records:dbo_dbWafer_Slicing
dbo_dbWafer_Slicing
WaferID
SawDate
SawRunNumber
Pass
03-157.05
1/8/2008 9:54:00 AM
03-157
0
03-157.03
1/8/2008 9:53:00 AM
03-157
-1
03-157.04
1/8/2008 9:53:00 AM
03-157
0
03-157.02
1/8/2008 9:52:00 AM
03-157
-1
03-157.01
1/8/2008 9:50:00 AM
03-157
-1
03-165.06
1/4/2008 10:46:00 AM
03-165
0
03-165.07
1/4/2008 10:46:00 AM
03-165
0
03-165.04
1/4/2008 10:45:00 AM
03-165
0
03-165.05
1/4/2008 10:45:00 AM
03-165
0
03-165.02
1/4/2008 10:44:00 AM
03-165
0
03-165.03
1/4/2008 10:44:00 AM
03-165
0
03-165.01
1/4/2008 10:43:00 AM
03-165
0
So, how many Distinct SawRunNumbers had no passing wafers? In trying to do this I've come up with:"SELECT COUNT(DISTINCT SawRunNumber) AS BouleCount FROM dbWafer_Slicing WHERE (SawDate >= @MinDate) AND (SawDate <= @MaxDate) AND (Pass = 1) HAVING (COUNT(DISTINCT WaferID) > 0)" but that doenst work. It still counts records where pass = 0 for distinct SawRunNumbers even if one record within that SawRunNumber is passing. From the above sample data I should get a result of 1 not 2 or 3. Can I do this with this set of data? I'm using SQL Server 2005 EE.Thanks for your help.
View 6 Replies
View Related
Sep 7, 2015
We have SharePoint list which has, say, two columns. Column A and Column B.
Column A can have three values - red, blue & green.
Column B can have four values - pen, marker, pencil & highlighter.
A typical view of list can be:
Column A - Column B
red - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red - pen
blue - pencil
blue - highlighter
blue - pencil
We are looking to create a report from SharePoint List using SSRS which has following view:
red blue green
pen 2 0 1
marker 0 1 0
pencil 1 3 0
highlighter 1 1 1
We tried Sum but not able to display in single row.
View 2 Replies
View Related
Apr 28, 2008
Hi All,
I need a bit of help with a join. I have 2 tables :
TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue
Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
thanks in advance
View 11 Replies
View Related
Apr 28, 2008
Hi All,
I need a bit of help with a join. I have 2 tables :
TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue
Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
thanks in advance
View 7 Replies
View Related
Sep 14, 2007
I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid user2_guidI am trying to write a query to return a single list of all a users' friends in a single column. Depending on who initiates the friendship, a users' guid value can be in either of the two columns. Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way... Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))
View 1 Replies
View Related
Jul 30, 2007
Hi,
Bonjour,
I want distinct sum for one of my column.But iam not able to do that.
I tried DISTINCTSUM function given inMSDN, but it always return ZERO.
My function call in FOOTER section is called first, before my DETAILS section function call.
please help me for this.
thanks and regards
Hemant
View 9 Replies
View Related
Aug 12, 2015
I am using this below query to generate this below result set. I want to display only one record as blank, it should not be duplicate records.
SELECT [MDMTerminalID], ISNULL([TerminalAlias], 'BLANK') , [RegionID] FROM [dbo].[Terminal]
Union
select -1,'All', (select top 1 areaid from area where name = 'Other')
View 2 Replies
View Related
Oct 19, 2006
hello I am trying to get a distinct on one column and 2 tables but it doesnt work
Table1
ID_Table1
Name1
Number1
Table2
ID_Table2
ID_Table1
Name2
I want to get : ID_Table1, DISTINCT(Name1), Name2
WHERE Name1 LIKE 'A%'
how can I do it ?
thank you
View 4 Replies
View Related
Apr 15, 2015
I have this
SELECT DISTINCT inta, name, PHN#, fROM nydta.adres
WHERE inta <> ' '
I want the distinct for inta because alot of the time phone is blank so those are coming thru i do want all columns, but distinct for inta.
View 1 Replies
View Related
Sep 11, 2007
We have a query in which there are 20,000 rows and 90 distinct ID's
If we say
SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8
from #TEMPX
we get the 90 distinct values, but if we say
SELECT distinct siteid ,ts1, ts2, ts3, ts4, ts5, ts6, ts7, ts8, ts1avg, ts2avg, ts3avg, ts4avg, ts5avg, ts6avg, ts7avg, ts8avg from #TEMPX
TS1 contains 90 distinct values, whily ts1avg has 2,000 disitinct rows
Is there a way to get Distinct to work against only one column, i.e. SiteID ?
View 2 Replies
View Related
Aug 30, 2007
Hi,
This is a query that joins a vouple of tables to display all the products purchased by a group of customers and the price they paid for it.
SELECT DISTINCT (p.code),p.descript_1 + ' ' + p.descript_2 + ' ' + p.descript_3 as description,sol.p_sales as price,sol.q_ordered as quantity,(sol.p_sales * sol.q_ordered) as total,so.date_in as dateFROM EfasLive..debtor AS d
INNER JOIN Informatica..so AS so ON so.deb_code = d.code AND so.co_code = d.co_code
INNER JOIN Informatica..so_line AS sol ON sol.code = so.code AND sol.co_code = so.co_code AND sol.acc_year = so.acc_year AND sol.efas = so.efas
INNER JOIN EfasLive..part AS p ON p.code = sol.part
WHERE d.[grp{003}] = 'GROUP' AND p.co_code = 1 AND p.code NOT LIKE '&%' AND so.date_in > DATEADD(m,-3,GETDATE()) AND sol.q_ordered > 0
ORDER BY (p.code), datum DESC
The problem with this is that it returns multiple lines for every product (p.code). Like so:
code description price quantity total date
603244 description_1 17.950000 150.000000 2692.500000000000 2007-08-01 00:00:00
603244 description_1 17.950000 150.000000 2692.500000000000 2007-07-10 00:00:00
603245 description_2 17.950000 40.000000 718.000000000000 2007-07-24 00:00:00
603245 description_2 17.950000 25.000000 448.750000000000 2007-07-16 00:00:00
603663 description_3 16.890000 27.000000 456.030000000000 2007-07-20 00:00:00
603663 description_3 16.890000 150.000000 2533.500000000000 2007-07-10 00:00:00
603663 description_3 16.890000 30.000000 506.700000000000 2007-07-03 00:00:00
I'd like there to be only 1 line for every different code with it's description. The idea is that the other rows are dropped and that only the first one remains. The one with the most recent purchase. I tried with GROUP BY but that's probably wrong since you'd have to add all the other columns as well and you end up with the same one. And even with adding a HAVING at the end I can't see how this could be solved
edit: There aren't any actual relationships in the tables (it's ancient you see ...) I'm using SQL 2005 though.
View 3 Replies
View Related
Oct 19, 2006
I know this is a popular problem, but i haven't found an answer to my situation.
I have a table (myTable) with three Columns (Column1, Column2, Column3). Column1 entries are unique (with unique identifier), Column2 entries are not unique.
I want to do a SELECT DISTINCT on myTable so that Column2 is unique and i get all the other columns. I thought something like this would work
SELECT DISTINCT Column2 as Col2, newid() as Col1, Column3 as Col4
-- I am getting uniqueidentifier for each row as well
This doesn't seem to work however. I still get all the rows because Col1 is unique.
since i am using aliases, the usual syntax doesn't work either.
any help would be greatly appreciated!
View 2 Replies
View Related
May 16, 2005
If I try to run the code below, and even one of the values in the INNER
JOIN statements is NULL, the DataReader ends up with zero rows.
What I need is to see the results even if one or more of INNER JOIN
statements has a NULL value. For example, if I want info on
asset# 2104, and there's no value in the DriverID field, I need the
rest of the data to display and just have the lblDriverName by
blank. Is that possible?
<code>
Sub BindSearchGrid()
Dim searchUnitID As String
Dim searchQuery As String
searchUnitID = tbSearchUnitID.Text
lblIDNum.Text = searchUnitID
searchQuery = "SELECT * FROM Assets " & _
"INNER JOIN Condition ON Condition.ConditionID = Assets.ConditionID " & _
"INNER JOIN Drivers ON Drivers.DriverID = Assets.DriverID " & _
"INNER JOIN Departments ON Departments.DepartmentID = Assets.DepartmentID " & _
"INNER JOIN AssetCategories
ON AssetCategories.AssetCategoryID = Assets.AssetCategoryID " & _
"INNER JOIN Store ON
Store.[Store ID] = Assets.StoreID WHERE RTRIM(Assets.[Unit ID]) = '"
& searchUnitID & "'"
Dim myReader As SqlDataReader
myReader = Data.queryDB(searchQuery)
While myReader.Read
If
Not IsDBNull(myReader("Store Name")) Then lblStrID.Text =
myReader("Store Name")
If
Not IsDBNull(myReader("AssetCategory")) Then lblAsstCat.Text =
myReader("AssetCategory")
If
Not IsDBNull(myReader("Condition Description")) Then lblCondID.Text =
myReader("Condition Description")
If
Not IsDBNull(myReader("DepartmentName")) Then lblDepID.Text =
myReader("DepartmentName")
If
Not IsDBNull(myReader("Unit ID")) Then lblUnID.Text = myReader("Unit
ID")
If
Not IsDBNull(myReader("Year")) Then lblYr.Text = myReader("Year")
If
Not IsDBNull(myReader("Make")) Then lblMk.Text = myReader("Make")
If
Not IsDBNull(myReader("Model")) Then lblMod.Text = myReader("Model")
If
Not IsDBNull(myReader("Mileage")) Then lblMile.Text =
myReader("Mileage")
If
Not IsDBNull(myReader("Vin Number")) Then lblVinNum.Text =
myReader("Vin Number")
If
Not IsDBNull(myReader("License Number")) Then lblLicNum.Text =
myReader("License Number")
If
Not IsDBNull(myReader("Name")) Then lblDriverName.Text =
myReader("Name")
If
Not IsDBNull(myReader("DateAcquired")) Then lblDateAcq.Text =
myReader("DateAcquired")
If
Not IsDBNull(myReader("DateSold")) Then lblDtSld.Text =
myReader("DateSold")
If
Not IsDBNull(myReader("PurchasePrice")) Then lblPrPrice.Text =
myReader("PurchasePrice")
If
Not IsDBNull(myReader("NextSchedMaint")) Then lblNSM.Text =
myReader("NextSchedMaint")
If
Not IsDBNull(myReader("GVWR")) Then lblGrVWR.Text = myReader("GVWR")
If
Not IsDBNull(myReader("GVW")) Then lblGrVW.Text = myReader("GVW")
If
Not IsDBNull(myReader("Crane Capacity")) Then lblCrCap.Text =
myReader("Crane Capacity")
If
Not IsDBNull(myReader("Crane Certification")) Then lblCrCert.Text =
myReader("Crane Certification")
If
Not IsDBNull(myReader("Repair Cost")) Then lblRepCost.Text =
myReader("Repair Cost")
If
Not IsDBNull(myReader("Estimate Replacement")) Then lblEstRep.Text =
myReader("Estimate Replacement")
If
Not IsDBNull(myReader("SalvageValue")) Then lblSalVal.Text =
myReader("SalvageValue")
If
Not IsDBNull(myReader("CurrentValue")) Then lblCurVal.Text =
myReader("CurrentValue")
If
Not IsDBNull(myReader("Comments")) Then lblCom.Text =
myReader("Comments")
If
Not IsDBNull(myReader("Description")) Then lblDesc.Text =
myReader("Description")
End While
End Sub</code>
View 1 Replies
View Related