Hi there, I need an easy solution for the following problem:
I want to join two tables. The first table contains an internal system customer ID and the official customer ID. The second table contains parts of the internal customer ID from table 1 plus the receivers of the invoices for each customer, If necessary. A parent child hierarchy If you'll call it like that. Now, how do I get a result table that puts out the customer ID plus the customer ID of the invoice receiver in a seperate row, like shown in the example result table?
I got assignment, how to make it appear in the right order .
/* DROP TABLE EMP SELECT * INTO Emp FROM ( SELECT 'A' EmpID, NULL ManID, 'Name' EmpName UNION ALL SELECT 'MAC' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT '1ABA' EmpID, 'MAC' ManID, 'Name____' EmpName UNION ALL SELECT 'ABB' EmpID, '1ABA' ManID, 'Name______' EmpName UNION ALL SELECT 'XB' EmpID, 'A' ManID, 'Name__' EmpName UNION ALL SELECT 'BAC' EmpID, 'XB' ManID, 'Name____' EmpName ) b */
Can anyone please help me? I need to create a tree structure of my company's database server. I need to include all the tables and their description in a tree structure. The output can be in any format. Is there some tool in the MSSql that will do this for me, so I don't have to write it out manualy?? Thank you very much for any ideas!! M.
I have the following table Memberid int submemberid int
example MemberId SubMemberId
1 2 2 3 1 4 3 5 2 6 7 8
Each member may supervise more than one member under him (whom I call submember) and then each sub member may also supervise more than one member under him I need to be able to select a specific member for example whose id is 1 and consequently all his sub members should be selected whose also all submembers should be selected and so on I do not know how to do this here is my trial:
ALTER Procedure dbo.MemberReports;1
@MemberMaskId nvarchar(8)
As SET NOCOUNT ON Begin Declare @Exists Int -- Return Value
-- Select * from MembersTree where MemberId = @memId or MemberId = subMemberId
SELECT MembersTree.memberid, MembersTree.submemberid, MembersTree1.memberid FROM MembersTree INNER JOIN MembersTree MembersTree1 ON MembersTree.Submemberid = MembersTree1.memberid
WHERE MembersTree.MemberId = @memId
If @@rowcount = 0 -- No Record returned Select @Exists = 0 Else Select @Exists = 1 Return @Exists
hello i have a table with four fields : name_id, name, tree_id, level withthese data:1 name1 001 12 name2 002 13 name3 003 14 name4 001001 25 name5 001002 26 name6 001003 27 name7 001001001 3tree:1-- 4-7-5-62-3-and procedure which add nodes to tree looks tree.add(idparent,idchild,name)how to write select which returns idparent,idchild and name then i can addnodes in loop reading datareader ?
Hello I have replicated 1 database.Now i need to change the datatype length of one feild from varchar 12 to varchar 20.But when i try to do that it shows error ODBC error !Cannot modify database because it is in replication mode. Can any body help me to sort out this problem. Thx Bilal
The same as following Tree as you can see 1__ | 2__ | 4__ | 6 | __ 3__ 5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that Could any one help me.?
Hi all! I am trying to organize a hierarchical data structure into a table. I need to have the possibility to set 2 parents for some nodes. Curently I see following two options: Example 1 id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1,2 Level 2 Child Example 2 id parent_id name-----------------------------------1 0 Level 1 Parent A2 0 Level 1 Parent B3 1 Level 2 Child3 2 Level 2 Child Is any of the two examples valid database logic wise? In fact, is it possible to achieve the requirement by using only one table? Thanks in advance,
Hai Iam new to SSRS 2005, please help me regarding below Drill Through Report Problem. Assum my problem with below example.Iam tried Hard iam not able to find the solution, Any body please help me.my real time problem is same as below functionality
If Suppose iam dispalying two columns like Country,Department,iam taking these two columns in a table, assume country column having America,south africa, individual america column having Florida state, south africa column contains capetown state, in the preview of the report iam applying drilldown to America column like + America, i need when clicking the America column i want to display Florida state under the column of Country like tree view structure
i need output like this and the same time i want to display other columns those also contain tree view structure assume other one is department column, with contains computers and sales, individualy computers contains HP, Sales contains Bikes
Country Department
+ America + Computers
---Florida -- HP
+ SothAfrica + Sales
---Capetown __Bikes
How to implement above output using drill through functionality, i tried with subreports and used all grouping formats, is possible for display output like above explain the procedure .
Our ERP vendor is helping us migrate from one version of their product to another. In the new version, they have added some columns and changed a few data types in several existing tables.
They sent us a list of the modifications that were necessary, which we did (painstakingly) by hand. But, for other reasons the update failed and we have to start anew.
This got me thinking: is there any way to do this kind of update via a script/stored procedure? I don't have their exact changes handy, but it was stuff like the table "inmast" needs a column of varchar(50) called "lotc" added to it, and the "intime" table needs the datatype of columns "Mon" "Tue" "Wed" changed from text to varchar(50), stuff like that.
SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
[Code] .....
I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output
Now Sql Function can not modify data as Sql Procedure like other database, it's very troublesome at most case! in most case, Sql Function is used to improve code structure then it can be maintanced easy! BUT only because it can not modify data, there are 2 troublesome way:
1. Make all callers in the path from Sql Functions to Sql Procedure. and the coder will cry, the code will become very confusional , and very difficult to maintance.
2. Divide the Sql Function into a thin CLR wrapper to call a Sql Procedure, can only use another connection, BUT can not be in the same transaction context And the code is ugly and slow.
You should not give limitation to Sql Function, should just limit the using context of Sql Function!
The sql code is more difficult to read and maintance than norm code(C#), then the improving code structure and readability should be one of your most important task, that's why microsoft!
Hi,I've just installed SQL server and then IIS and SQLCE tools.I created a virtual directory and was trying to update the NTFSpermissions from SQLServer Connectivity management when i got thefollowing error - Access Control List (structure) invalid.Has anyone come across this and if so, what did u do to fix it.ThanksLyn
I have created 3 views, which I then want to join to produce an overall result. The first view returns customer details, along with payment information. The next two views return values only when the customer has purchased extras outside our standard product i.e. if there is no purchase of an extra, then nothing is written to the extra's table. When I join the views together they only return values where data has been matched in all 3 views i.e. extra's have been purchased. Any data that did not match in all 3 view (i.e. no extra's purchased) is either ignored or dropped from the results. So I need my script to return all values even if no data exists in the two extra views.
My scripts are as follows: Main View SELECT CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, CUSTOMER_POLICY_DETAILS.POLICYNUMBER, --TotalPayable IsNull(SUM(CASE LIST_TRAN_BREAKDOWN_TYPE.IncludeInTotal WHEN 1 THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalPayable, --NetPremium IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'NET' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS NetPremium, --IPT IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Premium_Section_ID, 1, 3) = 'TAX' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS IPT, --Fee IsNull(SUM(CASE ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID WHEN 'FEE' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS Fee, --TotalCommission IsNull(SUM(CASE WHEN SubString(ACCOUNTS_TRAN_BREAKDOWN.Tran_Breakdown_Type_ID, 4, 4) = 'COMM' THEN ACCOUNTS_TRAN_BREAKDOWN.AMOUNT ELSE 0 END), 0) AS TotalCommission
FROM ACCOUNTS_CLIENT_TRAN_LINK INNER JOIN ACCOUNTS_TRANSACTION ON ACCOUNTS_CLIENT_TRAN_LINK.TRANSACTION_ID = ACCOUNTS_TRANSACTION.TRANSACTION_ID INNER JOIN ACCOUNTS_TRAN_BREAKDOWN ON ACCOUNTS_TRANSACTION.TRANSACTION_ID = ACCOUNTS_TRAN_BREAKDOWN.TRANSACTION_ID INNER JOIN LIST_TRAN_BREAKDOWN_TYPE ON ACCOUNTS_TRAN_BREAKDOWN.TRAN_BREAKDOWN_TYPE_ID = LIST_TRAN_BREAKDOWN_TYPE.TRAN_BREAKDOWN_TYPE_ID INNER JOIN CUSTOMER_POLICY_DETAILS ON CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_ID AND CUSTOMER_POLICY_DETAILS.HISTORY_ID = ACCOUNTS_CLIENT_TRAN_LINK.POLICY_DETAILS_HISTORY_ID INNER JOIN SYSTEM_INSURER ON CUSTOMER_POLICY_DETAILS.INSURER_ID = SYSTEM_INSURER.INSURER_ID INNER JOIN SYSTEM_SCHEME_NAME ON CUSTOMER_POLICY_DETAILS.SCHEMETABLE_ID = SYSTEM_SCHEME_NAME.SCHEMETABLE_ID INNER JOIN CUSTOMER_INSURED_PARTY ON ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_HISTORY_ID = CUSTOMER_INSURED_PARTY.HISTORY_ID AND ACCOUNTS_CLIENT_TRAN_LINK.INSURED_PARTY_ID = CUSTOMER_INSURED_PARTY.INSURED_PARTY_ID WHERE CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE = '2007-08-17' AND ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID <> 'PAY'
GROUP BY CUSTOMER_POLICY_DETAILS.POLICY_DETAILS_ID, CUSTOMER_POLICY_DETAILS.HISTORY_ID, CUSTOMER_POLICY_DETAILS.AUTHORISATIONUSER, CUSTOMER_POLICY_DETAILS.AUTHORISATIONDATE, ACCOUNTS_TRANSACTION.TRANSACTION_CODE_ID, CUSTOMER_INSURED_PARTY.SURNAME, SYSTEM_INSURER.INSURER_DEBUG, SYSTEM_SCHEME_NAME.SCHEMENAME, ACCOUNTS_TRANSACTION.Transaction_ID, CUSTOMER_POLICY_DETAILS.POLICYNUMBER
Add on View 1 CREATE VIEW TOPCARDPA AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'TRPCAE01'
Add on View 2 CREATE VIEW TOPCARDRESC AS select policy_details_id, History_id, Selected from customer_addon where product_addon_id = 'HICRESC01'
Join Result Script SELECT TOPCARD.AUTHORISATIONUSER, TOPCARD.AUTHORISATIONDATE, TOPCARD.TRANSACTION_CODE_ID, TOPCARD.SURNAME, TOPCARD.INSURER_DEBUG, TOPCARD.SCHEMENAME, TOPCARD.POLICYNUMBER, TOPCARD.TotalPayable, TOPCARD.NetPremium, TOPCARD.IPT, TOPCARD.Fee, TOPCARD.TotalCommission, TOPCARDPA.SELECTED, TOPCARDRESC.SELECTED FROM dbo.TOPCARD TOPCARD INNER JOIN dbo.TOPCARDPA TOPCARDPA ON TOPCARD.POLICY_DETAILS_ID = TOPCARDPA.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDPA.HISTORY_ID INNER JOIN dbo.TOPCARDRESC TOPCARDRESC ON TOPCARD.POLICY_DETAILS_ID = TOPCARDRESC.POLICY_DETAILS_ID AND TOPCARD.HISTORY_ID = TOPCARDRESC.HISTORY_ID
I have included all the scripts I have used, as others may find them useful, in addition to anyone that is able to provide me with some assistance. Thanks in advance for for the help.
Hi. I'm new to SQL, and need to join 2 tables... any hints??? table1:id (int)title(varchar(50))body(text) table2:id (int)title(varchar(50))body(text) somehow need to get the id, which table the record is from, and the title and body... so if the tables had the information: table1:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body" table2:id title body1 "first title" "first body"2 "second title" "second body"3 "third title" "third body" I would like to get... id table title body3 1 "third title" "third body"3 2 "third title" "third body"2 1 "second title" "second body"2 2 "second title" "second body"1 1 "first title" "first body"1 2 "first title" "first body" Does anyone know how to get this? I am fairly flexible if i need to change things... cheers, eh!
Hello everyone,I'm starting a new project right now and am trying to cut down on the number of stored procedures and tables I'm gonna have to use and I have run into a dead end.Up till now I have been doing the following: Say I had a PRODUCTS table with a DesignId column and ColorId column. I would then create a DESIGN table (Name, Id) and a COLOR table (Name, Id) to INNER JOIN with the two columns in my PRODUCTS table. And the same goes for all my other tables: ORDERS, CUSTOMERS, LINKS etc...... And in the end I would have a lot of tables and stored procedures for these category columns. So I thought, it would be nice to just have a Categories and Subcategories table for all my category columns for the whole website. That way every time I need to define a category column for any table I can simply just add the values to my Categories and Subcategories table instead of having to create a new table for every category column. Everything is fine and dandy except for trying to INNER JOIN these two tables with more than one column. To get values for one column is no problem:<code> SELECT *, _SubCategories.SubCategoryNameFROM _ProductsINNER JOIN _SubCategoriesON _Products.DesignId = _SubCategories.SubCategoryIdWHERE DesignId = COALESCE (@DesignId, DesignId)</code> But how do you INNER JOIN the ColorId column as well. Both DesignId and ColorId values are in my _SubCategories table. In a stored procedure: Is there any way to create a table and columns. Run a loop statement, with one INNER JOIN . Rerun another loop statement with a new INNER JOIN statement? Would that work or does any one else have an idea what would?Thank you guys for the help. It is much appreciated. Alec
Hello all, I have two datatables "customersReached " and "customersGuessed " and I want to combine them into one table only, the problem is that one table exeeded to the other by two fields, so what can I do??????? Mahmoudona
I've been trying to think about how I can do this. I have forums that I have written built around SQL Server. Basicly you have:
-A users Table -A Posts Table -A Replies Table.
Posts and replies have very similar structures. I'd like to be able to merge them and pick out the earliest post for said forum.
1 - is there a way to merge them so that the post date for both the replies and posts tables is contained in 1 column. If not is there a better alternative.
I'd also like to add indexing to the posts so I can do paging. Is there a way for me to add an index number to them while I can sort them anyway i want.
I am using MS SQL Server 2005 on Windows XP with SQL Server Management Studio Express CTP. I am having issues with my query on joining 2 tables I created using BETWEEN to restrict the Salary. Table 1 is called Employee and Table 2 is called Job_title. The column Job_title_code is the only column that is in both tables which is how I am joining both tables. Here is my SQL query:
Code:
SELECT Employee.*, Job_title.*
From Employee
INNER JOIN Job_title
ON Employee.Job_title_code=Job_title.Job_title_code
WHERE Salary
BETWEEN 50000 AND 500000;
The results I am getting back are:
Msg 207, Level 16, State 1, Line 7 Invalid column name 'Job_title_code'.
I can't figure out how to fix this error. I feel like I have tried everything, so any help will be much appreciated. Thank you.
Hi, I have a table with fields as partnerid, contractno. The partnerid field has the Id number which can be a supplier or a customer. I need to get the partner id(supplier) and the partner id (customers) of that particular supplier only. I tried with self join but the data is data is replicating.
Data in table PId ContractNo 20045 1567 435 1567 123 1567 345 1678 1004 1678
I need to display the data in the following format.
jack 20 Melbourne AAA Nick 30 Bendigo BBB Russ 28 Sydney AAA Marty 31 Perth AAA
Table 3
name age city Position
jack 20 Melbourne Manager Nick 30 Bendigo Manager Russ 28 Sydney Clerk Marty 31 Perth Manager
Table 4
name age city datejoined
jack 20 Melbourne 09-09-2001 Nick 30 Bendigo 08-05-2001 Russ 28 Sydney 10-12-2000 Marty 31 Perth 11-11-1999
I want a query which extract the name, age and city from Table 2 (where name,age and city equals table1 values) and position from table3 where position is 'manager' else return null and date joined from table 4 only for the managers else return null.
so the result should be
name age city position datejoined
jack 20 Melbourne Manager 09-09-2001 Nick 30 Bendigo Manager 08-05-2001 Russ 28 Sydney null null
my query
SELECT b. name, b.age, b.city,b.company,c.position,d.datejoined FROM Table1 a, Table2 b, Table3 c, Table4 d WHERE a.age=b.age and a.name=b.name and a.city=b.city and b.age*=c.age and b.name*=c.name and b.city*=c.city and b.position='Manager' and b.age*=d.age and b.name*=d.name and b.city*=d.city
THE RESULT IS
jack 20 Melbourne Manager 09-09-2001 Nick 30 Bendigo Manager 08-05-2001 Russ 28 Sydney null 10-12-2000
When I try to join table4 with table i am getting a exception
Ps: as the original code was in SQL SERVER 6.5 I have to use *= for joins not keywords LEFT JOIN or RIGHT JOIN
I have a bit of an issue that I can not seem to figure out and was hoping to get some feedback/advice from you all.
First a little background. I have two databases and I am adding a new table too one of them. However I need to join the two databases but by columns and the columns I want to use to join them will use different data types and values.
Example database 1 column 1 will be groups.group.id and database 2 column 1 will be users.group.id. However in database 2 (users) the group_id will contain different data.
Database 1 group.id will contain a single integer and database 2 group.id I want to have it contain multiple integers seperated by a comma.
Example code: select groups.group.id, groups.group.name from groups, users where groups.disabled='1' and users.user_id = $user_id and groups.group.id ? users.group.id
The "?" is where I am having trouble. Does anyone know of a way to join two databases by columns using different data types?
Hi, i have some sql experience and can link tables but the link i am trying to get is not displaying how i need it to
here is the code i am using, which display logical results, but not the ones i need :P
qry = "SELECT * FROM wce_contact INNER JOIN wce_mailer_link ON wce_contact.UNIQUEID = wce_mailer_link.Contactid LEFT JOIN wce_mailer ON wce_mailer.uniqueid = wce_mailer_link.mailerid RIGHT JOIN wce_mailer_attachments ON wce_mailer_attachments.uniqueid = wce_mailer.fileid WHERE wce_contact.uniqueid = '"& Request.QueryString("id") &"'"
Ok i have these tables
wce_contact This has the contacts name and address
wce_mailer This holds the details of the mailer and a link to the wce_mailer_attachments, there would be multiple rows in wce_mailer_attachments table which link to 1 row in wce_mailer.
wce_mailer_link This holds the wce_contact uniqueid, and the wce_mailer uniqueid. there will be many contacts to many mailers
wce_mailer_attachments This holds an individual row for one attachment, but the uniqueid would be the same for multiple rows, Dependant on how many attachments the users adds. i.e. one mailer could have several attachments, they would all have the same uniqueid.
Basically the results i am getting using the join i built are displaying each attachment as a separate row when i display the mailers assigned to a contacts record. i need them to display in one single row where the uniqueids are the same in the wce_mailer_attachments and they match the only fileid in wce_mailer.
Hi all! I have a problem joining three tables. My tables are: rooms: room_id ¦ room_name computers: computer_id ¦ computer_name ¦ room_id bookings: booking_id ¦ computer_id ¦ date
I want to join them so that i get a listing that displays all room names, and if there is a booking for any computer in that room I also want to display the computer name and the booking date next to the room name. If one room has bookings for several computers I would like to display that room several times.
I have gotten a correct result with:
SELECT rooms.room_name, t.computer_name, temp.date FROM rooms LEFT JOIN (SELECT computers.room_id, computers.computer_name, bookings.date FROM computers JOIN bookings ON (bookings.computer_id = computers.computer_id)) AS temp ON (rooms.room_id = temp.room_id)
but I would like to use something shorter like: SELECT rooms.room_name, computers.computer_name, bookings.date from computers INNER JOIN bookings ON (computers.computer_id = bookings.computer_id) RIGHT JOIN rooms ON (computers.room_ID = rooms.room_ID)
That however gives me a list where every combination of rooms and computers is listed and bookings displayed when there is a match. I don't really get why this doesn't work and I've been staring at it for days. Could somebody help me out? (I'm using PHP and MySQL)
Let say i have 3 tables (tblA, tblB, tblC). I want to get all data in tblA. At the same time, i want to join the tables. If value in column aStatus equal to 1, it will get the value from tblB. If the column value is 2, it will refer to tblC. Else, if it 0, no need to refer to any table. just like inner join.. but i dun get the logic of how im going to do that.. can someone advise me on how to do the joining? please refer to picture below.
Not sure if this as straight forward as it sounds!
Everytime I create new fields on our DB it creates new tables(our customer units have changed). What I need to do is pull the customer units from both tables into the same column. Is there a way to do this?
