create multiple INNER JOIN on derived tables as I have written below or use a #temp table for all derived tables and use them into JOIN. This below query is also very hard to understand what is going on .
CREATE TABLE #Temp
(
NumPlayers INT,
ModuleID INT,
ClientId INT,
ASF_Version VARCHAR(10),
ASF_VersionHead INT
[code]....
Hi!I want to return a derived table along with 4 simple tables in a storedprocedure as follows:Input parameter: @FtNum (==Order Number, selects one Order and allassociated data)Table 1: OrdersTable 2: ItemsTable 3: InstancesTable 4: StockDetailsDerived Table: for each Item that requires stock items, 1st columnshould receive the ItemNo (from Items), subsequent columns should receive thedetails from StockDetails via the common key field 'StockCode'.I have so far used a 'Fetch' cursor to find all occurrences of a StockCodewithin the Items table, but have been unable to figure out how to first addthe ItemNo into the temporary table.Code is as follows:... build #tmp_StockDECLARE stock_cursor CURSOR FORSELECT StockCode, ItemNoFROM ItemsWHERE FtNum = @FtNumORDER BY ItemNoOPEN stock_cursorFETCH NEXT FROM stock_cursorINTO @StockCode, @ItemNoWHILE @@FETCH_STATUS = 0BEGININSERT INTO #tmp_Stock-- wish to insert ItemNo = @ItemNo here --SELECT *FROM ControlledStockWHERE StockCode = @StockCodeFETCH NEXT FROM stock_cursorINTO @Stockcode, @ItemNoENDOf course there may be a much simpler way to do this!Your help would be greatly appreciated either way.--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200510/1
View 2 Replies View RelatedHello,
I am in the progress of designing a new section of my database and was thinking of creating a hole new database instead of just creating tables inside the database. My question is can you JOIN multiple tables in an SQL Statement from multiple databases. Ie, In the Management program I have a database called 'Convention' and another one called 'Services', inside the two databases there are many tables. Can I link say tblRegister from Convention to tblUser in Services?
Thanks
When I run:
select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
scheme.opheadm.customer, qty_received
from scheme.opheadm join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
where
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'
It gives me the 5 rows that I need to work with, one column is customer (which is giving me customer code) that I want to replace with customer name from another table
So I tried:
select scheme.opheadm.order_no, scheme.porecpm.order_no, delivery_no, invoice_no,
scheme.jcmastm.name, qty_received
from scheme.opheadm
join scheme.porecpm on (ltrim(rtrim(scheme.porecpm.commnt)) like (ltrim(rtrim(scheme.opheadm.order_no)) + '/%'))
join scheme.jcmastm on scheme.opheadm.customer = scheme.opheadm.customer
here
effective_date between '2011-10-01 00:00:00.000' and '2011-10-08 00:00:00.000'
this works with the same 5 rows that i need but loops them through every customer from the table scheme.jcmastm giving me a total of 960 rows not just the 5 that i want to work with. why this is looping?
hi i user this join and i have the answer like this"
select u.userid,
u.user_name,
u.password,
c.code_description as role_code,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active
from [usermaster] u inner join [codeMaster] c
on 'SP'=c.code
where u.userid = '2'
result:
userid user_name password role_code expiry_date
2billgatesbill Supervisor02/06/2007
created_date active
2007-02-06 00:00:00.000 0
so i have to join one more table which has the following records;
select * from HRUser_developerlog
result:
insserted_id user_date table operation userid
101/24/2007 11:47:54 AM usermasterinsert1
11/24/2007 1:02:18 PM usermasterinsert1
111/25/2007 9:26:12 AM usermasterinsert1
122/5/2007 9:56:48 AM usermasterupdate1
122/5/2007 10:23:01 AM usermasterinsert1
122/5/2007 10:23:38 AM usermasterupdate1
122/5/2007 4:10:11 PM usermasterupdate1
22/6/2007 8:53:37 AM usermasterinsert1
22/6/2007 9:48:24 AM usermasterdelete1
so i need to take the user_date using inserted_id and operation so i need the output as follows:
userid user_name password role_code expiry_date user_date
2billgatesbill Supervisor02/06/2007 2/6/2007 9:48:24 AM
for this i tried the following query:
select u.userid,
u.user_name,
u.password,
c.code_description as role_code,
convert(varchar, u.expiry_date,101) as expiry_date,
u.created_date,
u.active,
v.user_date
from [usermaster] u inner join [codeMaster] c inner join [HRUser_developerlog] v
on 'SP'=c.code or u.userid=v.inserted_id and v.operation='delete'
where u.userid = '2'
but i am getting error.can any onre please help me and please give me query please
Hi,
I have two tables, let's say "Main" and "Dictionary".
The Main table has several fields that point to records in the same dictionary table. Because of the multiple joins I couldn't get any results if I use an expression like:
SELECT Main.ID, Dictionary.Text AS Data1, Dictionary.Text AS Data2
FROM Main, Dictionary
WHERE Main.Data1 = Dictionary.ID AND Main.Data2 = Dictionary.ID
What kind of join expression should I use? I have to generate this expression programmatically, so it's quite important to keep it as simple as possible!
Thx!
Örs
I'm trying to join 3 tables:
EMPLOYEE - empid
SKILL - empid, skillid, skill
SKILLOPTIONS - skillid, option
An EMPLOYEE will always have at least 1 SKILL but each SKILL may or may not have any SKILLOPTIONS. I do an INNER JOIN:
EMPLOYEE->SKILL->SKILLOPTIONS but I only get a record if there is actually a SKILLOPTION. I want a record with EMPLOYEE and SKILL even if there are no SKILLOPTIONS. In Oracle it is the (+) symbol in the WHERE statement in conjunction with the JOIN. Am new to this so I'm sure the answer is simple.
I have three tables, Users, DocType and Docs. In the DocType table there are multiple entries for allowed document types, the descriptions and other pertinent data. In the Docs table, there are all manner of documents. In the User table are the users.
The DocType and Docs tables are relational. DocType.ID = Docs.tID
The Users and Docs tables are relational. Users.ID = Docs.uID
Every user is allowed to have exactly one document of each type. Therefore if there are 10 document types in the DocType table, there may be as many as 10 matching documents in the Docs table.
What I need is a single record for each user returning a boolean for each document type, whether or not there is a matching record in the Docs table.
For example, there are 5 document types defined in the DocType table (types 1 - 5), so the DocType table has 5 rows. In the Docs table, there are 23 rows, and in the User table there are 10 rows. Given that each user may have only one of each DocType, there could be a maximum of 50 rows in the Docs table, but there are 23, meaning that on the average each user is missing one document.Now the challenge is to return a table of all the users (10 rows) with a boolean value for each of the rows in DocType (as columns) based on whether there is a value in the Docs table that matches both the DocType and User.
I have 3 tables , Customer , Sales Cost Charge and Sales Price , i have join the customer table to the sales price table with a left outer join into a new table.
i now need to join the data in the new table to sales cost charge. However please note that there is data that is in the sales price table that is not in the sales cost charge table and there is data in the sales cost charge table that is not in the sales price table ,but i need to get all the data. e.g. if on our application it shows 15 records , the sales price table will maybe have 7 records and the sales cost charge table will have 8 which makes it 15 records
I am struggling to match the records , i have also tried a left outer join to the sales cost charge table however i only get the 7 records which is in the sales price table. see code below
SELECT
a.[No_],
a.[Name],
a.[Currency Code],
a.[Salesperson Code],
b.[Sales Code],
[code]....
I have two tables a and b, where I want to add columns from b to a with a criteria. The columns will be added by month criteria. There is a column in b table called stat_month which ranges from 1 (Jan) to 12 (Dec). I want to keep all the records in a, and join columns from b for each month. I do not want to loose any row from a if there is no data for that row in b.
Here is table a:
naics ust_code port all_qty_1_yr all_qty_2_yr all_val_yr all_air_val_yr all_air_wgt_yr all_ves_val_yr all_ves_wgt_yr all_cnt_val_yr all_cnt_wgt_yr all_border_val_yr
11111000 2010 2002 8070569.14298579 0 2335641254.30021 0 0 2335641254.30021 8156408492.66667 0 0 0
11111000 2230 2010 280841.478063446 0 84622385.9133129 0 0 84622385.9133129 299600780.773355 0 0 0
11111000 2410 1401 25735 0 12305667 0 0 12305667 25719794 0 0 0
[Code] ....
and here is table b:
naics ust_code port stat_month Cum_qty_1_mo Cum_qty_2_mo Cum_all_val_mo Cum_air_val_mo Cum_air_wgt_mo Cum_ves_val_mo
11111000 1220 0106 01 2 0 3440 0 0 0
11111000 1220 0107 03 14 0 3442 0 0 0
11111000 1220 0108 09 0 0 0 0 0 0
[Code] ....
I do not know how to have the multiple joins for 12 different months and what join I have to use. I used left join but still I am loosing not all but few rows in a, I would also like to know how in one script I can columns separately from stat_mont =’01’ to stat_month =’12’
/****** Script for SelectTopNRows command from SSMS ******/
SELECT a.[naics]
,a.[ust_code]
,a.[port]
,a.[all_qty_1_yr]
,a.[all_qty_2_yr]
[Code] ....
Output should have all columns from a and join columns from b when the months = '01' (for Jan) , '02' (for FEB), ...'12' (for Dec): Output table should be something like
* columns from a AND JAN_Cum_qty_1_mo JAN_Cum_qty_2_mo JAN_Cum_all_val_mo JAN_Cum_air_val_mo JAN_Cum_air_wgt_mo JAN_Cum_ves_val_mo FEB_Cum_qty_1_mo FEB_Cum_qty_2_mo FEB_Cum_all_val_mo FEB_Cum_air_val_mo FEB_Cum_air_wgt_mo FEB_Cum_ves_val_mo .....DEC_Cum_qty_1_mo DEC_Cum_qty_2_mo DEC_Cum_all_val_mo DEC_Cum_air_val_mo DEC_Cum_air_wgt_mo DEC_Cum_ves_val_mo (FROM TABLE b)
Hello all,
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
Please help :-D
Greetingz,
DJ Roelfsema
I'm trying to join 3 tables in an outer join since I am loosing records that need to be included if I only use an inner join. I am pulling data from an MSDE database using the microsoft query tool.
The problem is that I get the message that I can't use an outer join on a query with more than 2 tables, but that can't be right can it?
I'm a SQL code novice so any help would be greatly appreciated!
SELECT
Article.articleId
, Article.articleName
, Article.articleStatus
, Articlegroup_2.ArticlegroupId
, Articlegroup_2.g2_key
, Articlegroup_2.g2_name
, articleGroup.articleGroupId
FROM
HIP.dbo.Article Article, HIP.dbo.articleGroup articleGroup, HIP.dbo.Articlegroup_2 Articlegroup_2
WHERE
articleGroup.articleGroupId = Article.articleGroupId AND
Article.articleGroupId2 = Articlegroup_2.Articlegroup_2_Id
I'm trying to join 2 tables in an outer join, but MS Query won't let me do this because I have another 2 tables included in an inner join ("only two tables are allowed in an outer join"). I am pulling data from an MSDE database using the microsoft query tool.
I'm a SQL code novice so any help would be greatly appreciated!
Here is my existing SQL query (without the new outer join table):
SELECT
Lead_.Country, Lead_.Company_Name, Employee.Full_Name, Lead_.Rn_Create_Date, Lead_.Marketing_Project_Name, Employee_1.Full_Name, Lead_.Comments
FROM
ProductionED.dbo.Employee Employee, ProductionED.dbo.Employee Employee_1, ProductionED.dbo.Lead_ Lead_
WHERE
Employee.Employee_Id = Lead_.Account_Manager_Id AND Employee_1.Employee_Id = Lead_.Created_By_Employee_Id AND ((Lead_.Market_Segment='new'))
I am trying to pull in columns from multiple tables but am getting an error when I run the code:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "a.BOC" could not be bound.
I am guessing that my syntax is completely off.
SELECT
b.[PBCat]
,c.[VISN] --- I am trying to pull in the Column [VISN] from the Table [DIMSTA]. Current Status: --Failure
,a.[Station]
,a.[Facility]
,a.[CC]
,a.[Office]
[Code] ....
This is so complicated (for me) because I usually only work with single table and simple queries (SELECT, INSERT, UPDATE), but now I am in a situation where I am stuck.
What I am trying to archive is that: when a project manager logged-into his/her account, a grid-view will show a quick overview for all of his/her projects (id, created date, name and how many files are in pending) like below picture:
3 tables will be involved are:
Sample data for manager_id = 11
I tried this query but it not worked, it seems to display all columns right but the COUNT pending files column (assume the manager_id = 11)
SELECT COUNT(file_id) as 'Pending files', projects.project_id, projects.project_name, projects.status, projects.start_date
FROM ((project_manager
INNER JOIN files
ON project_manager.mag_id = files.manager_id AND project_manager.mag_id = 11 AND file_status = 'Pending')
INNER JOIN projects
ON projects.project_id = project_manager.project_id)
GROUP BY projects.project_id, projects.project_name, projects.status, projects.start_date
ORDER BY projects.status, projects.start_date DESC
result of this query:
I am trying to select specific columns from multiple tables based on acommon identifier found in each table.For example, the three tables:PUBACC_ACPUBACC_AMPUBACC_ANeach have a common column:PUBACC_AC.unique_system_identifierPUBACC_AM.unique_system_identifierPUBACC_AN.unique_system_identifierWhat I am trying to select, for example:PUBACC_AC.namePUBACC_AM.phone_numberPUBACC_AN.zipwhere the TABLE.unique_system_identifier is common.For example:----------------------------------------------PUBACC_AC=========unique_system_identifier name1234 JONES----------------------------------------------PUBACC_AM=========unique_system_identifier phone_number1234 555-1212----------------------------------------------PUBACC_AN=========unique_system_identifier zip1234 90210When I run my query, I would like to see the following returned as oneblob, rather than the separate tables:-------------------------------------------------------------------unique_system_identifier name phone_number zip1234 JONES 555-1212 90210-------------------------------------------------------------------I think this is an OUTER JOIN? I see examples on the net using a plussign, with mention of Oracle. I'm not running Oracle...I am usingMicrosoft SQL Server 2000.Help, please?P. S. Will this work with several tables? I actually have about 15tables in this mess, but I tried to keep it simple (!??!) for the aboveexample.Thanks in advance for your help!NOTE: TO REPLY VIA E-MAIL, PLEASE REMOVE THE "DELETE_THIS" FROM MY E-MAILADDRESS.Who actually BUYS the cr@p that the spammers advertise, anyhow???!!!(Rhetorical question only.)
View 1 Replies View RelatedI have to join two tables and i need to fetch All records from @tab2 and only max date record from @tab1 that ID is present in Tab2
1.) @Tab1 have multiple records for each ID
2.) @Tab2 also have multiple records for each ID
3.) Kind of Lef Outer join those tables with ID and take all records from @tab2 and only Max of date from @tab1 and order by ID and Date
Note: @Tab1 always have lesser dates than @tab2 for each ID
Tables looks like as follows
declare @tab1 table (id varchar(3), effDt Date, rate int)
insert into @tab1 values ('101','2013-12-01',5)
insert into @tab1 values ('101','2013-12-02',2)
insert into @tab1 values ('101','2013-12-03',52)
[code]....
In the given ex, ID 103 should not come as it is not present in @tab2, ID 104 should come even it is not present in @tab1 as we ahve to use left outer join Result should like follows.
how to declare multiple derived columns in SSIS Derived Column Task in one attempt.as i have around 150 columns coming from Flat file. I had created the required Expression in Excel and now i want add those in derived column task but its allowing only 1 expression at a time.
View 4 Replies View RelatedHello-
I have a sql query that I am using to populate a datagrid. The problem is one of the tables is a month table. and the other tables are full of data. So there is no common column name to match using a inner join "on".
How do i do this?
Hello all--
I'm trying to run a SELECT on 3 tables: Class, Enrolled, Waiting.
I want to select the name of the class, the count of the students enrolled, and the count of the students waiting to enroll.
My current query...
SELECT Class.Name, COUNT(Enrolled.StudentID) AS EnrolledCount, COUNT(Waiting.StudentID) AS WaitingCount
FROM Class LEFT OUTER JOIN
Enrolled ON Class.ClassID = Enrolled.ClassID LEFT OUTER JOIN
Waiting ON Class.ClassID = Waiting.ClassID
GROUP BY Class.Name
...results in identical counts for enrolled and waiting, which I know
to be incorrect. Furthermore, it appears that the counts are being
multiplied together (in one instance, enrolled should be 14, waiting
should be 2, but both numbers come back as 28).
If I run this query without one of the joined tables, the counts are
accurate. The problem only occurs when I try to pull counts from both
the tables.
Can anyone find the problem with my query? Should I be using something other than a LEFT OUTER JOIN?
Thanks very much for your time,
--Jeremy
I am trying to create multiple tables in a database using a SQL Script. First i want to find all the tables that have an Identity Column as a primary key in the database. Then for all the tables that meet the Identity Column and primary key criteria, I want to create a New Table.
For example if the Orders table has a Identity Column as a primary key, I want to Create a New Table called ProdID_Orders. The ProdID_Orders will have 1 column called ProdID.
If the OrdersDetails table has a Identity Column as a primary key, I want to Create a New Table called ProdID_OrdersDetails. The ProdID_OrdersDetails will have 1 column called ProdID.
If the Employee table has a Identity Column as a primary key, I want to Create a New Table called ProdID_Employee. The ProdID_Employee will have 1 column called ProdID.
so the Create Table Statement for the ProdID_Orders will look like this:
CREATE TABLE [dbo].[ProdID_Orders](
[ProdID] [int] NULL
) ON [PRIMARY]
The Create Table Statement for the ProdID_OrdersDetails will look like this:
CREATE TABLE [dbo].[ProdID_OrdersDetails](
[ProdID] [int] NULL
) ON [PRIMARY]
This sequence will continue for all the tables in the database that have an Identity Column as a primary key. Thanks. I will appreciate some assistance with this.
I've been struggling with this for about 2 weeks now and can't seem to get any further.I have two tables: orders and orders_extended. They can be joined by the common orderid field (example SELECT * FROM orders JOIN orders_extended ON orders.order = orders_extended.orderid WHERE 1=1)I need to create a report that sums the following fields:
SELECT
CONVERT(VARCHAR(12), orderdate, 101) As orderdate
, COALESCE (
CASE WHEN orders_extended.productprice < 0 THEN 'DISCOUNT' ELSE orders_extended.productnumber END
, CASE WHEN orders_extended.productnumber LIKE '%AB%' THEN 'PRODUCTGROUPAB' ELSE orders_extended.productnumber END
, CASE WHEN orders_extended.productnumber LIKE '%CD%' THEN 'PRODUCTGROUPCD' ELSE NULL END
[code]...
What I'm trying to accomplish is to get the total dolloar amount of sales for each day for each payment type on one line per productgroup.
My SQL knowledge is limited to only trial-and-error and on-the-fly learning, sadly. However, I've had past experience with VB and some earlier, more basic programming, so I don't find it that difficult to grasp; it's just rather overwhelming without any formalized training.
I'm trying now to solve a problem that involves a sales history report. Our sales history table lists only records for product lines which a customer has purchased in the past. However, the budgeting team needs a list of each customer with all product lines, even if they've never purchased, and they'd like to include an industry code value that is from another table.
So the way the tables are looking is this:
Sales_History as the main table with the appropriate data in it
Product_Lines as a reference to fill out the main table with non-purchased lines
Accounts as another reference that gives the industry code per the customer ID in the main table''I'm seemingly on the right track with the following, which tries to create a list of all product lines and the records from sales_history which match them:
SELECT sales_history.sah_sort_id,sales_history.sah_sortkey3,sales_history.sah_sortkey2_desc,
prod_lines.pl_prodline_id,prod_lines.pl_short_desc
FROM dbo.sales_history
INNER JOIN prod_lines ON sales_history.sah_sortkey3=prod_lines.pl_prodline_id
[code]...
However, this produces a list of only 21 rows, when there should be 41.
I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.
for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score
and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error
Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)
Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );
I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.
Hi All
I hope someone smart can help me, it would be highly appriciated.
I am developing an SQL Serverdatabase and in on of the tables I need the primary key to consist of two pieces of data.
TblOrders: OrderNum, Orderdate, ....
TblDispatchers: DispatcerID, Dispatcher, DispatcherAddress
The OrderNum field in TblOrders need to be a composite of an AutoNum-field (incrementet by 1) and the DispatcerID from the tblDispatchers.
Can this be done, and how.
Many thanks
Kind regards
Tina
I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as
UserID=1, FName = John,
LName=Abraham and Industry = 2. I have a
Industry table with data as
ID=1 and Name= Sports,
ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
FName =John,
LName = Abraham and
Industry= Film
How to write query?
Hello all,
Being still a relative newcomer to SQL Server (people may say I'm trying to take on too much being somewhat inexperienced once they read about the problem I'm trying to tackle, but alas...) I'm running into the following problem: I need to create tables in my user database on the fly (using Stored Procedures) so that each table can be created many times in the database but only once for every user. The tables should be named something like "username.Table1", "username.Table2" etc. as opposed to "dbo.Table1". I then want to use the stored procedure from .NET/C# in my web application, so that i can create the complete set of usertables for each of my clients.
Now, I tackled the stored procedure part (that is, it creates all the tables I need with all the parameters I want) and am able to use it from my web application (which took some time to learn but proved quite easy to do), but I cannot seem to get it coupled to the current user (instead of the dbo). Every time I trie, the tables are created as dbo.Table1 and when I try to create a new set, it gives a warning ("table with name such and so already exists..."). I made sure to log in as an authenticated user (using forms authentication) before trying to create the tables but this gives the aforementioned result.
What am I doing wrong? I use Visual Web Developer Express, SQL Server 2005 Express and IIS version 5.1
Please help :-D
Greetingz,
DJ Roelfsema
I am a C# developer who was forced to use a DataSet with Infragistics grid instead of the class object I was using as a data source, so that I can implement an AddNew functionality directly in the grid.
In code I would simply add a read only property to do this, and it was much simpler, but if someone could point in the right direction as to can a view do what I want.
The field I want to create is called "Info" short for HasInfo.
Info has 3 values (from an enumeration) NoInfo = 0; HasResume = 1; HasHomeInfo = 2
The business logic for this is if we have a resume, I show a resume icon in the grid, if we do not have a resume, if we have home phone # or mobil # then an icon of phones are shown in the grid.
To find out if a contact has a resume the SQL is going to be something like if exists (select ContactResumeID from ContactResume Where ContactResume.ContactID = Contact.ContactID) then 1; and if that is not true; check if Contact.HomePhone is Not Null Or Empty or Contact.WorkPhone is not null or empty then 2 else 0
I can do this in C# blindfolded, but in SQL I am fish out of water for compound if statements.
The SQL for this view is shown below (creating using SQL Server Gui Selector which I think is real cool).
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[Employees]
AS
SELECT dbo.Company.CompanyID,
dbo.Company.Name AS CompanyName,
dbo.Contact.FirstName,
dbo.Contact.LastName,
dbo.Position.Title,
dbo.Department.DepartmentName,
dbo.ContactPosition.WorkPhone,
dbo.ContactPosition.Extension,
dbo.Contact.ContactID,
dbo.ContactPosition.ContactPositionID,
dbo.Department.DepartmentID,
dbo.Position.PositionID
FROM dbo.ContactPosition INNER JOIN dbo.Contact
ON dbo.ContactPosition.ContactID = dbo.Contact.ContactID INNER JOIN dbo.Position
ON dbo.ContactPosition.PositionID = dbo.Position.PositionID INNER JOIN dbo.Department
ON dbo.Position.DepartmentID = dbo.Department.DepartmentID INNER JOIN dbo.Company
ON dbo.Position.CompanyID = dbo.Company.CompanyID
Thanks,
Flying Elephant Software
OK....I know how to write a query to return for example :
All the people that ordered X and Y
but how do I write one for:
All the people that ordered X but not Y?
Thanks,
Trey
Hi. We have to create an export from our system to be imported into another system. To get the data out we need to create some SQl but we're struggling a bit.
I presently have the following code
SELECT ProSolution.dbo.StudentDetail.RefNo, ProSolution.dbo.StudentDetail.FirstForename, ProSolution.dbo.StudentDetail.Surname,
ProSolution.dbo.StudentDetail.MobileTel, ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID, ProSolution.dbo.Enrolment.CompletionStatusID,
ProSolution.dbo.Offering.Code
FROM ProSolution.dbo.StudentDetail INNER JOIN
ProSolution.dbo.Enrolment ON ProSolution.dbo.StudentDetail.StudentDetailID = ProSolution.dbo.Enrolment.StudentDetailID INNER JOIN
ProSolution.dbo.Offering ON ProSolution.dbo.Enrolment.OfferingID = ProSolution.dbo.Offering.OfferingID
WHERE (ProSolution.dbo.StudentDetail.AcademicYearID = '06/07') AND (ProSolution.dbo.StudentDetail.RestrictedUseIndicatorID = '9') AND
(ProSolution.dbo.Enrolment.CompletionStatusID = '1')
The above code returns the data one line per course but we need it to be one line per student with all their courses on one line too, like follows.
567897 Tom Smith 07111 111111 TCFT1 CKSAN1 DHICS
Can anyone give us any guidance please?
Thanks
Chip
Hi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
This is kind of what I'm trying to do in my MS SQL 2000 query. ShouldI be able to reference s1.col1 inside the 2nd derived table?I'm getting 'Invalid column name col1' and it's coming from the 2ndderived table (I've commented out other refs to just it to check).Maybe I need to use a temp table instead.SELECT s1.col1,(SELECT * FROM(SELECT COUNT(zzz) AS SomeTotalFROM tab1WHERE s1.col1 = zzz)) AS RowCount) /* error here */FROM(SELECT col1 FROM table) AS s1Thank you!
View 1 Replies View RelatedHi,
In the SQL92 Specifications i read the foloowingf statement...
"All base tables are updatable. Derived tables are either updatable or read-only. The operations of insert, update, and delete are
permitted for updatable tables, subject to constraining Access
Rules. The operations of insert, update, and delete are not allowed
for read-only tables."
But i am concentrating on the below line from the above written lines,
"Derived tables are either updatable or read-only."
I want to ask that is derived tables are updatable or not??? if yes then how,???i tried the following querry but its not working...
Code Block
Update (select * from test1) AS de SET id=0
????