I have been working on a database and I have imported a number of lists into multiple tables.
I am having a lot of trouble writing the SQL code to be able to query across multiple tables. For instance, I want to know if john doe is on all five lists or if he is only on 3 lists (search by student name).
Can someone help me with this?
Luin.
I've been using SQL for a while but I'm kinda stumped as to where to start with this one. I have a search function on my ecommerce site and whatever anyone searches for is being stored in a database.
I need a script that will look at my search entries table and return a list of the most popular search terms.
So go to the table and produce result like
Search Term (count)
Harry Potter (6)
Sherlock Holmes (4)
Garfield (2)
But like I say, I'm a little stumped as to where to even begin with this one.
I have a table that I filled with data imported from another database.
What I need to do is now take this huge table and break apart the information and put it into 5 smaller tables.
So I have a huge insert statement.
I have one main table called Property with two keys. One key is a "Prop_ID" and the other is "owner" where Prop_Id is a automated unique ID. Once the information is inserted into that table, I then get the Unique ID that it was given, and I then used that ID to insert into the other tables.
The problem I am encountering is I keep getting the following error
Violation of PRIMARY KEY constraint 'PK_Prop_Res_Detail'. Cannot insert duplicate key in object 'Prop_Res_Detail'.
The statement has been terminated.
I have an idea what might be going wrong, but I am not sure. What I want to happen is that I want the query to look at the first row of the huge table and then do all 4 of the inserts, and then go to the next row. But I think it is trying to all the inserts into the property table, and then go on to the Prop_Res_Detail table and that is why I am getting that error.
Any help is greatly appreicated.
here is the code..
Code:
CREATE PROCEDURE [dbo].[Insert_Properties]
AS
DECLARE @Prop_ID Int
SET NOCOUNT ON
INSERT INTO Property(Acres,
Assoc_Phone,
Assoc_Cell,
AppraisalForm,
Area,
Assess_Account,
AttachDetach,
Block,
City,
County,
Directions,
DOM,
ER_EA,
FloodZone,
Import_From,
Import_ID,
Insert_Date,
LandSQFT,
LandSQFTDim,
LegalRemarks,
ListAppraiser_ID,
ListAssoc_ID,
ListBroker_ID,
ListDate,
Listing_Office_Remarks,
ListPrice,
Lot,
Map,
Num_Images,
Office_Phone,
Original_ListPrice,
Owner,
Pending_Date,
PhotoName,
PropSubType,
Prop_Type,
Quad,
Remarks,
State,
Status,
StreetDir,
StreetNum,
StreetName,
Township,
UnitNumber,
ZipCode)
SELECT CONVERT(FLOAT(8), Acres),
CONVERT(Varchar(25), Assoc_Phone),
CONVERT(Varchar(25),Assoc_Cell),
CONVERT(Varchar(50), AppraisalForm),
CONVERT(Varchar(10), Area),
CONVERT(Varchar(50), Assess_Account),
CONVERT(Varchar(20), AttachDetach),
CONVERT(Varchar(20), Block),
CONVERT(Varchar(40), City),
CONVERT(Varchar(50), County),
CONVERT(Varchar(1000), Directions),
CONVERT(int, DOM),
CONVERT(Varchar(10), ER_EA),
CONVERT(Varchar(50), FloodZone),
CONVERT(Varchar(20), Import_From),
CONVERT(Varchar(20), Import_ID),
CONVERT(datetime, Insert_Date, 101),
CONVERT(Varchar(20), LandSQFT),
CONVERT(Varchar(50), LandSQFTDim),
CONVERT(Varchar(2000), LegalRemarks),
CONVERT(Varchar(50), ListAppraiser_ID),
CONVERT(Varchar(50), ListAssoc_ID),
CONVERT(Varchar(50), ListBroker_ID),
CONVERT(varchar(11), ListDate),
CONVERT(Varchar(1000), Listing_Office_Remarks),
CONVERT(Varchar(10), ListPrice),
CONVERT(Varchar(20), Lot),
CONVERT(Varchar(10), Map),
CONVERT(Varchar(10), Num_Images),
CONVERT(Varchar(25), Office_Phone),
CONVERT(Varchar(10), Original_ListPrice),
CONVERT(Varchar(50), Owner),
CONVERT(datetime, Pending_Date, 101),
CONVERT(Varchar(50), PhotoName),
CONVERT(Varchar(25), PropSubType),
CONVERT(Varchar(20), Prop_Type),
CONVERT(Varchar(10), Quad),
CONVERT(Varchar(1000), Remarks),
CONVERT(Varchar(25), State),
CONVERT(Varchar(10), Status),
CONVERT(Varchar(4), StreetDir),
CONVERT(Varchar(15), StreetNum),
CONVERT(Varchar(50), StreetName),
CONVERT(Varchar(20), Township),
CONVERT(Varchar(6), UnitNumber),
CONVERT(Varchar(20), ZipCode )
FROM Imported_Closed_Property_From_MLS
SET @Prop_ID = @@Identity
/*Property Res Table */
INSERT INTO Prop_Res_Detail(Prop_ID,
Addition,
Appliances,
Basement_Area,
BasementDesc,
Builder,
Construction,
Cool,
Dining,
District_School,
Energy,
Exterior_Features,
Fence,
Floors,
Foundation,
FP,
FP_Type,
Garage_Attach_Detach,
Garage_Cap,
Handicap,
Heat,
HOA,
HOA_Fee,
HOA_Inc,
HOA_Period,
Inlaw_Plan,
Interior_Features,
Livestock,
Lot_Desc,
Mechanical,
NumLivingArea,
Num_Baths,
Num_Beds,
Num_Levels,
Other_Info,
OvenDesc,
Owner,
Parking,
Patio,
Patio_Dim,
Perc_Basement_Com,
Pool,
Pool_Type,
Prop_Faces,
Range,
RangeDesc,
Remodeled,
Rental,
RentalAmount,
Roof_Type,
Roof_Year,
RoomOther,
Sect,
SQFT,
SQFTSource,
Style,
Tax_Amount,
Tot_Rooms,
UtilityAvailable,
WindowType,
Year_Built)
SELECT @Prop_ID,
CONVERT(Varchar(50), Addition),
CONVERT(Varchar(100), Appliances),
CONVERT(Varchar(25), Basement_Area),
CONVERT(Varchar(100), BasementDesc),
CONVERT(Varchar(50), Builder),
CONVERT(Varchar(50), Construction),
CONVERT(Varchar(20), Cool),
CONVERT(Varchar(10), Dining),
CONVERT(Varchar(60), District_School),
CONVERT(Varchar(100), Energy),
CONVERT(Varchar(100), Exterior_Features),
CONVERT(Varchar(40), Fence),
CONVERT(Varchar(100), Floors),
CONVERT(Varchar(40), Foundation),
CONVERT(Varchar(50), FP),
CONVERT(Varchar(40), FP_Type),
CONVERT(Varchar(50), Garage_Attach_Detach),
CONVERT(Varchar(25), Garage_Cap),
CONVERT(Varchar(20), Handicap),
CONVERT(Varchar(20), Heat),
CONVERT(Varchar(40), HOA),
CONVERT(Varchar(30), HOA_Fee),
CONVERT(Varchar(100), HOA_Inc),
CONVERT(Varchar(20), HOA_Period),
CONVERT(Varchar(20), Inlaw_Plan),
CONVERT(Varchar(100), Interior_Features),
CONVERT(Varchar(40), Livestock),
CONVERT(Varchar(400), Lot_Desc),
CONVERT(Varchar(100), Mechanical),
CONVERT(Varchar(10), NumLivingArea),
CONVERT(Varchar(5), Num_Baths),
CONVERT(Varchar(5), Num_Beds),
CONVERT(Varchar(30), Num_Levels),
CONVERT(Varchar(100), Other_Info),
CONVERT(Varchar(100), OvenDesc),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(100), Parking),
CONVERT(Varchar(25), Patio),
CONVERT(Varchar(50), Patio_Dim),
CONVERT(Varchar(25), Perc_Basement_Com),
CONVERT(Varchar(20), Pool),
CONVERT(Varchar(20), Pool_Type),
CONVERT(Varchar(40), Prop_Faces),
CONVERT(Varchar(20), Range),
CONVERT(Varchar(100), RangeDesc),
CONVERT(Varchar(50), Remodeled),
CONVERT(Varchar(10), Rental),
CONVERT(Varchar(10), RentalAmount),
CONVERT(Varchar(20), Roof_Type),
CONVERT(Varchar(5), Roof_year),
CONVERT(Varchar(100), RoomOther),
CONVERT(Varchar(10), Sect),
CONVERT(Varchar(10), SQFT),
CONVERT(Varchar(50), SQFTSource),
CONVERT(Varchar(100), Style),
CONVERT(Varchar(10), Tax_Amount),
CONVERT(Varchar(5), Tot_Rooms),
CONVERT(Varchar(100), UtilityAvailable),
CONVERT(Varchar(50), WindowType),
CONVERT(Varchar(5), Year_Built)
FROM Imported_Closed_Property_From_MLS
/*Sold Info Table */
INSERT INTO Sold_Info(Prop_ID,
Buy_Pts,
Closed_Date,
Closed_Price,
Closed_Price_SQFT,
COOP_Sales,
Days_On_Market,
InterestRate,
Lender,
LoanAmount,
LoanTerms,
Loan_Years,
Origination_Fee,
Owner,
SellerConcessions,
LoanType,
Sold_Remarks)
SELECT @Prop_ID,
CONVERT(Varchar(10), Buy_Pts),
CONVERT(datetime, Closed_Date, 101),
CONVERT(Varchar(10), Closed_Price),
CONVERT(Varchar(50), Closed_Price_SQFT),
CONVERT(Varchar(50), COOP_Sales),
CONVERT(Varchar(5), DOM),
CONVERT(Varchar(10), InterestRate),
CONVERT(Varchar(50), Lender),
CONVERT(Varchar(10), LoanAmount),
CONVERT(Varchar(50), LoanTerms),
CONVERT(Varchar(10), Loan_Years),
CONVERT(Varchar(10), Origination_Fee),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(100), SellerConcessions),
CONVERT(Varchar(25), LoanType),
CONVERT(Varchar(1000), Sold_Remarks)
FROM Imported_Closed_Property_From_MLS
/*Remarks Table */
INSERT INTO Remarks(Prop_ID,
App_Date,
App_Remark,
Contract_Date,
Inspection_Type,
Owner,
PendingSalesPrice,
PendingSaleComments)
SELECT @Prop_ID,
CONVERT(datetime, App_Date, 101),
CONVERT(Varchar(1000), App_Remark),
CONVERT(datetime, Contract_Date, 101),
CONVERT(Varchar(50), Inspection_Type),
CONVERT(Varchar(50), Owner),
CONVERT(Varchar(10), PendingSalesPrice),
CONVERT(Varchar(1000), PendingSaleComments)
FROM Imported_Closed_Property_From_MLS
GO
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
I have 5-6 table and wanna to make sure if I'm using any of them in my Packages and I do have 100 Packages.shortest way to search these tables in Packages.
View 2 Replies View RelatedI've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...
In this case the best solution is to create an index on each column individually or at least the most used?
I have this query currently:
select updatedb.callref, updatedb.updatetxt, updatedb.udsource, opencall.suppgroup
from updatedb
left join opencall
on updatedb.callref=opencall.callref
where udindex = '0'
and suppgroup = 'SUPPORT'
and (updatetxt like '%' + @Word + '%')
And opencall.status <> '17'This means that when they search for items and they separate each word it is "and" between each one.
They would like it to be more fuzzy with "and" and "or". How can I adapt this?
The following
SearchCondition[] sc = {new SearchCondition() {
Name = "TypeName"
,Values = new string[] {"Report"}
,Condition = ConditionEnum.Equals
,ConditionSpecified = true
}};
catalogItems = ReportService2010.FindItems("/"
,BooleanOperatorEnum.And
,new Property[] {new Property(){Name = "Recursive",Value="True"}}
,sc
);
Returns the following error
System.Web.Services.Protocols.SoapException: The TypeName field has a value that is not valid. ---> Microsoft.ReportingServices.Diagnostics.Utilities.InvalidElementException: The TypeName field has a value that is not valid.
at Microsoft.ReportingServices.WebServer.ReportingService2010Impl.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items)
at Microsoft.ReportingServices.WebServer.ReportingService2010.FindItems(String Folder, BooleanOperatorEnum BooleanOperator, Property[] SearchOptions, SearchCondition[] SearchConditions, CatalogItem[]& Items)
The type appears to be correct. I've tried type of "Folder" and receive the same error.
I have two tables, Personnel and Emails.
Personnel
*per_personnelID (int)
per_name (varchar)
per_office (varchar)
per_cell (varchar)
Emails
*eml_emailID (int)
eml_personnelID (int)
eml_name (varchar)
I can have a user that has multiple emails but I need to return them all in one row in addition to the name, office and phone. And I need to do this regardless of the number of emails they have. Please, can anyone help the newbie?
HiI am working on a site right now. Basicly how it works is they choose from a list of checkboxes what they want to be quized on. This all gets stored in an arrayList. Once they done that they have an option to save there settings so they don't have to go through the list and choose everything again if they want to come back and do that same quiz. So I am trying to take all those options in the arrayList and put them into the database. So I have 4 questions. Question 1.This is how I was thinking my database design should be.This table that all this stuff is going to be inserted into is called QuickLinks. Originally I was thinking of having a PK to FK relationship with the Asp membership table called Membership(or User Table was another posibility). I am now thinking that is a waste of time and pointless. My first reasoning was well this way I can get the UserID from that table but now I realize no I actually need the UserID to be in the QuickLink table before I try to join the two tables together. With joining the 2 tables I don't get anything out of it since if I just do a check if the User is logged in before I insert the data into the database I can just slip the userID in there as well. Then the QuickLink table should have everything it needs. So My question is I am thinking of having the quick Link talbe to operate like this. Say you have 50 items in the array(this is all from the same item). Each item will gets its own row that will have the UserID,CharacterName,CharacterImagePath, QuickLinkName plus the LinkID.So when I need to call that information I just have to look at 2 pieces of information. Find all the rows that have the same QuickLink name and UserID. So is this a good way to store the data? Question 2.Currently right now I have UserID as unquieIdentifer but I am guessing if I have 50 rows with the same UserID it won't like that very much since its not Unquie. Should I just change that to a varChar?LinkID UserID CharacterName CharacterImagePath----------- ---------------------------------------------------------------------------------------------------- ------------- --------------------------------------------------1 15bde69a-c6fe-4159-b60f-405a013ae4c3 hi image2 15bde69a-c6fe-4159-b60f-405a013ae4e2 hi image3 15bde69a-c6fe-4159-b60f-405a013ae4c3 hi imageFind it actually kinda weird that it allowed 2 of the same UserIDs in. Question 3.Right now my primary key is LinkID. I just made that up but right now I have no use for it since I can't think of anything that would be useful to be a primary key but I figured I need a primary key. So does anyone have any ideas if I should leave that as the primary key or not have one? Or what I should do?Question 4I been playing around on a new webpage I made so I don't have so much cludder code to get in the way I don't screw anything up(I just find it sometimes easier to make a watered down version of what I want and get that work before I tackle the big version and have no clue what is going on. I am not sure if this is the best approach). So right now I have text box that I type in the UserID into(I have gotten the part to work where it checks the login of the user and grabs it but I don't want use it for now since it might complicate things a bit).I have then a textbox where you type in the image path and then 2 other textboxes that you type in the characterName. Those 2 textBoxes for the characterName gets stored in array(so it is almost like the real thing I want to do). I then tired to put my code in a for loop and tired to keep inserting it in until everything from the array was out. However it does not work. I get this errorThe variable name '@UserID' has already been declared. Variable names must be unique within a query batch or stored procedure. I am not sure what it means or wants. My code. ArrayList InsertCharcter = new ArrayList();
InsertCharcter.Add(TextBox1.Text);
InsertCharcter.Add(TextBox2.Text);
SqlCommand comm;
string holdInsert = "INSERT INTO QuickLinks (UserID, CharacterName, CharacterImagePath) VALUES (@UserID,@CharacterName,@CharacterImagePath)";
comm = new SqlCommand(holdInsert, getConnection());
Guid userID = new Guid(txtuserID.Text);
comm.Connection.Open();
for (int i = 0; i < InsertCharcter.Count; i++)
{
comm.Parameters.Add("@UserID", SqlDbType.VarChar);
comm.Parameters["@UserID"].Value = txtuserID.Text;
comm.Parameters.Add("@CharacterName", SqlDbType.VarChar);
comm.Parameters["@CharacterName"].Value = InsertCharcter[i].ToString();
comm.Parameters.Add("@CharacterImagePath", SqlDbType.VarChar);
comm.Parameters["@CharacterImagePath"].Value = txtImage.Text;
comm.ExecuteNonQuery(); } comm.Connection.Close(); Thanks
When using SQL Server Management Studio Express I get multiples of all the standard Windows menu items as well as in the dropdowns. I have also experienced this in the Visual Basic 2005 Exprerss edition IDE. What is causing this?
View 10 Replies View RelatedIn a multi-parameters report I want to show the parameter(s) user selected in a text box. How to set this up. For example user selected A, B, C in the parameter dropdown etc in the text box I want to show A, B, C.
Thanks
We are trying to do something basic with SRS but we can't seem to get iit to work. Here's the scenario: We want to send out a monthly report that consists of numerous subreports. Each subreport stands on its own withh its own data source.
On our main report we drag the subreports into position as we want them to display. But when we run the report sometimes we get 2 subreports on 1 page, other times we get 1.... Bottom line is that we want to get 7-8 subreports on one page. Any ideas on what we can do to get everything on one page?
Hi There
This is probably realy simple but since im still a newbie some help would be appreciated. I have a listbox bound to an sqldatasource which has names of people from my sql database employeeDetails table. I simply want to allow someone to select to select multiple names and insert them into another field in my database.
I have this bit workng, ie they can select multiple people on the list, it does insert, however only the first selected name, not the others.Can you please help me out
Kind Regads
Dan
Hi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
Regards
I have table1 with orderID and demographic info.
Table2 with orderID and items.
I would like to have a results display like this:
OrderIDDemographicInfo Item1Item2Item3....ect
One line per order. When I do a join I displaying all items in different rows.
I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.
Checkbox tag:
<asp:CheckBoxList ID="cblGroup" Style="vertical-align: baseline" runat="server" CssClass="chkbox">
</asp:CheckBoxList>
Select Query:SqlCommand cmd = new SqlCommand("select * from AppInvent_Test where Designation= '" + cblGroup.SelectedValue + "'", con);
Looking to write an query that will update a field for multiple items, like 1,500.
something like:
UPDATE INMAST
SET FPRICE = 111.11
WHERE
INMAST.FPARTNO = 'xxx'
only issue I'm having is a need to do a JOIN because there's one more condition that must be met from another table, i've tried this:
SET FPRICE = 111.11
JOIN INVCUR
ON
(inmast.fpartno + inmast.frev)= (invcur.fcpartno + invcur.fcpartrev)
WHERE
INMAST.FPARTNO = 'NRE'
AND
invcur.flanycur = 'TRUE'
but that is giving me an error around the JOIN
I want to search in fulltextindexes for multiple searchterms in multiple columns. The difficulty is:
I don't want only the records with columns that contains both searchterms.
I also want the records of which one column contains one of the searchterm ans another column contains one of the searchterms.
For example I search for NETWORK and PERFORMANCE in two columns.
Jobdescr_________________________|Jobtext
Bad NETWORK PERFORMANCE________|Slow NETWORK browsing in Windows XP
Bad application PERFORMANCE_______|Because of slow NETWORK browsing, the application runs slow.
I only get the first record because JobDescr contains both searchterms
I don't get the second record because none of the columns contains both searchterms
I managed to find a workaround:
SELECT T3.jobid, T3.jobdescr
FROM (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'network*') or CONTAINS(jobtext, 'network*') ) T1
INNER JOIN (SELECT jobid FROM dba.job WHERE contains(jobdescr, 'performance*') or CONTAINS(jobtext, 'performance*')) T2 ON T2.Jobid = T1.Jobid
INNER JOIN (SELECT jobid, jobdescr FROM dba.job) T3 ON T3.Jobid = T1.Jobid OR T3.Jobid = T2.JobId
It works but i guess this will result in a heavy database load when the number of searchterms and columns will increase.
Does anyone know a better solution?
Thanks in advance Bart Rouw
I frequently have the problem where I have a list of items to delete ina temp table, such asProjectId Description------------- ----------------1 test12 test43 test34 test2And I want to delete all those items from another table.. What is thebest way to do that? If I use two IN clauses it will do it where itmatches anything in both, not the exact combination of the two. I can'tdo joins in a delete clause like an update, so how is this typicallyhandled?The only way I can see so far to get around it is to concatenate thecolumns like CAST(ProjectId as varchar) + '-' + Description and do anIN clause on that which is pretty nasty.Any better way?
View 2 Replies View RelatedHi,
I am trying to build search engin with 11 parameters in 4 different tables in the database.
For example:
In search.aspx I have 11 textboxes namely
nameTextbox, phoneTextbox, nationalityTextbox, ageTextbox etc.
And in the result.aspx page I have gridview which post data from the database if the search match.
I wrote this stored procedure. P.S please ignore the syntax.
@name var(30),
@nationality (30),
@phone int,
etc
as
Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
WHERE
name LIKE '%' @name '%'
OR nationality LIKE '%' @nationality '%'
OR phone LIKE '%' @phone '%'
etc
But I got an error when I am trying to execute this code because the nulls values so I wrote
1 @name var(30),
2
3 @nationality (30),
4
5 @phone int,
6
7 etc
8
9 as
10
11
12
13 Select a.UserId, b.UserId, c.UserId FROM Table1 a, Table2 b, Table3 c
14
15 WHERE
16
17 name LIKE '%' ISNULL(@name, '') '%'
18
19 OR nationality LIKE '%' ISNULL(@nationality,'') '%'
20
21 OR phone LIKE '%' ISNULL(@phone,'') '%'
22
23 etc
24
25
Also the error still exist.
What is the best way to search for multiple parameters in multiple tables ?
Thanks in advanced
I have installed the Adobe iFilter 11 64 bit and set the path to the bin folder. I still cannot find any text from the pdf files. I suspect I am missing something trivial because I don't find much when I Bing for this so it must not be a common problem.Here is the code.
--Adobe iFilter 11 64 bit is installed
--The Path variable is set to the bin folder for the Adobe iFilter.
--SQL Developer version 64 bit on both Windows 7 and Windows 8.
USE master;
GO
DROP DATABASE FileTableStudy;
GO
CREATE DATABASE FileTableStudy
ON PRIMARY
[code]....
I have a checkbox list like the one above.
For example, Training OR Production – should include everyone with an Training OR everyone with a Production checked OR everyone with both Training and Production checked. If service AND technical support – just those two options will show – the customer can only have those 2 options selected in their account and nothing else.
Is there an easy way to build the SQL query for this scenario? Any suggestions or tips?
Thank you for any help
I have a table that contains words that will be used to search another table where FullText index has been created on searchable columns. I'm basically trying to run something like this:
SELECT t1.col1, t2.col3
FROM tbl1 t1, tbl2 t2
WHERE CONTAINS (t1.col1, t2.col1)
I know this won't work but is there a way to join these two tables so the words (t2.col1) can be passed as search conditions? There is no common key on both tables so normal join won't work. I'm trying to find a way to pass the search words from one table to another.
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
View 3 Replies View RelatedI'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). In some cases, items can be components of another item (called "Kits"). In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". In our Items table, we have a field called "Parent_Item_Id". Records for Child Items contain the Item Id of the parent. So a sample of my database would be the following:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. So an example of the report would be the following:
Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100
How I can setup my report to group properly?
how to find which items from a list (without using table ) are not in a specific table
('443',
'470',
'878',
'567',
'430'
)
problem is the following query gives what is in the table
select distinct areacode
from area_code
where areacode
in
('443',
'470',
'878',
'567',
'430'
)
tried using count to see 0 but only get 1 .... N
don't want to create a table everytime ... for dynamic list
TIA
We have two tables (one has DTS import of external spreadsheet - the other is the online table)
The scenario is that I need to add items (from the first table) to the second table, when they don't exist. However, it's harder than that - the second table, in some fields has integers (ID field)), referring to another table (let's call it the 3rd table), but the first table has text (which is in the description, in the third table).
I have to run through the first table, row by row, checking the part number if it already exists in the second table - nothing happens, and it goes to the next line. In each line, if the part number does NOT exist, I need to insert the data in that row. BUT - remember that third table, for the item(s) in the first table with text, I need to dynamically query the third table, to get the ID, in order to complete the insert correctly (Select ID from Third Table where Description=@FirstTableFieldText)
I've never done anything like this, and I have no idea how to put this together - especially if it's all in one Stored Procedure. I need some help here on recommended ways to do it (and how)..... Even the base structure (pseudo code- whatever) would help
Hi,
I'm trying to create a resultset with the top 100 customers for the year (based on this year's sales) and for each of these customers, to return 5 top items and their corresponding sales dollars, as well as percentage of total sales achieved by each item. What I'm struggling with is how to return specifically 5 sales items per customer. If I use a temp table or a correlated subquery, what is the exact syntax to use? Any advice will be appreciated. Thanks all :)
hello
i am currently trying to port a "Business Objects" report to SQL Server Reporting Services.
in B.O., there is a possibility to define the order of the table values by previewing the table values and moving them up or down.
Example : the dataset contains data for 3 items, so in the Custom Sort window, I will see these 3 items and I can order them by moving them up or down in a listbox.
is this possible to do in Reporting Services ?
I have the following table
Region Table
ID
ParentID
RegionName
RestaurantTable
ID
RestaurantName
RegionID
What i tried to do is count the number of restaurants by specific regionname. My current query is
Select RegionID, RegionName, count(*) as RestaurantNo
From Region Inner Join Restaurant
On Region.ID = Restaurant.RegionID
However I only get the results below
RegionID RegionName RestaurantNO
1 A1 0
2 A1.1 2
3 A1.2 1
4 A1.3 0
Where A1.1 , A1.2, and A1.3 are children of A1 in Region table
The result is not correct due to A1 should have 3 in RestaurantNo due to it contains A1.1 , A1.2 and A1.3
Could anyone help me to solve this problem.
Thank you
Hey guys,
I have created an asp.net page where users can select multiple items and then submit the form. I would like to return related items back. The catch is, I want to only return items that are related to all of the selected items.
I've created a SQL Procedure that puts each of the inputted item's ItemId in to a temp table, I have a second table called RelatedItems which I use as my junction table that has ItemId, and ReleatedItemId, I then have my Item table that has the data I want to get to (I've excluded this because I have no trouble pulling out data once I have an ItemId)
I can pull out all related ItemIds with a simple join, however I don't know where to start when it comes to pulling out only items related to all ItemIds in the @TempTable.
Any help or suggestions would be great.
Thanks,
Matt