i have two tables one with id and one with storyid which match. i need to check which record is at top of table two. and then pull top four from table one where the top story from table two (storyid) is not one of the top four in table one (id). is this possible? can anyone help?
View 8 RepliesHi, hope someone can help.
I have a table of employees and two sites. Most employees only do shifts at the one site but may occasionally work at the other site. What i want is the details of those who have worked at both sites.
for e.g.
Employees
1 Dave
2 Peter
3 John
Site 1
Mon John
Tue Peter
Wed John
Thu John
Fri Peter
Site 2
Mon Dave
Tue Dave
Wed Dave
Thu Dave
Fri Peter
So the answer should be 2 Peter. Hope this explains the problem. Probably very obvious but it has me stumped.
Thanks
I have a table of customer information (ci), but I have to link to another table in order to get the street address (sa).
If I exclude the street address I get 33 results. If I make the link to street address I will only get 30 results.
What I found is that if the ci table field for country is blank and I try linking to sa table then that result will not be shown.
Is there a way to get all 33 results while linking the two tables?
Hello, Everyone
I had 2 tables contain data:
1) old_data: 601,195 records
firstname
lastname
address
city
state
zip
zip4
2) current_data: 410,185 records
firstname
lastname
address
city
state
zip
zip4
3)Questions: How I write a query make to delete records? From table 'old_data' delete match records reference to table 'current_data' and keep the remain good records are from 'current_data' table.
****Here's below my test query that came up with if there are any error and please provide me any suggestion or new query. Very important task ....Thanks you to all.
/*******
Delete current_data
From old_data Cross Join current_data
Where old_data.FirstName = current_data.FirstName and old_data.LastName = current_data.LastName
and old_data.Address = current_data.Address
********/
RV
I have a table variable @tbl with one column (user) with example Laura, Scott, and Kevin.
Then I have a table usergroups with two columns (groupid, user) with example:
1, Laura
1, Scott
2, Laura
2, Scott
2, Kevin
3, Laura
3, Kevin
3, Scott
3, Jim
4, Laura
4, Kevin
4, Scott
I want to find groupid 2 and 4 because they exactly match the content of @tbl.
Hi All,I need to link two tables using partial word match.How can I write a SQL statement to do so? (I am using MS-Access.)Table One: [Table Name: tblStreet] [Field Name: Street]123 ABC Street124 ABC Street125 ABC Street1 XYZ AVE2 XYZ AVE3 XYZ AVE10 CBS Street11 CBS Street12 CBS Street100 Apple Road101 Apple Road102 Apple RoadTable Two: [Table Name: tblWord] [Field Name: Word]ABCCBSThe output should be:123 ABC Street124 ABC Street125 ABC Street10 CBS Street11 CBS Street12 CBS StreetCould this be done?Thank you in advanced.- Grasshopper -
View 2 Replies View RelatedI have two table People and Employee, both have firstname and lastname as fields
I want to display only the names that don't match on firstname and lastname
CREATE TABLE #NAME1(FULLNAME VARCHAR (100))
INSERT INTO #NAME1(FULLNAME) VALUES('JOHN X. DOE')
INSERT INTO #NAME1(FULLNAME) VALUES('FITZGERALD F. SCOTT')
CREATE TABLE #NAME2(LASTNAME VARCHAR(25), MI VARCHAR(2), FIRSTNAME VARCHAR(25))
INSERT INTO #NAME2(LASTNAME, MI, FIRSTNAME) VALUES('DOE', 'X', 'JOHN')
INSERT INTO #NAME2(LASTNAME, FIRSTNAME) VALUES('FITZGERALD', 'F SCOTT')
My task is to find matches between these two tables on "name."
There are two tables
TABLE 1 (NAME - Groupseats)
id session course groupcode sub1 sub2 sub3
1 2015 ba1 137 HL EL Eco
2 2015 ba1 138 EL SL HS
3 2015 ba1 139 SL EL His
From this table i use to admit a student and select their choice of group simultaneously all the subjects associated with GROUP is save on another table.
Here is the TABLE 2 Structure and sample data:
table 2 (NAME - tblstudetail)
id studentID session course sub1 sub2 sub3
1 15120001 2015 ba1 EL SL HS
2 15120002 2015 ba1 HL EL Eco
3 15120003 2015 ba1 SL EL His
4 15120004 2015 ba1 HL EL Eco
AND so no..........................
Now i just want to COUNT the Number of Groups Filled in tblStudateil.
The database has approx. 2500 temporary tables. The temp tables match a pattern such as APTMP... I tried deleting the tables in SSMS with the statement, Delete from Information_Schema.tables where substring(table_name,1,5) = 'APTMP' This returns the error message"Ad hoc updates to system catalogs are not allowed".
What is the correct way to delete a group of tables whose name match a pattern from within SSMS?
Does that make sense? I have 2 tables, one is an older version, one newer. The newer version has some additional fields, indexes, etc. I'd love to have a tool to run against them that would generate an ALTER TABLE script to modify the old table so that it matches the new table.
Is there anything like that out there?
I have a table having single column id and n number of rows and 1 want to know all combination of id's where sum or subtraction of any two id's is equal to 7.
id
1
2
3
4
5
6
9
12
18
..
..
so output should be like
2 5
3 4
17 10
.......
Hello,
I am writing a SP, inside the SP i have a select statement.
I also have another SP, when i run it, it sends a list back with all the users that are currently online.
Now in my first SP, i want to construct a select statement that returns a users data, but only for the users that are online.
This is the first SP (GetAllOnlineUsers), that produces a list with users online (just abit rewritten from the membership function total users online):
SELECT u.Username FROM dbo.aspnet_Users u(NOLOCK), dbo.aspnet_Applications a(NOLOCK), dbo.aspnet_Membership m(NOLOCK) WHERE u.ApplicationId = a.ApplicationId AND LastActivityDate > @DateActive AND a.LoweredApplicationName = LOWER(@ApplicationName) AND u.UserId = m.UserId
This is my second SP, in witch i want to get the online users data:
SELECT m.username, d.data1, d.data2 FROM m.userstable INNER JOIN data ON (data.username = m.username) WHERE (m.username IN (EXEC GetAllOnlineUsers))
Is it possible to exec a SP into a select statement?
It seems my sysindexes table is inaccurate on a nonclustered index. In my case the rowcount (rows and rowcnt) do not match the actual rowcount of the table. The command UPDATE STATISTICS doesn't change the rows or rowcount, adding 'FULSLCAN' won't budge rowcount either.
After I did a dbcc reindex, the number of rows matched, however, upon adding rows in the table both rows and rowcount are out of sync again.
It's a fairly straightforward table, no triggers, no computed fields, only integer, datetime, varchar and bigint columns. There's a clustered index on a bigint column and a nonclustered index on a integer column.
dbcc show_statistics show that the nonclustered index is updated and it's rows and rows sampled match the number of rows in the table (not in the sysindexes-table).
I'd like to know if I'm chasing ghosts here or if there's something very wrong here. What could be causing the counts being inaccurate? Anyone who could shed some light?
I have this sql statement:
SELECT Countries.Name, Companies.ShortName, Persons.FirstName, Persons.LastName, PersonSkills.Skills
FROM PersonSkills INNER JOIN....
How can I choose a certain value from PersonSkills.Skills?
for example, I would like to choose a value from PersonSkills.Skills that matches a certain product. I can do the Max/Min value but the selected value needs to match the product.
The tables that I have are:
PersonSkills:
id, ProductID, Skills
Product:
id, ProductName
Ive been reading and playing around with this without success.
I want to select Property names or owner id from table A where there is no match between owner ID in table A and B , for example Property 3 in red. In table A only one person can be listed as owner of property. In table B many people can be listed as owner of the same property.
The way i script will return property 1 twice (as no match) because the owner ID from table A '123456' mismatch twice with owner ID from table B
'111111' and '222222'. I dont want Property 1 to be selected at all because the owner '123456' is listed as one of the owners in Table B
I have two tables in the same DB, one named “Client” one named “Case”.
-One column inside Client is “ClientID”, another is “ClientCode”.
-One column inside Case is “CaseID” and another is “ClientCode”, which corresponds to the ClientCode column in the previous table.
I need a file with output that looks like this CaseID+ClientID.
So, an example row from the “Client” table” could be ClientID = 123, ClientCode=999. An example row from the “Case table” could be CaseID=555, ClientCode=999. So, I need output of 555123 .
Scenario: 2 sets of data
Set 1 (200 records): firstname, lastname, address, city, phone
Set 2 (100 records): firstname, lastname, address, city, phone
I run a query using phone as criteria/condition (where xx.phone=yy.phone). I got 75 matches based on the phone numbers
However, if I deleted the phone numbers from set 2 data, used below query, the return result is only 45 matches. My question is what technique that I can use to optimize the result just based on criteria like first, last, and address.
select xx.firstname, xx.lastname, yy.Firstname, yy.Lastname, xx.address, yy.address, xx.city, yy.city, yy.phone
from set2 xx, set1 yy where
substring(soundex(xx.Firstname),0,3)=substring(soundex(yy.Firstname),0,3)
and substring(soundex(xx.lastname),0,4)=substring(soundex(yy.Lastname),0,4)
and substring(xx.address,1,7)= substring(yy.address,1,7)
and xx.city=yy.city;
Thank you
id....
In the trading (stock market) industry there is a practice of rolling up (merging) multiple trades into a single trade in an effort to save on ticket charges. The way this is done is performing a SUM() on the quantities and calculating an average price. (Average price is the SUM(Qty * Price) / SUM(Qty).
So, given :
Qty Price
20 $5
20 $10
You get:
40 $7.5 -- 20 + 20 and SUM(20 * $5, 20 * $10) / SUM(20 + 20)
Here is my dilema: If given a set of trades, I need to loop through them and check every combination to determine which one matches the expected rolled-up final trade. In other words,
If I know that the final trade is:
15 $10
And I have the following trades in my set:
TradeId Qty Price
1 10 $10
2 7 $20
3 5 $10
I need to check the roll-up of trades (1, 2), (1, 3), and (2, 3) and determine that it final trade was made by rolling up trades 1 and 3.
In the real situation, the number of trades that I need to check is not set to a specific number.
Any help would be appreciated. Cursors, temp tables, functions, recursive calls, .NET (I am running SQL 2005 so have access to CLR) are ALL acceptable solutions...
Here is a sample SQL code (table and data) to work with.
USE [tempdb]
DROP TABLE [Trades]
GO
CREATE TABLE [Trades] (
[TradeId] INT,
[Quantity] INT,
[Price] DECIMAL(6,2)
)
GO
-- need to find trades that rollup to quantity 30 and average price 7.5
INSERT INTO [Trades] VALUES (1, 10, 10)
INSERT INTO [Trades] VALUES (2, 10, 5.0)
INSERT INTO [Trades] VALUES (3, 25, 7.5)
INSERT INTO [Trades] VALUES (4, 10, 10.0)
INSERT INTO [Trades] VALUES (5, 2, 2.0)
INSERT INTO [Trades] VALUES (6, 10, 7.5)
SELECT
[TradeId],
[Quantity],
[Price],
[Quantity] * [Price] AS [MarketValue]
FROM
[Trades]
-- need to find the trades that roll up to quantity 30 with an average price of $7.5
-- Trades 2, 4, and 6 are the solution
SELECT
SUM([Quantity]) AS [TotalQuantity],
SUM([Quantity] * [Price]) / SUM([Quantity]) AS [AveragePrice]
FROM
[Trades]
WHERE
[TradeId] IN (2, 4, 6)
-- what I need to get back is the SET of rows that make up the rolled trade, so I want to see
SELECT
*
FROM
[Trades]
WHERE
[TradeId] IN (2, 4, 6)
Thank you for any and all help in this.
- Jason
I have a need to execute a query in T-SQL on a numeric field in a SQL table.
However if there is no exact match I'd likea query that will return the row that is just below my value.
As an example if the table has values: 1,2,4,5,7,9, 15 and 20 for instance and I am matching with a varibale containing the value 9 then I'd like it to return that row. however if my variable has the value 19 I want the row containign 15 returned.
Is this possible? Can anyone help me with such a query?
Regards
Clive
Find value match in SQL table
Is there any application that can compare two tables and find the similarities (there is no high rate of exact match on the field values but there are similarities)?
Does anyone know what this error means?
Ambiguous match found.
i'm using sql server 2000
here is my code
Dim con As SqlConnection = New SqlConnection
con.ConnectionString = _
"Data Source=localhost;" + _
"Initial Catalog=registeruser;" + _
"User ID=int422;" + _
"Password=int422"
Dim cmd As SqlCommand = New SqlCommand
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = "SELECT salt,hash FROM users WHERE login_id = '" + user.Text + "'"
Dim rdr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
con.Open()
Dim salt, hash As String
While rdr.Read()
If user.Text = rdr.Item("login_id").ToString() Then
salt = rdr.Item("salt").ToString()
hash = rdr.Item("hash").ToString()
End If
End While
con.Close()
Label1.Text = salt
End Sub
I'm trying to correct an error that a user is getting when running SQL Server 7.0 SP3, Desktop edition/ Windows NT 4.0 SP6. The error says that C:WINNTSYSTEM32odbcint.dll and C:WINNTSYSTEM32odbccp32.dll are different versions. I tried to install MDAC 2.5 sp1 to correct this problem but this message continues throughout the install. This is causing sqlagent service to hang. Any ideas of how to reinstall ODBC components without reinstalling the OS?
View 1 Replies View RelatedHow can I make my search button have the "Any part of field" match as a default? with a simple query...
I have a field in MS access with hundreds of words (cv)... I want to be able to find a word in "Any part of field"
my try:
WHERE ((([cv].[detail cv])=[detail]));
detail is nowhere to find... i am prompt to give a value. fine.but it
equals whole field; detail must be the sole value of the field detail cv...
help!
mchel
I asked this question in the mysql forums, but I am also interested in any info regarding MSSQL. I've seen databases with search systems with the functionality I seek below. I just have no idea how they are doing it. Thank you.
We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.
Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.
Any ideas? or are we stuck with using MSSQL. We know this will work with MSSQL.
Thank you very much. This is a huge problem for us.
Jim
Hello,
I asked this question about mysql on another forum, but I would also like to know about MSSQL. I may need to switch to this platform if Mysql doesn't work. Here's my problem:
Instead of "match", I am looking for a "match any portion" way to do searches with mysql.
We have an mysql inventory database. We want to be able to put in 4984.600 and choose "match any portion" and it finds 4984600 which is in our database.
Does Mysql have a "match any portion" search function? In this case LIKE didn't work which we tried already.
Any ideas?
Thank you very much. This is a huge problem for us.
Jim
Hi,
I am not an expert with SQL2000 and need to create table(s) to match this xml format(if possible):
<?xml version="1.0" encoding="UTF-8" ?>
<MenuData xmlns="http://tempuri.org/MenuDataDataSet.xsd">
<Menu Id="RootMenu">
<Item1 Caption="About TTF FIS" Url="" Target="" Id="AboutItem">
<ChildMenu1 Id="AboutMenu">
<ChildItem Caption="FAQs" Url="Faqs.aspx" Target="_self" Id="FaqsItem" />
</ChildMenu1>
</Item1>
<Item2 Caption="Resource Solutions" Url="" Target="" Id="BizSolItem">
<ChildMenu2 Id="ResourceMenu">
<ChildItemMenu2 Caption="Resource Tracking" Url="" Target="" Id="AccItem">
<ChildMenuChild2 Id="AccMenu">
<ChildItemChild2 Caption="Resource_A" Url="Resource_A.aspx" Target="_self" Id="AItem" />
<ChildItemChild2 Caption="Resource_B" Url="Resource_B.aspx" Target="_self" Id="BItem" />
<ChildItemChild2 Caption="Resource_C" Url="Resource_C.aspx" Target="_self" Id="CItem" />
<ChildItemChild2 Caption="Resource_D" Url="Resource_D.aspx" Target="_self" Id="DItem" />
</ChildMenuChild2>
</ChildItemMenu2>
<ChildItemMenu2 Caption="Engineering" Url="Engineering.aspx" Target="_self" Id="EngItem" />
<ChildItemMenu2 Caption="Design" Url="Design.aspx" Target="_self" Id="MarItem" />
<ChildItemMenu2 Caption="Deployment" Url="Fire.aspx" Target="_self" Id="FireItem" />
</ChildMenu2>
</Item2>
<Item3 Caption="MES Solutions" Url="" Target="" Id="PerSolItem">
<ChildMenu3 Id="PerSolMenu">
<ChildItemMenu3 Caption="MES First Solution" Url="Education.aspx" Target="_self" Id="EduItem" />
<ChildItemMenu3 Caption="MES Second Solution" Url="Household.aspx" Target="_self" Id="HousItem" />
<ChildItemMenu3 Caption="MES Third Solution" Url="PersonalAccidents.aspx" Target="_self" Id="PerAcItem" />
<ChildItemMenu3 Caption="MES Fourth Solution" Url="Protection.aspx" Target="_self" Id="ProtItem" />
<ChildItemMenu3 Caption="MES Fifth Solution" Url="Retirement.aspx" Target="_self" Id="RetItem" />
<ChildItemMenu3 Caption="MES Sixth Solution" Url="Small Commercial Business.aspx" Target="_self"
Id="SmallBizItem" />
<ChildItemMenu3 Caption="MES Seventh Solution" Url="Term Life.aspx" Target="_self" Id="TermItem" />
<ChildItemMenu3 Caption="Others" Url="Yachts.aspx" Target="_self" Id="YacItem" />
</ChildMenu3>
</Item3>
<Item4 Caption="Our Company" Target="" Url="" Id="ComItem">
<ChildMenu4 Id="ComMenu">
<ChildItemMenu4 Caption="Identity" Url="Identity.aspx" Target="_self" Id="IdItem" />
<ChildItemMenu4 Caption="Promise" Url="Promise.aspx" Target="_self" Id="PromItem" />
</ChildMenu4>
</Item4>
</Menu>
</MenuData>
any help appriciated
SQL 2000
I am testing a query for use in Crystal Reports. It was copied from an existing query with the necessary adjustments. The first part of it works correctly;
SELECT NA.*
into #cl_temp
FROM OLT.dbo.NACBTR NA
WHERE NA.CourseCode in ('RGF00001','RGF00002','RGF00005','RGF00006', 'RGF00038','RGF00039','RGF00040','RGF00041','RGF00042','RGF00043') And
NA.completedDate >= '01/01/2006' and
NOT EXISTS (SELECT * FROM hrdw.dbo.E_View EV WHERE NA.ssn = EV.ssn)
but when I add the second line;
select #cl_temp.*,
ISNULL((select 1 from #cl_temp where #cl_temp.coursecode = 'RGF00001'),0) as fire_yes into #oshasafety_temp
I receive the error message:
The column prefix '#cl_temp' does not match with a table name or alias name used in the query.
Any ideas?
Thanks,
Tom
btw, sql newbie.
I have an Access database, that is in connection with sql server.
On my computer with access2007 i have no problems with viewing tables and stuff.
But on other computers with access2003 it gives an error when i use this query:
INSERT INTO [tbl_sap-staffel] ( ItemCode, CardCode, [Amount-0], [Price-0], [Amount-1], [Price-1], [Amount-2], [Price-2] )
SELECT [qry_sap-spp-0].ItemCode, [qry_sap-spp-0].CardCode, [qry_sap-spp-0].Amount, [qry_sap-spp-0].Price, [qry_sap-spp-1].Amount, [qry_sap-spp-1].Price, [qry_sap-spp-2].Amount, [qry_sap-spp-2].Price
FROM ([qry_sap-spp-0] LEFT JOIN [qry_sap-spp-1] ON ([qry_sap-spp-0].ItemCode = [qry_sap-spp-1].ItemCode) AND ([qry_sap-spp-0].CardCode = [qry_sap-spp-1].CardCode)) LEFT JOIN [qry_sap-spp-2] ON ([qry_sap-spp-1].ItemCode = [qry_sap-spp-2].ItemCode) AND ([qry_sap-spp-1].CardCode = [qry_sap-spp-2].CardCode);
It says:quote:
ODBC call failed
[Microsoft][ODBC SQL Server Driver][SQL Server]
The column prefix 'MS1' does not match with a table name or alias name used in the query.
The column prefix 'MS2' does not match with a table name or alias name used in the query.
And it will give this error 6 times.
I dont know what to do.
Can anybody help me?
hi..i'm new in sql progaming,i try to make make a query that in table field "match" return to "1"if no member record in another table and return to "0" if there is anyrecord member :extable member:member idA 12B 14Table Incoming.member note matchC bla..bla 1A bla..bla 0D bla..bla 1...... ....... .....can anyone help me please?D
View 3 Replies View RelatedHi,
Data in my table is loking like this.
InvID
ItemInputDtTime
SrNo
ItemId
Rate
Qty
GroupID
8252
07-04-2008 15:51
1
001138
9.99
1
1
8252
07-04-2008 15:51
2
000009
0.5
1
1
8252
07-04-2008 15:51
3
000016
1
1
1
8252
07-04-2008 15:52
4
000207
NULL
1
1
8252
07-04-2008 15:52
5
000203
NULL
1
1
8252
07-04-2008 15:52
6
001138
11.9
1
2
8252
07-04-2008 15:52
7
000016
1
1
2
8252
07-04-2008 15:52
8
000009
0.5
1
2
8252
07-04-2008 15:52
9
000207
NULL
1
2
8252
07-04-2008 15:52
10
000203
NULL
1
2
8252
07-04-2008 15:52
11
001138
11.9
1
3
8252
07-04-2008 15:52
12
000009
0.5
1
3
8252
07-04-2008 15:52
13
000008
0.5
1
3
8252
07-04-2008 15:53
14
001106
5
1
4
8252
07-04-2008 15:53
15
001000
10
1
5
8252
07-04-2008 15:54
16
001202
10
1
6
8252
07-04-2008 15:54
17
001117
13.9
1
7
8252
07-04-2008 15:54
18
001113
NULL
1
7
8252
07-04-2008 15:54
19
001117
13.9
1
8
8252
07-04-2008 15:54
20
001113
NULL
1
8
8252
07-04-2008 15:54
21
001117
13.9
1
9
8252
07-04-2008 15:54
22
001115
2
1
9
same colored items are grouped by GroupID. Each group contains ItemID, Qty and rate.
How can i compare IteamID, Qty and Rate of each group with other group's ItemID, Qty and rate?
OR
How can i get number of groups with same ItemID, Qty and rate?
All I need to do by T-SQL
Thanx
In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:
Row yielded no match during lookup.
I would really like to have a list of all records that did not match so
that I could send an email of those missing rows
Please give me solution with example
Thanks
I have configured a lookup transformation to 'redirect error' all no-matched rows to a text file using the flat file destination.
Now I want to send the same text file as an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.
Can we use the send email task on eventhandler and invoke the same in case of such error "row yielded no match during lookup" so that we can send the such non matching rows as an email.
Or else any other way to send an email after generating the text file ocntaining the non matching rows.
Please suggest using steps or example