Combine Two Table Records By First Name?
Feb 15, 2012
I have 2 tables First is Student_detail and another is Employee_detail. Student_detail have 14 fields like (stud_Firstname,stud_Lastname...) and Employee_detail have 17 fields like(emp_Firstname,emp_Lastname...).there is no relationship between these two table and also not in a relationship with any other table in my database.This is a structure of my db. but i want to get the records from these two table whose first name is same for both the tables.as well as the result of this query will first show me Student_detail record first and then Employee_detail record.but not in a one row.it should be display in one by one.
Like this way:
HTML Code:
Student_detail :-
stud_First_name stud_Last_name std_city ........
Shrikant Joshi Jalgaon ........
Yogesh Trivedi Malkapur ........
Employee_detail:-
emp_First_name emp_Last_name emp_city ..........
Tushar Patil Mumbai ..........
Shrikant Rane Nasik ..........
Result of a query:-
First_name Last_name City ..........
Shrikant Joshi Jalgaon .........
Shrikant Rane Nasik .........
View 14 Replies
ADVERTISEMENT
Nov 8, 2007
I have a SQL table with Sales Order release information.
Following are some records from the table
SMIPartNo
QtyType
PO
POLine
QtyDue
UOM
DateDue
DateDueType
fsono
fcustno
finumber
frelease
fspq
JI-117933A1
Firm
N40136001
234
200
EA
7/2/2007 0:00
SH Ship Date
E00001
20
001
1
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/9/2007 0:00
SH Ship Date
E00001
20
001
2
1800
JI-117933A1
Firm
N40136001
234
400
EA
7/30/2007 0:00
SH Ship Date
E00001
20
001
3
180
If sum(QtyDue) is Less than fspq then read next record, if sum(QtyDue)>= fspq then write the record.
For the above example the output would look like the following
SMIPartNo
QtyType
PO
POLine
QtyDue
UOM
DateDue
DateDueType
fsono
fcustno
finumber
frelease
fspq
JI-117933A1
Firm
N40136001
234
1000
EA
7/2/2007 0:00
SH Ship Date
E00001
20
001
1
1800
Any help would be nice.
Thanks
David Davis
View 2 Replies
View Related
Mar 25, 2004
Here is what I have,
select id, name from rss_user
gives me this
r604738 one
r604738 two
r604738 three
r604739 one
r604739 two
r604739 three
r604739 four
I would like to be able to pipe this into a @temp table so it looks like this,
r604738 one,two,three
r604739 one,two,three,four
Any ideas, so far I am drawing a blank.
View 1 Replies
View Related
Nov 5, 2007
Hello everyone -
This is my first post to the forum and I'm very new to SQL. I apologize if this is addressed elsewhere.
Here is an example of the results I am getting from my query
AdmissionID, sNurseInit, sSWInit
100, {NULL}, SAE
100, REG , {Null}
Is there a way to combine (merge, join? I don't know the right word) these records so that a single record for the admission is returned?
AdmissionID, sNurseInit, sSWInit
100, REG, SAE
Thanks in advance!
Amy
View 20 Replies
View Related
Oct 29, 2006
How to write a sql to combine the 4 tables into one without repetitive records? The 4 tables have exactly the same fields.
The tables do not have primary key. The fields to identiry the rows is name and dob. In the case the name and dob is same for two records, the one with latest date_created is selected.
Thanks
View 9 Replies
View Related
Apr 19, 2004
Hello,
I have a table which has the following structure:
ID MessageText
001 Hello
001 There
001 Working
003 See
003 you
003 Next
003 Time
How to build a query or store procedure to return result like this:
ID MessageText
001 Hello There Working
003 See you Next Time
Your help/advice is greatly appreciated.
Thanks, Ficisa
View 14 Replies
View Related
Dec 5, 2005
I have the following table;CREATE TABLE [x_Note] ([x_NoteId] [int] IDENTITY (1, 1) NOT NULL ,[Note] [varchar] (7200) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_x_NoteId] PRIMARY KEY CLUSTERED([x_NoteId],) WITH FILLFACTOR = 90 ON [USERDATA] ,) ON [USERDATA]GOMy clients want me to take the contents of the Note column for each rowand combine them. In other words, they basically want:Note = Note [accumulated from previous rows] + Char(13) [because theywant a carriage return] + Note [from current record].What is the most efficient and relatively painless way to do this? Ithink it might require a cursor, but I'm not sure if there is a moreelegant set-based method to make this happen.
View 14 Replies
View Related
Jul 11, 2006
Hi all,We have an app that uses SQL 2000. I am trying to track when a code field(selcode) is changed on an order which then causes a status field (status)to change. I tried a trigger but the app may use 2 different updatestatements to change these fields depending on what the user does. When thetrigger fires (on update to selcode), the status field has already beenchanged. So my trigger to record the changes from inserted and deleted donot get the true 'before' value of the status field.The app does use a log table that tracks these changes. The problem I amhaving is that 2 records are created, one for the change to selcode andanother for the change to status.I am looking for help with a script to combine the existence of these 2 logrecords into 1 unique record or occurance that I can track.example:ordlog: table that logs order changesordernr: order numbervarname: name of field being changedold_value: contents of field before changenew_value: contents of field after changesyscreated: date/time of log entrySELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'selcode' and ordernr = '10580'SELECT ordernr, varname, old_value, new_value, syscreatedFROM ordlogwhere varname = 'status' and ordernr = '10580' and old_value = 'A' andnew_value = 'O'So I need a way to combine these 2 log entries into a unique occurance. Theordernr and syscreated could be used to link records. syscreated alwaysappears to be the same for the 2 log entries down to the second. Selcodecan change from NULL to a number of different values or back to NULL.Statusis either 'A' for approved or 'O' for open. An order can have many logentries during its life. The selcode may be changed several times for thesame order.Ideally, I would like a result that links 2 log entries and shows the statuschanged from 'A' to 'O' when selcode changed.Thanks for your time.
View 1 Replies
View Related
Mar 29, 2007
This is how the data is organized:vID Answer12 Satisfied12 Marketing12 Yes15 Dissatisfied15 Technology15 No32 Strongly Dissatisfied32 Marketing32 YesWhat I need to do is pull a recordset which each vID is a single rowand each of the answers is a different field in the row so it lookssomething like thisvID Answer1 Answer2 Answer312 Saitsfied Marketing Yesetc...I can't quite get my mind wrapped around this one.
View 13 Replies
View Related
Sep 28, 2007
I need to return one record with concatenated string fields from a table that may contain several records. I think a cursor will be able to do what I want, but I'm not very experienced at writing them.
My data
HDR DMCD
107 TEX
107 AIR
107 LG
108 TEX
108 CAR
109 SM
I want the result of my query to find adn return each header and return the 1 or more DMCD field values concatenated. i.e.
107 TEX AIR LG
108 TEX CAR
109 SM
This is my attempt at the cursor so far
SET NOCOUNT ON
DECLARE @AACODE varchar(50),@hdr varchar(20),@dmcd varchar(20)
DECLARE AAROW_cursor CURSOR FOR
SELECT aaglhdrid,aatrxdimid
FROM aag30003
OPEN AAROW_cursor
FETCH NEXT FROM AAROW_cursor
INTo @hdr, @dmcd
WHILE @@FETCH_STATUS = 0
BEGIN
set @aacode=@aacode+@dmcd
select @hdr,@dim,@aacode
FETCH NEXT FROM aarow_cursor
INTO @hdr, @dmcd
END
CLOSE AAROW_cursor
DEALLOCATE aarow_cursor
View 5 Replies
View Related
Jun 5, 2014
I'm working on a report where my table is as follows:
WITH SampleData (ID,NAME,[VALUE]) AS
(
SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'DateToday','6/04/2014'
UNION ALL SELECT 170983,'employee','1010'
UNION ALL SELECT 170983,'employee','1010'
[Code] .....
Here is my query against the table above:
SELECT
ID
,MAX(CASE WHEN NAME = 'employee' THEN VALUE END) AS PERSON
,MAX(CASE WHEN NAME = 'DateToday' THEN VALUE END) AS REQUEST_DATE
,MAX(CASE WHEN NAME = 'LeaveStartDate' THEN VALUE END) AS REQUEST_START_DATE
,MAX(CASE WHEN NAME = 'LeaveEndDate' THEN VALUE END) AS REQUEST_END_DATE
,MAX(CASE WHEN NAME = 'HoursPerDay' THEN VALUE END) AS REQUESTED_HOURS
,MAX(CASE WHEN NAME = 'LeaveType' THEN VALUE END) AS REQUEST_TYPE
FROM SampleData
Here is the result from the above query, I'm not sure how to get the desired results (listed at the end):
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
170983NULL6/04/2014NULL NULL NULL NULL
1709831010NULL NULL NULL NULL NULL
170983NULLNULL NULL NULL 8:00 NULL
170983NULLNULL NULL 6/16/2014 NULL NULL
[Code] .....
My Desired results are as follows:
IDPERSONREQUEST_DATEREQUEST_START_DATE REQUEST_END_DATE REQUESTED_HOURS REQUEST_TYPE
17098310106/04/20146/16/2014 6/16/2014 8:00 Personal
17102416/04/20146/17/2014 6/17/2014 8:00 Bereavement
View 2 Replies
View Related
Sep 30, 2015
I have a robust query that returns a dataset and the data is good, however some of the records contain the exact same data with the exception of the 'Price' field. I want to combine the records that are identical and SUM the values in the 'Price' field. My query and example return dataset is below.
Query:
--------
select distinct
'On-Demand' as 'Business Line',
o.OrderID as 'Order #',
isnull(d.DisplayCode,'UNK') as Hub,
isnull(rz.RouteID,'UNK') as 'Default Route',
'On-Demand' as 'Assigned Route',
[Code] ....
View 4 Replies
View Related
Sep 13, 2006
Ok, I'm really new at this, but I am looking for a way to automatically insert new records into tables. I have one primary table with a primary key id that is automatically generated on insert and 3 other tables that have foreign keys pointing to the primary key. Is there a way to automatically create new records in the foreign tables that will have the new id? Would this be a job for a trigger, stored procedure? I admit I haven't studied up on those yet--I am learning things as I need them. Thanks.
View 4 Replies
View Related
Aug 11, 2015
Table1 contains fields Groupid, UserName,Category, Dimension
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1)
and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
View 9 Replies
View Related
Oct 21, 2015
I am trying to write a query that will retrieve all students of a particular class and also any rows in HomeworkLogLine if they exist (but return null if there is no row). I thought this should be a relatively simple LEFT join but I've tried every possible combination of joins but it's not working.
SELECT
Student.StudentSurname + ', ' + Student.StudentForename AS Fullname,
HomeworkLogLine.HomeworkLogLineTimestamp,
HomeworkLog.HomeworkLogDescription,
ROW_NUMBER() OVER (PARTITION BY HomeworkLogLine.HomeworkLogLineStudentID ORDER BY
[Code] ...
It's only returning two rows (the students where they have a row in the HomeworkLogLine table).
View 3 Replies
View Related
Apr 5, 2012
I have write two query but its only work one at a time need your expertise what i am doing wrong.
[Category].Category AS project_type,
[SalesManager].SalesManager As Manager,
FROM [Category] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [Category].ID = Projects.Category
FROM [SalesManager] INNER JOIN (Projects INNER JOIN [MO/FSC] ON Projects.ProjectID = [MO/FSC].Project)
ON [SalesManager].ID = Projects.SalesManagerID
View 2 Replies
View Related
Apr 29, 2015
I want to combine 2 columns from different table.
let said my table1:
id: A1 customername: WesternDigital
id: A2 customername: Sony
id: A3 customername: Samsung
my table2 :
id: A1 customername: Rose
id: A3 customername: John
My output is like that:
customername
WesternDigital, Rose
Sony
Samsung, John
my sql as below:
select table1.customername + table2.customername as customername
from table1
inner join table2 on table1.id = table2.id
how to make the table1 sony appear also even it does not exist in table2?
View 1 Replies
View Related
Feb 16, 2005
Could you write the simple SQL statement from 'Combine two column in one table '?
I try to use 'Union' which combine two column in two table . thx
View 2 Replies
View Related
Jan 9, 2014
I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).
I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..
Here is query #1:
select investor, full_date, month_end_summary, category, loan_count
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) < 6
group by investor, full_date, month_end_summary, category, loan_count
order by investor, full_date
Here is query #2:
select investor, full_date, month_end_summary
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) =0
order by investor, full_date
Can they be combined into one query to produce one result table??
View 3 Replies
View Related
Jan 18, 2004
It's rather easy to combine resultset from the same table structure...we can either insert the entries or union the results.
But let's say you select different columns from different tables and want to combine them to form a new table, how would you do it (assuming you can't join those tables since they are not related), assuming they all return the same number of rows.
select col1 from table1
go
select col2 from table2
go
Now I want to combine them so table3 is made of col1 and col2.
View 4 Replies
View Related
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
View 3 Replies
View Related
Aug 1, 2007
I'm trying not to use a temp table, but i may have to do so..
I'm using sql2005 for this case.
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something. If I've aliased it as 'results', is there a way to alias results again as something else? or maybe a trick with CTEs? I will try that! It seems promising.
View 1 Replies
View Related
Aug 13, 2014
Recently, I partitioned one of my largest tables into multiple monthly field groups. For the current month, it is attached to my "Active' table. The older records are kept in the "historical" table. I need an efficient way to pull records when have a date range that can be spread across both tables.
View 9 Replies
View Related
Aug 1, 2007
I'm trying not to use a temp table, but i may have to do so..
i have a derived table that makes the following results:
ID Status Name
2 1 "A"
2 2 "B"
I want to get the following:
ID Name1 Name2
2 "A" "B"
but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.
View 5 Replies
View Related
Sep 17, 2012
I am TRYING to write code to combine two tables and then return the maximum value of one table, but SQL Server keeps telling me that the column is not valid.... I have added attached screenshots to show that it IS a valid column, so I cannot figure out what is the retarded issue!!
View 11 Replies
View Related
Sep 11, 2014
DECLARE @EmployeeID nvarchar(1000)
DECLARE @FiscalYear nvarchar(1000)
SET @EmployeeID = '101,102,103,104,105'
SET @FiscalYear = '2013,2014'
SELECT Data FROM dbo.Split(@EmployeeID, ',')
SELECT Data FROM dbo.Split(@fiscalyear, ',')
_______________________________________
This is part of a bigger project but I am stuck on this part. I get back 2 result sets
Data Data
101 2013
102 2014
103
104
105
I want to insert the results in a new table 2 columns and get the results below.
New Table
ID Fiscal Year
101 2013
101 2014
102 2013
102 2014
103 2013
103 2014
104 2013
104 2014
105 2013
105 2014
View 2 Replies
View Related
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
Table D
View 7 Replies
View Related
Jul 20, 2005
I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
View 2 Replies
View Related
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Dec 3, 2014
I have a table with about half a million records, each representing a patient in my county.
Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.
I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.
I can see what I want to accomplish by using ROW_NUMBER, thus:
SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank
I see the numbers in the RecNumber column falling behind the RRank as I scan down the results
My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:
UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);
but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).
View 5 Replies
View Related
Jul 20, 2005
I have been searching many postings and I cant seem to find anyonethat has this answer so I decided to post. I am using SQL(Transact-SQL).If I have 2 tables with columnsacct_num,activity_date,and pay_amt and I want to delete one instanceof a record in table 1 for every instance of that record in table 2how could I do that. For example.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00Table 2-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00I need a delete statement that will find 2 of the 5 records(It doesn'tmatter which 2) and delete them.Leaving table one looking like this.Table 1-----------acct activity_date pay_amt123 5/1/2004 50.00123 5/1/2004 50.00123 5/1/2004 50.00How can I do this??
View 7 Replies
View Related
Apr 4, 2006
How can i combine my data in single row ? All data are in a single table sorted as employeeno, date
Code:
Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000
Should Appear
Code:
EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800
PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.
THANKS IN ADVANCE
View 3 Replies
View Related
Aug 19, 2006
In SQL Server 2000, I have a parent table with a cascade update to a child table. I want to add a record to the child table whenever I add a table to the parent table. Thanks
View 1 Replies
View Related
