Queryproblem - 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

Queryproblem

Author

Topic

newbone
Starting Member

4 Posts

Posted - 2011-01-10 : 13:01:16

Hi!

I'm trying to get this query to work :

select distinct
A6ID,
A6DEPART,
A6ENDDATE,
(select F7ID from sstt.swchavaf7 where f7type=(select f6pkey from sstt.swchatyf6 where f6id='01SVP' and f7pkey=f8value)) as 01SVP,
(select F7ID from sstt.swchavaf7 where f7type=(select f6pkey from sstt.swchatyf6 where f6id='02VP' and f7pkey=f8value)) as 02VP,
(select F7ID from sstt.swchavaf7 where f7type=(select f6pkey from sstt.swchatyf6 where f6id='03Director' and f7pkey=f8value)) as 03Director
from
sstt.swresoua6,
sstt.swreschf8,
sstt.swchavaf7,
sstt.swchatyf6
where f8resource = a6pkey and f8value=f7pkey and f6pkey=f7type order by a6id

but I get this :

'BENMAT', '2009-12-28 21:31:26.828', '0', NULL, '01AM', NULL
'BENMAT', '2009-12-28 21:31:26.828', '0', NULL, NULL, '34AUTO'
'BENMAT', '2009-12-28 21:31:26.828', '0', '10CD', NULL, NULL
'FI-015', '2010-01-25 17:30:19.53', '0', NULL, '01AM', NULL
'FI-015', '2010-01-25 17:30:19.53', '0', NULL, NULL, '34AUTO'
'FI-015', '2010-01-25 17:30:19.53', '0', '10CD', NULL, NULL
'FI-020', '2010-01-25 17:30:19.53', '0', '20FD', NULL, NULL
'FI-020', '2010-01-25 17:30:19.53', '0', NULL, '01AM', NULL
'FI-020', '2010-01-25 17:30:19.53', '0', NULL, NULL, '34AUTO'

I would like only one row per a6Id (avoiding the one with NULL)
How should I get those away? (this would the only return 3 rows)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 13:08:59

looks like below is what you want


select 
A6ID,
A6DEPART,
A6ENDDATE,
01SVP,
02VP,
03Director
from
sstt.swresoua6
join sstt.swreschf8
on f8resource = a6pkey
join (
select f7pkey,
max(case when f6id='01SVP' then F7ID end) as 01SVP,
max(case when f6id='02VP' then F7ID end) as 02VP,
max(case when f6id='03Director' then F7ID end) as 03Director
from sstt.swchavaf7
join sstt.swchatyf6
on f6pkey=f7type 
group by f7pkey
)t
on f8value=f7pkey
order by a6id

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

newbone
Starting Member

4 Posts

Posted - 2011-01-10 : 13:23:31

hmm, when I run that I get the same result- I would like to have this :

'BENMAT', '2009-12-28 21:31:26.828', '0', 10CD, '01AM','34AUTO'
'FI-015', '2010-01-25 17:30:19.53', '0', 10CD, '01AM','34AUTO'
'FI-020', '2010-01-25 17:30:19.53', '0', '20FD', '01AM','34AUTO'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 13:25:32

good. now you can post the sample data as it is existing in different tables for above two rows without which its difficult for somebody to guess the type of relationships with them.

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

newbone
Starting Member

4 Posts

Posted - 2011-01-10 : 13:42:25

hmmm

create table `sstt`.`swresoua6`(
`A6PKEY` CHAR(25) not null,
`A6ID` CHAR(15) unique,
`A6FIRSTNAM` CHAR(50),
`A6LASTNAME` CHAR(50),
`A6DEPART` CHAR(25),
`A6GROUP` CHAR(25),
`A6EMPLOYNO` CHAR(15) unique,
`A6ENDDATE` DECIMAL(8),
`A6START` DECIMAL(8),
`A6ALLOC` CHAR(2),
`A6ACTIVATE` CHAR(1),
`A6PAYTRF` CHAR(1),
`A6COSTGR` CHAR(25),
`A6EMAIL` VARCHAR(200),
`A6INTPRICE` CHAR(25),
`A6COUNTRY` CHAR(25),
`A6HOLIDAY` DECIMAL(3),
`A6ABSENCE` DECIMAL(3),
`A6PAYROSYS` CHAR(10),
`A6COSTACC` CHAR(25),
primary key (`A6PKEY`)
);

create table `sstt`.`swreschf8`(
`F8PKEY` CHAR(25) not null,
`F8RESOURCE` CHAR(25),
`F8VALUE` CHAR(25),
primary key (`F8PKEY`)
);

create table `sstt`.`swchavaf7`(
`F7PKEY` CHAR(25) not null,
`F7ID` CHAR(15),
`F7DESC` CHAR(50),
`F7TYPE` CHAR(25),
primary key (`F7PKEY`)
);

create table `sstt`.`swchatyf6`(
`F6PKEY` CHAR(25) not null,
`F6ID` CHAR(15) unique,
`F6DESC` CHAR(50),
primary key (`F6PKEY`)
);

A6PKEY<-->F8RESOURCE
F8VALUE<-->F7PKEY
F7TYPE<--F6PKEY

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 13:45:21

where's the data?

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

newbone
Starting Member

4 Posts

Posted - 2011-01-10 : 13:57:24

hmm, here's some dump
swresoua6:

'2009-03-08 19:45:21.1', 'BENMAT', 'Benny', 'Mattsson', '2009-12-28 21:31:26.828', '2009-12-29 20:16:47.888', '5', '0', '20090216', '1', '', '', NULL, '', NULL, '2009-02-09 15:39:16.10', '0', '0', '', '2010-01-04 11:54:04.913'
'2010-01-25 17:38:59.65', 'FI-015', 'Bea', 'Lindholm', '2010-01-25 17:30:19.53', '2009-01-26 16:04:25.7', 'IPC-0281', '0', '20100101', '1', '1', '1', NULL, '', NULL, '2009-01-26 15:47:32.35', '0', '0', '', NULL
'2010-01-25 17:45:28.70', 'FI-020', 'Julia', 'Törnqvist', '2010-01-25 17:30:19.53', '2009-01-26 16:04:25.7', 'IPC-0447', '0', '20100101', '1', '1', '1', NULL, '', NULL, '2009-01-26 15:47:32.35', '0', '0', '', NULL

swreschf8:
'2011-01-10 12:35:23.7', '2009-03-08 19:45:21.1', '2011-01-10 12:16:21.1'
'2011-01-10 12:35:23.8', '2009-03-08 19:45:21.1', '2011-01-10 12:33:57.4'
'2011-01-10 12:35:23.9', '2009-03-08 19:45:21.1', '2011-01-10 12:34:54.6'
'2011-01-10 14:28:38.16', '2010-01-25 17:45:28.70', '2011-01-10 12:34:54.6'
'2011-01-10 14:28:38.15', '2010-01-25 17:45:28.70', '2011-01-10 12:33:57.4'
'2011-01-10 14:28:38.14', '2010-01-25 17:45:28.70', '2011-01-10 14:28:20.13'
'2011-01-10 14:45:41.17', '2010-01-25 17:38:59.65', '2011-01-10 12:16:21.1'
'2011-01-10 14:45:41.18', '2010-01-25 17:38:59.65', '2011-01-10 12:33:57.4'
'2011-01-10 14:45:41.19', '2010-01-25 17:38:59.65', '2011-01-10 12:34:54.6'

swchavaf7:
'2011-01-10 12:16:21.1', '10CD', 'Corporate Developement', '2011-01-10 12:15:38.0'
'2011-01-10 12:33:57.4', '01AM', 'Americas', '2011-01-10 12:33:23.3'
'2011-01-10 12:34:54.6', '34AUTO', 'Automative, PD', '2011-01-10 12:34:36.5'
'2011-01-10 14:28:20.13', '20FD', 'Fg Developement', '2011-01-10 12:15:38.0'

swchatyf6:
'2011-01-10 12:34:36.5', '03Director', 'Sub-area'
'2011-01-10 12:33:23.3', '02VP', 'Areas'
'2011-01-10 12:15:38.0', '01SVP', 'Business Line'

Thank you very much for your answers, I hope this helps...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 14:01:19

see this to understand how to post data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources