Query trouble with multiple tables

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

SQL Server 2000 Forums

SQL Server Development (2000)

Query trouble with multiple tables

Author

Topic

synakamr
Starting Member

3 Posts

Posted - 2003-10-22 : 04:45:02

Greeting, I've just started with SQL (MSSQL 2000) and got stuck at this problem.

I have the following schema:


CREATE TABLE td_yusougenbo_kukan (
	bumonCD int NOT NULL ,
	gkigouNEN smallint NOT NULL ,
	gkigouTUKI smallint NOT NULL ,
	SEQ int NOT NULL ,
	KukS int NOT NULL ,
	kukanNAME1 nvarchar (64) COLLATE Japanese_Unicode_BIN NULL ,
	kukanNAME2 nvarchar (64) COLLATE Japanese_Unicode_BIN NULL ,
	kukanNAME3 nvarchar (64) COLLATE Japanese_Unicode_BIN NULL ,
	PRIMARY KEY (bumonCD, gkigouNEN, gkigouTUKI, SEQ, KukS))

CREATE TABLE td_yusougenbo_seikyu (
	bumonCD int NOT NULL ,
	gkigouNEN smallint NOT NULL ,
	gkigouTUKI smallint NOT NULL ,
	SEQ int NOT NULL ,
	KukS int NOT NULL ,
	SeRyCD smallint NOT NULL ,
	SeFl smallint NOT NULL ,
	kingaku money NULL ,
	PRIMARY KEY (bumonCD, gkigouNEN, gkigouTUKI, SEQ, KukS))
ALTER TABLE td_yusougenbo_seikyu ADD 
	CONSTRAINT FK_td_yusougenbo_seikyu_td_yusougenbo_kukan FOREIGN KEY 
	(bumonCD, gkigouNEN, gkigouTUKI, SEQ, KukS)
	REFERENCES td_yusougenbo_kukan (bumonCD, gkigouNEN, gkigouTUKI, SEQ, KukS)
	ON DELETE CASCADE  ON UPDATE CASCADE 

CREATE TABLE td_yusougenbo_sitabarai (
	bumonCD int NOT NULL ,
	gkigouNEN smallint NOT NULL ,
	gkigouTUKI smallint NOT NULL ,
	SEQ int NOT NULL ,
	KukS int NOT NULL ,
	SiRyCD smallint NOT NULL ,
	SiFl smallint NOT NULL ,
	kingaku money NULL ,
	PRIMARY KEY (bumonCD, gkigouNEN, gkigouTUKI, SEQ, KukS))
ALTER TABLE td_yusougenbo_sitabarai ADD 
	CONSTRAINT FK_td_yusougenbo_sitabarai_td_yusougenbo_kukan FOREIGN KEY 
	(bumonCD, gkigouNEN, gkigouTUKI, SEQ, KukS)
	REFERENCES td_yusougenbo_kukan (bumonCD, gkigouNEN, gkigouTUKI, SEQ, KukS)
	ON DELETE CASCADE  ON UPDATE CASCADE

I have the following sample data:


INSERT INTO td_yusougenbo_kukan
SELECT 93114, 2003, 10, 16, 98, 'A', 'B', 'C'
UNION SELECT 93114, 2003, 10, 17, 99, 'A', 'B', 'C'
UNION SELECT 93114, 2003, 10, 18, 100, 'A', 'B', 'C'
UNION SELECT 93114, 2003, 10, 19, 114, 'A', 'B', 'C'

INSERT INTO td_yusougenbo_seikyu
SELECT 93114, 2003, 10, 16, 98, 176, 3, 1
UNION SELECT 93114, 2003, 10, 16, 98, 177, 1, 2
UNION SELECT 93114, 2003, 10, 16, 98, 191, 1, 3
UNION SELECT 93114, 2003, 10, 18, 100, 176, 3, 1
UNION SELECT 93114, 2003, 10, 18, 100, 177, 1, 2
UNION SELECT 93114, 2003, 10, 18, 100, 191, 1, 3

INSERT INTO td_yusougenbo_sitabarai
SELECT 93114, 2003, 10, 17, 99, 176, 3, 1
UNION SELECT 93114, 2003, 10, 17, 99, 177, 1, 2
UNION SELECT 93114, 2003, 10, 17, 99, 191, 1, 3
UNION SELECT 93114, 2003, 10, 18, 100, 176, 3, 1
UNION SELECT 93114, 2003, 10, 18, 100, 177, 1, 2
UNION SELECT 93114, 2003, 10, 18, 100, 191, 1, 3

I would like to generate the following rowset:


SEQ    KukS  SeRyCD  SeFl    SiRyCD  SiFl
------ ----- ------- ------- ------- ------
16     98    176     3       <NULL>  <NULL>
16     98    177     1       <NULL>  <NULL>
16     98    191     1       <NULL>  <NULL>
17     99    <NULL>  <NULL>  176     3
17     99    <NULL>  <NULL>  177     1
17     99    <NULL>  <NULL>  191     1
18     100   176     3       176     3
18     100   177     1       177     1
18     100   191     1       191     1
19     114   <NULL>  <NULL>  <NULL>  <NULL>

The actual tables contains more fields and data but I've cut it down a bit
to easier grasp and get to the core of my troubles.
Thanks in advance.

synakamr

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-10-22 : 07:09:02

Great post!! The CREATE TABLE and INSERT statemetns are really appreciated!

However, can you provide a little more detail about the desired results, and how these 3 tables realte. It is hard to try to guess what you are trying to return. Not knowing Japanese makes it quite a bit harder !

- Jeff

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-10-22 : 07:11:58

First, you haven't created any foreign key constraints, so it is impossible for me to tell how these tables relate. Additionally, your desired rowset selects columns (SeRyCD, SeFl) which don't seem to exist in the DDL.

Jay White
{0}

synakamr
Starting Member

3 Posts

Posted - 2003-10-22 : 08:04:03

Ah, Sorry about forgetting the FK's and the errata with the column names.
I've edited my original post's CREATE statements but wasn't sure how to correctly add the
foreign key constraints directly, I hope the ALTER statement is correct.
The values shown in these tables are ID's and flags for other values in other tables.

What I'm trying to achieve is merging the tables into a single one with the result as slim as possible.
I suspect that it can be done with some INSERT/UPDATE statement but I just started working with this last week
and havent been able to put my mind into SQL language mode yet.
I can easely achieve something like:


SEQ    KukS  SeRyCD  SeFl    SiRyCD  SiFl
------ ----- ------- ------- ------- ------
...
18     100   176     3       <NULL>  <NULL>
18     100   177     1       <NULL>  <NULL>
18     100   191     1       <NULL>  <NULL>
18     100   <NULL>  <NULL>  176     3
18     100   <NULL>  <NULL>  177     1
18     100   <NULL>  <NULL>  191     1
...

But what I need is a result more like this:


SEQ    KukS  SeRyCD  SeFl    SiRyCD  SiFl
------ ----- ------- ------- ------- ------
...
18     100   176     3       176     3
18     100   177     1       177     1
18     100   191     1       191     1
...

Hope I explained it better this time.

synakamr

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-10-22 : 08:13:40

You haven't provided the query that you used to to get the first resultset, but anyway the solution is not that difficult:


SELECT SEQ, KukS, MAX(SeRyCD) AS SeRyCD, MAX(SeFl) AS SeFl, MAX(SiRyCD) AS SiRyCD, MAX(SiFl) AS SiFl FROM
 (
    --Your original query here
 ) A
GROUP BY SEQ, KukS

Sayonara

Owais

Make it idiot proof and someone will make a better idiot

synakamr
Starting Member

3 Posts

Posted - 2003-10-22 : 22:03:22

The Query I used to get my first example was:


CREATE TABLE #Temp_Example (SEQ smallint, KukS smallint, SeRyCD int NULL, SeFl smallint, SiRyCD int NULL, SiFl smallint)

INSERT INTO #Temp_Example(SEQ, KukS, SeRyCD, SeFl)
SELECT	td_yusougenbo_kukan.SEQ, td_yusougenbo_kukan.KukS,
	td_yusougenbo_seikyu.SeRyCD, td_yusougenbo_seikyu.SeFl
FROM	td_yusougenbo_kukan
INNER JOIN td_yusougenbo_seikyu ON
	td_yusougenbo_kukan.bumonCD = td_yusougenbo_seikyu.bumonCD AND
	td_yusougenbo_kukan.gkigouNEN = td_yusougenbo_seikyu.gkigouNEN AND
	td_yusougenbo_kukan.gkigouTUKI = td_yusougenbo_seikyu.gkigouTUKI AND
	td_yusougenbo_kukan.SEQ = td_yusougenbo_seikyu.SEQ AND
	td_yusougenbo_kukan.KukS = td_yusougenbo_seikyu.KukS
WHERE	(td_yusougenbo_kukan.bumonCD = 93114) AND (td_yusougenbo_kukan.SEQ = 18) AND
	(td_yusougenbo_kukan.gkigouNEN = 2003) AND (td_yusougenbo_kukan.gkigouTUKI = 10)
	
INSERT INTO #Temp_Example(SEQ, KukS, SiRyCD, SiFl)
SELECT	td_yusougenbo_kukan.SEQ, td_yusougenbo_kukan.KukS,
	td_yusougenbo_sitabarai.SeRyCD, td_yusougenbo_sitabarai.SeFl
FROM	td_yusougenbo_kukan
INNER JOIN td_yusougenbo_sitabarai ON
	td_yusougenbo_kukan.bumonCD = td_yusougenbo_sitabarai.bumonCD AND
	td_yusougenbo_kukan.gkigouNEN = td_yusougenbo_sitabarai.gkigouNEN AND
	td_yusougenbo_kukan.gkigouTUKI = td_yusougenbo_sitabarai.gkigouTUKI AND
	td_yusougenbo_kukan.SEQ = td_yusougenbo_sitabarai.SEQ AND
	td_yusougenbo_kukan.KukS = td_yusougenbo_sitabarai.KukS
WHERE	(td_yusougenbo_kukan.bumonCD = 93114) AND (td_yusougenbo_kukan.SEQ = 18) AND
	(td_yusougenbo_kukan.gkigouNEN = 2003) AND (td_yusougenbo_kukan.gkigouTUKI = 10)

SELECT * From #Temp_Example ORDER BY SEQ
DROP TABLE #Temp_Example

The solution though quickly provided from Owais would generate:


SEQ    KukS  SeRyCD  SeFl    SiRyCD  SiFl
------ ----- ------- ------- ------- ------
18     100   191     3       191     3

Which is a bit off from what I wanted as it only pick up the highest values.
The desired result should preferably be something like:


SEQ    KukS  SeRyCD  SeFl    SiRyCD  SiFl
------ ----- ------- ------- ------- ------
18     100   176     3       176     3
18     100   177     1       177     1
18     100   191     1       191     1

Please have patience as I'm a novice with SQL.
Thanks in advance

-synakamr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources