Join tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join tables

Author

Topic

jorgepeneirol
Starting Member

6 Posts

Posted - 2010-12-29 : 08:01:21

Hi.

I trying to get some help building an sql statement for the follwing cenario :

I have the following tables :

W1A0 ( custumer general data )
====
CLIENT_REFERENCE
LAST_NAME_COY_NAME
FIRST_NAME
MIDDLE_NAMES

W1V0 ( customer extra data )
====
CLIENT_REFERENCE
MARITAL_STATUS
FISCAL_NUMBER
BIRTH_DATE
SEX
OCCUPATION_CODE

W1L0 ( customer address data )
====
CLIENT_REFERENCE
ADDRESS_CATEGORY
ADDRESS_LINE_1
ADDRESS_LINE_2
TOWN_SUBURB
POST_CODE

I would like to get all my customers W1A0 data, all my customers W1V0 data and the W1L0 data that as the max TIMESTAMP-UPDATED from customer that matches the W1A0 customer.

can you help me ? thanks

Sachin.Nand

2937 Posts

Posted - 2010-12-29 : 08:22:07

Where is the TIMESTAMP-UPDATED column ?

PBUH

jorgepeneirol
Starting Member

6 Posts

Posted - 2010-12-29 : 08:27:38

sorry. is in the W1L0 table ( address data ). it can return several and what I wish is the latest.

regards.

Sachin.Nand

2937 Posts

Posted - 2010-12-29 : 08:39:26

[code]
select * from(
select *,row_number()over(partition by W1A0.CLIENT_REFERENCE order by W1L0.TIMESTAMP-UPDATED desc)rid from W1A0
inner join W1V0 on W1V0.CLIENT_REFERENCE=W1A0.CLIENT_REFERENCE
inner join W1L0 on W1L0.CLIENT_REFERENCE=W1A0.CLIENT_REFERENCE
)T where rid=1
[/code]

PBUH

jorgepeneirol
Starting Member

6 Posts

Posted - 2010-12-29 : 08:56:00

thanks for the fast reply.

I've run the statement but I'm getting erro :

SQL CODE IS -104
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: FROM INTO
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 24 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'00000018' X'00000000' SQL DIAGNOSTIC INFORMATION

This is in sql for the DB2 of IBM. I don't know if that makes any diference.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-29 : 09:00:00

can you show what you want with some sample data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-12-29 : 09:04:54

quote:
This is in sql for the DB2 of IBM. I don't know if that makes any diference.

It does.My query wont work for DB2.

PBUH

jorgepeneirol
Starting Member

6 Posts

Posted - 2010-12-29 : 09:50:04

Sachin, Do you know how it can be done for DB2 ?

visakh, I'm making the sample.

jorgepeneirol
Starting Member

6 Posts

Posted - 2010-12-29 : 09:54:43

W1A0 ( custumer general data )
====
CLIENT_REFERENCE LAST_NAME_COY_NAME FIRST_NAME MIDDLE_NAMES
1001 Mendes João Sousa
1002 Salvador Nuno Silva

W1V0 ( customer extra data )
====
CLIENT_REFERENCE MARITAL_STATUS FISCAL_NUMBER BIRTH_DATE SEX OCCUPATION_CODE
1001 C 12345678 12-12-1990 M 12
1002 C 87654321 12-12-1980 M 12

W1L0 ( customer address data )
====
CLIENT_REFERENCE ADDRESS_CATEGORY ADDRESS_LINE_1 ADDRESS_LINE_2 TOWN_SUBURB POST_CODE TIMESTAMP_UPDATED
1001 X Rua da Palavra Nr 200 Lisboa 12000456 2007-01-01-10.10.10.000000
1001 X Rua da Palavra Nr 201 Lisboa 12000456 2008-01-01-10.10.10.000000
1001 X Rua da Palavra Nr 202 Lisboa 12000456 2009-01-01-10.10.10.000000
1001 X Rua da Palavra Nr 203 Lisboa 12000456 2010-01-01-10.10.10.000000
1002 X Rua da Palavra Nr 300 Lisboa 16000456 2007-01-01-10.10.10.000000
1002 X Rua da Palavra Nr 301 Lisboa 16000456 2008-01-01-10.10.10.000000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-12-29 : 09:54:54

quote:
Originally posted by jorgepeneirol

thanks for the fast reply.

I've run the statement but I'm getting erro :

SQL CODE IS -104
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL ",". SOME SYMBOLS THAT MIGHT
BE LEGAL ARE: FROM INTO
DSNT418I SQLSTATE = 42601 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNHPARS SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = 502 0 0 -1 24 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'000001F6' X'00000000' X'00000000' X'FFFFFFFF'
X'00000018' X'00000000' SQL DIAGNOSTIC INFORMATION

This is in sql for the DB2 of IBM. I don't know if that makes any diference.

then you need to post it on DB2 forum. this is MS SQL Server forum

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jorgepeneirol
Starting Member

6 Posts

Posted - 2010-12-29 : 09:56:02

sorry, ok thanks for your help. I'm going to an DB2 Forum. regards.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources