|
Hi
I have 2 tables Table A and Table B
I want to join these tables.
The relation between these tables is RegNo
The first table stores the Trudent details and second table is a history table
The regNo in the first table is primary key and second table is forign key
I want to Join all the data in the these two tables based on Date and Last updated time
I want the result like the following
pls help me to find a soln
RegNo Name Class Date
12ABC ABC 10 3/2/2010
13ABC DCB 1 3/2/2010
14DFG DFG 12 3/2/2010
15FHG FHG 5 3/2/2010
16RTY RTY 8 3/3/2010
Table B
Id RegNo LastUpdated
1 12ABC 3/2/2010 12:30
2 12ABC 3/2/2010 13:00
3 12ABC 3/2/2010 16:00
4 13BC 3/2/2010 12:00
Result
RegNo Name Class LastUpdated
12ABC ABC 10 3/2/2010 16:00
13ABC DCB 1 3/2/2010 12:00
14DFG DFG 12 NULL
15FHG FHG 5 NULL
|
|
|
|
|
Here is an article [^]that will help you understand what you want
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hope below code will resolve you problem
select a.regno, a.name , a.class, b.date from table a
left outer join
( select * from tableb wehre id = (select max(id) from tablb group by regno)) b
on a.regno = b.regno
|
|
|
|
|
Hi,
This query dose not answer because RegNo in Table_B in Id 4 dose not equal
with RegNo with Table_A,
But if it's correct this is answer
select a.RegNo,a.Name,count(a.Class),b.LastUpdated from Table_A as a
left join Table_B as b on a.RegNo=b.RegNo
where (b.Id between 3 and 4 ) or (a.RegNo Not In('16RTY')and a.RegNo In('14DFG','15FHG'))
group by a.RegNo,a.Name,b.LastUpdated
|
|
|
|
|
Hi Friends,
I have to create a view using a few tables. One table has a column having four values (A,B,C,D), I want the view have a column only have two values (say for A,B,C I want the value to be "INTERNAL", and for D the value is "EXTERNAL")
How can I solve this problem? Do I have to use a procedure? can anyone post a sample for me?
Many thanks...
^_^
|
|
|
|
|
I would assume Oracle has a case statement, something like the following works in SQL Server.
case where colA = 'D' then 'External' else 'internal' end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Dear All,
I am looking to query followings in Sql 2005 table as
Table A
ID Name
1 A
1 B
1 C
2 D
2 E
2 F
I want the result as
ID Name
1 A,B,C
2 D,E,F
any ideas
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
You have to use cursor. check this.[^]
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Thanks. I have used for Xml Path and I have done it in SQl 2005.
but i want to do it only with pure T-Sql which I need the same thing in Ms Access also.
any ideas for Ms Access 2003/2007
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Following one of the solution work for you but there must be better solution than this
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
CREATE FUNCTION tableaValue
(
@id int
)
RETURNS varchar(50)
AS
BEGIN
declare @name varchar(50)
declare @tempString varchar(50)
DECLARE tablea_cursor CURSOR FOR
select name from tablea where id = @id -- Variable value from the outer cursor
OPEN tablea_cursor
FETCH NEXT FROM tablea_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @tempString= isnull(@tempString,'') + ',' + @name
FETCH NEXT FROM tablea_cursor INTO @name
END
CLOSE tablea_cursor
DEALLOCATE tablea_cursor
-- Return the result of the function
RETURN @tempString
END
GO
select dbo.tableaValue(id),id from tablea group by id
|
|
|
|
|
You dont even need the cursor to build a CSV, you can use COALESCE
DECLARE @nameCsv VARCHAR(MAX)
SET @nameCSV = ''
SELECT @nameCsv = COALESCE(name + ',','') + @nameCsv
FROM tablea
SELECT LEFT(@nameCsv,LEN(@nameCsv)-1) AS NameCsv
|
|
|
|
|
its good but it nned it id wise see the question he need csv value which group by id
i hope this will on id basis
thanks for reply
|
|
|
|
|
I have no idea what you just said
|
|
|
|
|
how abt this ?
select b.id, b.name from TableA b
where b.id in (select distinct a.id from TableA a) Apurv
“Never trust a computer you can’t throw out a window.”
(Steve Wozniak)
“There are only two industries that refer to their customers as ‘users’.”
(Edward Tufte)
|
|
|
|
|
Thanks for reply.
the query returns my table as it is, not the output which I am searching forAbdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Oh .. sry ..
I think 'cursors' can only solve this ...Apurv
“Never trust a computer you can’t throw out a window.”
(Steve Wozniak)
“There are only two industries that refer to their customers as ‘users’.”
(Edward Tufte)
|
|
|
|
|
Hi all, How to create temp table with new column name from a existing table that has different
column name.
thanks
modified on Wednesday, February 3, 2010 8:19 AM
|
|
|
|
|
Easy
select column1 as newname
into #temp
from oldtable
where 1 = 2
insert into #temp(newname)
select column1
from oldtable
By creating an empty table you avoid locking a lot of the system tables in tempdb - ALL the time a select into is running the system tables in tempdb are locked, which prevents other users from creating temp tables.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I will have to try it then I will let you know.
thanks for your help
|
|
|
|
|
Jashimu--you have been a member long enough to know not to use such a general message title. Please refresh yourself with the forum guidelines if needed!
|
|
|
|
|
Hi,
How can I get all children in just one level downer than a parent in hiererchy in SQL Server 2008?
Best wishes
|
|
|
|
|
Typical example, organ area problem. The table name is organ:
ID OrganName ParentID
1 A 0
2 a 1
3 b 1
SELECT a.* from organ as a, organ as b where a.ParentID=B.ID
The result is:
ID OrganName ParentID
2 a 1
3 b 1
April
Comm100 - Leading Live Chat Software Provider
modified 27-May-14 8:49am.
|
|
|
|
|
For some reason I can't add my dataconnect. I click on add connection select sql server
then I browse to the programfiles/...sqlserver/data and click on books.mdf
it tells me every single time that the file is in use. But I don't know of any program that would be using it. I'm running the server on my desktop simply to try and learn it and do a little programming with it. i can't seem to understand why it will not access that database.
Any help would be great thanks...
|
|
|
|
|
With ProcMon[^] you can investigate which process has the file in use.
Wout Louwers
|
|
|
|