Sql Data Join Related Problem
Apr 1, 2008I am using join for 2 tables in one sql data file.
now i want to build relationship between 2 tables of different SQL file is it possible?
ADVERTISEMENT
Related Tables: Help Needed With JOIN Query
Oct 16, 2006Hi Group,My apologies for the lengthy post, but here goes...I have the following tables:TABLE Vehicles([ID] nvarchar(5),[Make] nvarchar(20),[Model] nvarchar(20),)TABLE [Vehicle Status]([ID] int, /* this is an auto-incrementing field*/[Vehicle ID] nvarchar(5), /* foriegn key, references Vehicles.[ID] */[Status] nvarchar(20),[Status Date] datetime)Here's my problem...I have the following data in my [Vehicles] and [Vehicle Status] tables:[ID] [Make] [Model]----------------------H80 Nissan SkylineH86 Toyota Aristo[ID] [Vehicle ID] [Status] [Status Date]----------------------------------------1 H80 OK 2006-10-012 H80 Damage 2006-10-053 H86 OK 2006-10-134 H86 Dent 2006-10-155 H86 Scratched 2006-10-16I need a query that will join the two tables so that the most recentstatus of each vehicle can be determined. I've gotten as far as:SELECT Vehicle.[ID], Make, Model, [Status], [Status Date] FROM[Vehicles] INNER JOIN [Vehicle Status] ON [Vehicles].[ID] = [VehicleStatus].[Vehicle ID]Of course this produces the following results:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline OK 2006-10-01H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo OK 2006-10-13H86 Toyota Aristo Dent 2006-10-15H86 Toyota Aristo Scratched 2006-10-16How do I filter these results so that I get only the MOST RECENT vehiclestatus?i.e:[ID] [Make] [Model] [Status] [Status Date]--------------------------------------------H80 Nissan Skyline Damage 2006-10-05H86 Toyota Aristo Scratched 2006-10-16Thanks in advance,Rommel the iCeMAn*** Sent via Developersdex http://www.developersdex.com ***
View 1 Replies View RelatedPerformance Related Doubt On The Following Items --&&> LEFT/INNER JOIN Vs IN And Cost Of CONVERT
May 28, 2008Hi,
1.
Right now in my queries I am using lots of LEFT Joins and INNER JOINs... and I was suggested to look at 'IN'... But with IN I did face some performance issues previously and stopped using it... but I have got new doubts on which query will give me better performance...
A query using LEFTJoin or a query using IN/NOT-IN
2.
This question is about CONVERT...
I have a stored proc which is used for updating a table... and multiple columns [of the same table] and corresponding values are sent to the proc [only a subset of the columns might be sent for updates everytime and the columns to update is not fixed for each run of the SP]...
I have to construct a UPDATE String out of it using string concatenation to finally be able to use "sys.sp_executesql" on that update statement...
This results in me having to use CONVERT() lots of times... and one of the columns among them on which I am doing a CONVERT is of the type XML...
So the question is as follows...
a. Is it preferrable to construct a single UPDATE statement string and execute it using "sys.sp_executesql"
b. Or Is it preferrable to give multiple UPDATE statments... i.e. one update statement for each column [Depending on whether that column has to be updated for that run or not]
i.e. The question essentially is:
Does a single update query constructed using lots of CONVERTS [Basically on INT and XML types]
give more performance over using multiple UPDATE statments on the table
Or is it the other way round..
Thanks,
Pratap.
Importing Related Data
Jul 19, 2006I need to recreate a SQL 2005 db in a SQL 2000 server. So far I was able to recreate database schema, but I don't know how to import the raw data. I tried copy-pasting table data to the new db using SQL Server Management Studio Express, but there's a problem - the identity columns do not retain the correct values. For example:
Old db table
ID Username
-------------
15 Peter
45 James
When pasting the same data to teh new db, I would get this:
ID Username
-------------
1 Peter
2 James
You can see the ID field values become different (since the ID attribute is an identity and the table in the new db is empty, the db picks the values 1 and 2 for the two records) and now when pasting data into related tables, I get the error because table relations (which are based on identities) do not match. How do I solve this problem and import all the data into the new db while maintaining correct relationships among tables?
What's The Best Method For Updating Related Data?
Jul 31, 2006Can you describe the best (or your preferred) method for updating data held in a related table using Visual Studio 2005 and SQL Server.
For example; if you had a stock control system with the product names and current stock levels in one table and all stock movements in and out held in another table, what is the best, fastest, safest and most reliable method of inserting a stock movement and then updating the current stock level?
I have tried a couple of different methods but would really appreciate a wider range of opinions.
Thanks
INSERING DATA INTO RELATED TABLES
Nov 14, 2007I have a relationship database that contains two related tables. Table1 has the fields Customer_Name, Customer_ID, and products_Purchased. Table2 has the fields Customer_Name and Cutomer_ID. I would like to add a new row to Table1for a new purchase by a cutomer. When I try to add a new value in Table1 I get an error message saying I can't add a value to table1 because it requires a corresponding value in Table2. I am using the following code: '**********************************************
("INSERT INTO [Table1]([products_Purchased], [Customer_ID]) VALUES('" & textBox..Text & "',"
& session("ID") & ")"
' *****************************************
How do I write an INSERT STATEMENT that adds vaules to both tables, so that the code works?
Any HELP would be appreciated.
charlie
Retrieve Data From 3 Tables Which Are Related To Each Other
May 28, 2007Hey,
I want retrieve data from 3 table my tables structure is this
tblUsers
U_ID - Name
3 John
tblGroups
G_ID - Name
5 Admins
6 Moderators
Now I want join some of the users to different groups for example John maby is a member of two groups (Admins and Moderators)
in order to do this I created a new table names tblGroupsUsers
tblGroupsUsers
ID - User_ID - Group_ID
1 3 5
1 3 6
its ok, but Now I don't know how to retrive my users list from database I don't know how to write a wuery for this
I have tried this :
strSQL = "SELECT tblUsers.name, tblUsers.U_ID, tblGroups.G_ID, tblGroupsUsers.Group_ID, tblGroupsUsers.User_ID FROM tblUsers INNER JOIN tblGroupsUsers ON tblGroupsUsers.User_ID = tblUsers.U_ID, tblGroups WHERE tblGroupsUsers.Group_ID = tblGroups.G_ID ORDER BY tblUsers.name ASC;"
Its working withut error but the problem is the results its like this
John
John
its will retrive the username twice , I think its reading based on tblGroupsUsers table because it has two rows ,
help please I need this how can I configure my query to get eache name once
Thanks
Insert Data Into Related Tables
Mar 2, 2008i am using ms sql server 2005 express for the following:
T1 = {t1.c1(PK), t1.c2, t2.c1(FK)}
T2 = {t2.c1(PK), t2.c2}
where T1 and T2 are tables from the same database, and tn.cn are CoulmnN of TableN
T1 and T2 are linked by the relationship t2.c1
i need to know how to add records to T1 and T2 using ms sql.
record for T2 needs to be added first then retreive t2.c1 from T2 to add record to T1 with t1.c1, t1.c2 t2.c1
please help, thanks
Automatic Insert Of Data Into A Related Table
Jul 20, 2006I have two tables. When my user completes an insert of data in table (1), I would like the second "related" table (2) to be automatically populated with defaults. Is this possible?
My logical approach to this is:
1. Build a handler for the OnInsert event of the first table
2. In the handler, call the Insert Command on the SQLDataSource for the second table with the defaults specified in the DataSource.
What I'm not sure how to do is Step 2 or whats the best way. How do I call the Insertcommand programmatically for a DataSource? Or, is there a better way such as some kind of traditional hardwired SQL insert statement like in classical ASP? Or is there a way to programmatically call a stored procedure and if so is the 3rd approach the best way?
How exactly would someone do this best? It seems this would be a rather common thing someone might need to do.
Loading Data In SQL Server To Related Tables (how)
Jul 14, 2004Hi I have a question how to load data to tables linked by Foreign Keys in MSDE/SQL server. Example:
If I have 2 tables linked (by Foreign Key):
One table:
ITEM idITEM NAMEITEM CATEGORY (FK)
1cheese 2
And another:
Category IDCATEGORY NAME
1 household
2 food
3 general
How do I enter the load of data
Do I have to enter it as
1cheese2
or is there some way of entering it as
1cheesefood
TDS wizard does not allow me to transfer to views/querries what I thought would be a normal way as I would enter data to view(relevant to Access's form) and it would update related tables . When I wrote sql to do it it said I can not update my view table as too many tables would be affected(I had lookup tables empty then though)
I am doing it by number using TDS wizard to transfer it directly to the main table but there must be a better way
How To Get Data From All Related Tables Using Stored Procedure
Sep 16, 2005I have a situation where I want to load some entities from one table lets say the table is customers and i would like to load all the customers with first name = dummy, not only this i would like to load all the orders and order details for these specific customers (these are two different separate tables) . I want all this within one stored procedure that return me three results for three different tables. Please tell me whether it is possible and how.
View 1 Replies View RelatedHelp With Pulling Data Related To All Items In A Table
Jan 11, 2006Hey 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
Inserting Data Into Multiple Related Tables
Jun 18, 2007Hey guys up until now i've only inserted data into a single table. Now I have a form that collects information over a span of three forms. Each form has a table related to it and these three tables are related to each other.
What I want to know is:
1)How do you go abouts inserting data into multiple related tables that have constraints on them?
2)Would you use a stored procedure in an instance like this?
3)At what stage would you execute the sql queries. I assume you do this once you have collected all the required information as opposed to: Enter info into form1, submit form1 data to database... enter info into form2, submit form2 data into database etc
Any help would be greatly appreciated!
Say for instance I have three related tables.
table1
------
tbl1_id
tbl1_data1
tbl1_data2
table2
------
tbl2_id
tbl2_data1
tbl2_data2
table3
------
tbl3_id
tbl3_data1
tbl3_data2
table1 has a one-to-many relationship with table2
table3 has a one-to-one relationship with table2
Database Problem Related Clusterd Data
Nov 23, 2005Hello sirWe have a very huge database its around 6 lakh recordsare being stored in it.records are not being a sorted order so we checked allrecord field through clustering option in Sql server.when we usedclustering records are showing in sorted order but speed of database isvery slow .i want to know with clustering is there any implecation in databaseregading performance od data.Can any one give his views to solve thisproblem?and increase the performance of our database.thanks a lot
View 2 Replies View RelatedIdentify Range And Related Data From Look Up Table
Dec 27, 2007I have a query/report that I need to create that needs to look at the size of a company and based on that size apply different rules. I am sure that this is not the only query/report I'll need to do using this and I'm also not so sure that the size ranges won't be changed in the future. Given this, I'd like to store the size ranges in a lookup(global) table. That way, if the ranges ever change I can just alter them in that table and not in all of the queries/reports that use them. What I need to figure out is how to join the live table with the look up table.
Specifically, here is what I have. The look up table would be:
Code Block
CREATE TABLE #gl_sizerange (
glsid int IDENTITY(1,1) NOT NULL,
lowsize int,
highsize int,
sizecat varchar(10),
milestone varchar(25),
days int
) ON [PRIMARY]
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Approach', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Interview', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(1, 24, 'Small', 'Close', 7)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Approach', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Interview', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(25, 99, 'Medium', 'Close', 7)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Approach', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Interview', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Demonstrate', 21)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Negotiate', 14)
INSERT into #gl_sizerange VALUES(100, 499, 'Large', 'Close', 7)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Approach', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Interview', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Demonstrate', 28)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Negotiate', 35)
INSERT into #gl_sizerange VALUES(500, 300000000, 'Super', 'Close', 35)
Then what I have is two more tables, one that indicates the size of the company and another that has the milestone contained within it and I will also calculate how long that milestone has been open and if it is longer than what is in the lookup table for that milestone and size range I need it returned in the report. Here are some quick sample table to represent that data (I've condensed the size and number of tables for the example):
Code Block
CREATE TABLE #en_entity (
enid int NOT NULL,
orgsize int,
) ON [PRIMARY]
INSERT into #en_entity VALUES(1, 5)
INSERT into #en_entity VALUES(2, 18)
INSERT into #en_entity VALUES(3, 24)
INSERT into #en_entity VALUES(4, 25)
INSERT into #en_entity VALUES(5, 47)
INSERT into #en_entity VALUES(6, 101)
INSERT into #en_entity VALUES(7, 499)
INSERT into #en_entity VALUES(8, 500)
INSERT into #en_entity VALUES(9, 10000)
INSERT into #en_entity VALUES(10, 567890)
CREATE TABLE #op_opportunity (
opid int NOT NULL,
enid int NOT NULL,
milestone varchar(25),
daysopen int
) ON [PRIMARY]
INSERT into #op_opportunity VALUES(1, 1, 'Approach', 5)
INSERT into #op_opportunity VALUES(2, 2, 'Interview', 18)
INSERT into #op_opportunity VALUES(3, 4, 'Negotiate', 24)
INSERT into #op_opportunity VALUES(4, 7, 'Demonstrate', 25)
INSERT into #op_opportunity VALUES(5, 7, 'Approach', 7)
INSERT into #op_opportunity VALUES(6, 9, 'Close', 35)
INSERT into #op_opportunity VALUES(7, 8, 'Close', 36)
So, given the sample data, I would expect the results to return me the following opids from the #op_opportunity table because they don't comply with what is in the look up table based on milestone, size and days open: 2,3,4,7
Importing Related Data From 2 Tables Into Sql 2005
Dec 5, 2007Does anyone know how I can do this?
I have 2 tables in an ODBC datasource (INV HEADER) and (INV DETAILS). The relationship on these 2 tables is (INVNUM).
I want to import these tables into SQL 2005 on a nightly basis by date. The problem is the date field is on the (INV HEADER) table and not the (INV DETAILS) table.
Basically I want to import all the (INV DETAILS) rows that have the same (INVNUM) as the (INVHEADER) but don't know how to do this.
I could use a join on the source tables but how would I direct specific columns to 2 different destinations?
Any help on this would be great and appreciated.
Thanks
A Design Related Problem Regarding Filtering Data Pr Month
May 8, 2008Hi all!
I have a problem.. I have a report, with subreports, which does the job it was supposed to do. Unfortunately, subreports does not export to Excel very well, so I have to come up with a solution without the use of subreports.
The scenario (keeping it simple)
I have a list of departments. Each department can have multiple costs attached to it. The cost happens on a specific date. Fairly straightforward and simplified
I want to view each departments costs, sorted by month, like this:
Jan - Feb - March - April....
Dep SUM COST
Dep FOR EACH MONTH
Dep
Dep
...
...
I did this in the old report by passing the department ID and the date interval as parameters to a subreport and have the subreport return the sum of all the fields, which worked fine.
Now that the subreports can no longer be used, I'm struggling on how to sort my output on that date interval. I can group my result pr. department, but how to filter the result so that I get the right numbers in the right month... no luck yet.
As far as I know; I can't use different datasets in one table? I could have made a different dataset for each month...
Anyone know of a way to design this? I'm sure the problem isn't unique.
Does one have to aggregate the sums inside the SQL query instead?
Thanks for any replies!
Regards,
Mats
Accessing Data Related To Current Week From List Of All Weeks
Apr 4, 2007hi friends,
I m new to asp.net... actually i have an application where the data for current week needs to be displayed from all the data present in different weeks.actually i want to retrieve the syllabus of current week from all weeks
i used the below query but itz not working
sqlString = "SELECT DATEPART(ww,sylWeeKID)AS Week FROM SylWeeks WHERE SyllabusIDFK= " & SylId & " GROUP BY DATEPART(ww,sylWeeKID) "
..can anyone suggest me how to retrieve data for current week , for a related item
thanks in advance
i need urgent help.
SQL Server 2012 :: Read XML Data With Related Attributes / Properties
Dec 4, 2013I have a table with one of the column of xml type. the column contains xml like given below. I want to read this xml from the table and show as below with T-sql query
"EmployeeID" "IndustryDome" "description "
Where Description value comes from the value of AllDome/ITEM/Dome /Description whose Dome equals to IndustryDome value
EX:
1166586 3951LX01 Description10
<GetEmployeeDetails xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<return xmlns="http://applications.apch1.com/webservice/schema/">
<EmployeeID>1166586</EmployeeID>
<BankAccounts>
[Code] ....
Power Pivot :: Not Showing Data Because It Is Not Clear How These Fields Are Related
Apr 1, 2015Purpose - relate Server to Software, and Server to App and display it in power view.Current State - I am able to display that Server/Software and Server/App combos separately in power view.  However when I add Server/Software/App it throws the warning as seen below.  This leads me to believe the relationships aren't transitive so to speak.
Supporting Info - v_gs_installedsoftware is the software table. Â It relates to v_r_system which is the server table through resource id. Â The software table can have many rows with the same resource id, however the server table only has unique resourceids (think of it as a server can have many software products installed). Â Table1 is the application table, it relates to the v_r_system table through the resourceid. Â Each server from the server table can have many applications that it actually plays a role in (think of a web server that hosts multiple websites for different business units).
Select Statement That Will Output Related Rows With Different Column Data Per Row?
Apr 27, 2008Is there a way to build a select statement that will output related rows with different column data per row? I want to return something like:
rowtype| ID | value
A | 123 | alpha
B | 123 | beta
C | 123 | delta
A | 124 | some val
B | 124 | some val 2
C | 124 | some val 3
etc...
where for each ID, I have 3 rows that are associated with it and with different corresponding values.
I'm thinking that I will have to build a temp table/cursor that will get all the ID data and then loop through it to insert each rowtype data into another temp table.
i.e. each ID iteration will do something like:
insert into #someTempTable (rowtype, ID, value) values ('A', 123, 'alpha')
insert into #someTempTable (rowtype, ID, value) values ('B', 123, 'beta')
insert into #someTempTable (rowtype, ID, value) values ('C', 123, 'delta')
etc..
After my loop, I will just do a select * from #someTempTable
Is there a better, more elegant way instead of using two temp tables? I am using MSSQL 2005
Transact SQL :: Get DataTypes And Other Schema Related Data Of Columns In A Query
Sep 11, 2015Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the followingÂ
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'
But instead of TABLE_NAME i would like to give a query like
SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].
System.Data.SqlServerCe.SqlCeCommand Has A Bug Related To SqlCeParameter Order That Causes Exceptions.
Jan 11, 2008I came across a frustrating bug last week. Basically, whenever I tried to execute almost any sql query with unnamed parameters (i.e. using "?" instead of "@param_name" in the SQL text), an exception would be thrown.
After trying lots and lots of things and navigating my way through the internals of System.Data.SqlServerCe.dll, I discovered that the method System.Data.SqlServerCe.SqlCeCommand.CreateParameterAccessor(...) has a bug.
The bug is that the private arrays "parameters" and "metadata" are ordered differently, yet at one point in CreateParameterAccessor(...) they are compared using the same index. Here are the two lines:
p = this.parameters[ i ];
and
MetaData info = metadata[ i ]
and then the column data types of "info" & "p" are incorrectly compared in a later method, ValidateDataConversion(...).
So take a step back... how are they ordered differently? From observation, I concluded the following:
The "parameters" array is ordered exactly in the order that the DbParameter's were added to the DbCommand object.
The "metadata" array is ordered according to the column order of the table in the database.
So what causes the exception? Well, CreateParameterAccessor(...) passes data types from two different columns (one type taken from parameters[ i ] and the other from metadata[ i ]) on to SqlCeType.ValidateDataConversion(...). And, of course, if they differ (e.g. one column is of type DateTime and the other is a SmallInt), an exception is thrown.
I've found two workarounds, and both seem to work well.
The first is to name the SqlCeParameters (e.g. "SELECT ... WHERE id=@id"). This causes the buggy branch of code to be completely bypassed.
The second is to add the SqlCeParameters in the exact same order as the columns exist in the table you are accessing. Note, I do *not* mean the order that you select the columns (e.g. "SELECT column1, column2, ..."). I mean the actual order of the columns in the database.
I've included my setup and a stack trace below to help if it can.
My setup is:
.Net CF 3.5
SqlServer CE 3.5
Visual Studio 2008
Deployed to Pocket PC 2003
Here is the stack trace (note the variables passed to ValidateDataConversion):
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeType.ValidateDataConversion(System.Data.SqlDbType expectedType = DateTime, System.Data.SqlDbType actualType = SmallInt) + 0x4a bytes
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.CreateParameterAccessor(System.Data.SqlServerCe.MetaData[] metadata = {System.Data.SqlServerCe.MetaData[34]}) + 0x89 bytes
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.CreateDataBindings() + 0x5e bytes
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan() + 0x16f bytes
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(System.Data.CommandBehavior behavior = Default, string method = "ExecuteNonQuery", System.Data.SqlServerCe.ResultSetOptions options = None) + 0xa7 bytes
System.Data.SqlServerCe.dll!System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery() + 0x7 bytes
Query Not Returning Proper Data (date Related) In 2005 After Upgrade From 2000...
Jun 7, 2007I am sending out an SOS.
Here is the situation:
We recently upgrade to 2005(sp). We have one report that ran fine in 2000 but leaves out data from certain columns (date related) in the results, so we chalked it up to being a non compatiable issue. So, I decided to try and switch the DB back to 2000 compatibility (in our test env) and then back to 2005. After that the report started returning the proper data. We can€™t really explain why it worked but it did. So we thought we would try it in prod (we knew it was a long shot) and it didn€™t work. So the business needs this report so we thought we would refresh the test system from prod, but now we are back to square one. I was wondering if anyone else has heard or seen anything like this. I am open to any idea€™s, no matter how crazy. J The systems are configured identically. Let me know if you need more information.
Thank you. Scott
Copy Rows To The Same Table And Its Related Data In The Other Table
Nov 23, 2007Hi All,
I have 2 tables People & PeopleCosts.
PeopleID in People Table is the primarykey and foreign Key in PeopleCosts Table. PeopleID is an autonumber
The major fields in People Table are PeopleID | MajorVersion | SubVersion. I want to create a new copy of data for existing subversion (say from sub version 1 to 2) in the same table. when the new data is copied my PeopleID is getting incremented and how to copy the related data in the other table (PeopleCosts Table) with the new set of PeopleIDs..
Kindly help. thanks in advance.
Myl
Can Any One Tell Me The Difference Between Cross Join, Inner Join And Outer Join In Laymans Language
Apr 30, 2008Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
Fill Data Grid With Data From JOIN Query
Jul 8, 2013I am working on a school project and have come up against a bit of a sticking point. I am supposed to be creating a very basic OMS, the teacher themselves have said they do not know how to do this (in previous years it has all be done via Access) but apparently I am a lucky one to be doing it in SQL this year.
So I have 2 tables for products in the system
products
+-----------+------------+
|productid |productname |
|Int |varchar(50) |
+-----------+------------+
productdetail
+---------+----------+------------+------+------+
|detailId |productid |description |price |stock |
|Int |Int |Text |Money |Int |
| |FK_From_ | | | |
| |productid_| | | |
| |products | | | |
+---------+----------+------------+------+------+
One of the user requirements of the OMS is to fill a data grid with product name and the product details which I have the query for or rather I have created a view for, which is then queried from a stored procedure.
CREATE VIEW [dbo].[v_stock]
AS SELECT tab_products.productname, tab_productdetails.description, tab_productdetails.image, tab_productdetails.price, tab_productdetails.stock
FROM tab_productdetails INNER JOIN
tab_products ON tab_productdetails.productid = tab_products.productid
The problem I am having is then returning the data from this query into a data grid, I think the reason is because when I attached the stored procedure to a table and then call that procedure via the table adapter there is a mismatch of the schema - specifically the table it is attached to does not contain the column "productName".
I am thinking I need to create a temporary table to fill the data grid with - however, I am not sure how I would create a temporary table.
Is there something I am missing or not done correctly. As far as I can tell the queries work as when I preview them they produce the expected results.
Join Three Data Sets From Different Data Flows Into One Txt File
Mar 9, 2008Hi, I was wondering how it is posible to join three data sets from different data flows into one txt file.
Let's explain a little more:
I have 3 dataflows. Each of them connect to sql server and and by a SQL command, they bring data into SSIS.
Each SQL command differ between them. So each data set have different columns (they dont have the same format). Also the amount of columns differ between each one.
What I need is to join the three data sets into one txt file. How can I do this? It is posible to join them with different data set formats into a txt file?
Is this the best way to join different data? It is better to use as many OLE DB Sources are needed instead of different data flows?
Thanks for your help!
Exporting Data From A Merge Join From One Data Flow To Another
Mar 1, 2006Hi,
Does anyone know if it is possible to point data that underwent the "merge join" transformation (in one data flow) to the following data flow? I don't want to recreate all that merging, sorting and calling the same sources again in the following data flow if the data that I am using exists in the previous data flow. The merged data is simply too big to export to an excel file, so does anyone have any ideas? Thanks!
Newbie-DELETE A Record In A Table A That Is Related To Table B, And Table B Related To Table A
Mar 20, 2008Hi thanks for looking at my question
Using sqlServer management studio 2005
My Tables are something like this:
--Table 1 "Employee"
CREATE TABLE [MyCompany].[Employee](
[EmployeeGID] [int] IDENTITY(1,1) NOT NULL,
[BranchFID] [int] NOT NULL,
[FirstName] [varchar](50) NOT NULL,
[MiddleName] [varchar](50) NOT NULL,
[LastName] [varchar](50) NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED
(
[EmployeeGID]
)
GO
ALTER TABLE [MyCompany].[Employee]
WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID]
FOREIGN KEY([BranchFID])
REFERENCES [myCompany].[Branch] ([BranchGID])
GO
ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch"
CREATE TABLE [Mycompany].[Branch](
[BranchGID] [int] IDENTITY(1,1) NOT NULL,
[BranchName] [varchar](50) NOT NULL,
[City] [varchar](50) NOT NULL,
[ManagerFID] [int] NOT NULL,
CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED
(
[BranchGID]
)
GO
ALTER TABLE [MyCompany].[Branch]
WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID]
FOREIGN KEY([ManagerFID])
REFERENCES [MyCompany].[Employee] ([EmployeeGID])
GO
ALTER TABLE [MyCompany].[Branch]
CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID
--generated IDs = GID
Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee
WHERE EmployeeGID= 39
Well this might look like a very basic error:
I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the
REFERENCE constraint "FK_Branch_ManagerFID".
The conflict occurred in database "MyDatabase",
table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Thanks
Join Into New Data
Jan 19, 2012I need joining 2 tables and putting the result into a new table.
Table export has the following columns:
Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k
Table cc_split has the following columns:
Company, Fun_Account, CC_Account, x, y, Split
The new table called export2 has exactly the same values as export i.e. :
Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k
So what I need to join from export and split into export2 are company, cc_account (that shall go into account column of export2 together with the account of export) and the amount, which shall be ( split column * amount column of export), the rest values shall be the same.
So what I have so far is
Code:
INSERT INTO EXP_DATA_2(PERIOD, ACTUALITY, COMPANY, CURRENCY, ACCOUNT, AMOUNT,
EXTDIM1, EXTDIM2, EXTDIM3,EXTDIM4,JOURNAL_TYPE,C_COMPANY,
COMPANY,C_DIM, TRANAMOUNT, TRANCURR,REGION, TRANSF_DATE)
SELECT COMPANY, CC_ACCOUNT, SPLIT
FROM EXP_DATA INNER JOIN CC_SPLIT ON EXP_DATA.COMPANY=CC_SPLIT.COMPANY AND
EXP_DATA.ACCOUNT = CC_SPLIT.CC_ACCOUNT
WHERE EXP_DATA.PERIOD IN ('1102', '1103', '1104')]
So at the end basically table exp_data 2, shall have the same values as exp_data but the account column shall include the same values of account column of exp_data plus the values of cc_account of cc_split table. And also the amount column shall be the multiplication of the column split from cc_split * amount of exp_data
I have realised that I need an alias, maybe a for exp_data and b for cc_split.
Inner Join Duplicate Data
Jun 13, 2000i have 2 tables not connected in any way
but both have orderid filed (same filed).
In one table this filed (and onther one) are keys,
the second table dose not hace a key at all.
The same order_id CAN repeat itself in each table.
When i try to join the tables (some rows just in one table and some in both):
Select tab1.name, tab1.orderid, tab2.sku
from tab1 inner join tab2 on tab1.orderid=tab2.orderid
The result i get is duplicate.
each row is multiple.
What I'm doing wrong?
Join On Similar Data
Feb 23, 2015Table One is an older database and has the column employee id, which would always consist of first letter of the last name an underscore and a numeric value. So for example, data looks like
employeeID
R_12345678
S_5555555
T_777777
U_7777
Our new data structure simply removed the letter & underscore.
employeeID
12345678
5555555
777777
7777
Now my question is, how could I join on oldDB.employeeID to newDB.employeeID since the data is very similar, but not exactly the same?