Joining 3 Tables - Better to use Temp_Table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Joining 3 Tables - Better to use Temp_Table?

Author

Topic

overcast
Starting Member

6 Posts

Posted - 2002-01-15 : 10:54:43

$orders_query="SELECT o.ORDS1_REF,o.ORDS1_LINE,o.ORDS4_CUS_SUP,a.ACCMNAME FROM ORDS_DATA o, ACCM_DATA a WHERE
o.ORDS4_CO_SITE='10' AND o.ORDS3_PART='02-084' AND o.ORDS4_REC_TYPE='30' AND o.ORDS3_KEY_STAT < 18
AND o.ORDS4_CUS_SUP=a.ACCM1_CUS_SUP AND a.ACCM1_REC_TYPE='02' AND a.ACCM1_CO_SITE='10'";

$schd_query="SELECT o.ORDS1_LINE,o.ORDS4_CUS_SUP,o.ACCMNAME,s.SCHD4_REF,s.SCHD4_REF_LINE FROM SCHD_DATA s WHERE s.SCHD4_REF = o.ORDS1_REF";

Basically what is happening is that i want columns from both the queries and not just the final query so i can't use a simple subquery. I'd rather not use a temporary table or correlated subqueries. Any ideas? ---- Trying to link SCHD_DATA.SCHD4_REF with the data returned from the $ords_query.
Thanks Guys.

Edited by - overcast on 01/15/2002 10:55:21

Edited by - overcast on 01/15/2002 10:56:22

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-01-15 : 11:10:21

What's wrong with a correlated subquery? That's one of the gems that most people don't use.

overcast
Starting Member

6 Posts

Posted - 2002-01-15 : 11:16:32

well the inner result set must be constructed for every single row that is used in the outer results set. which means tons of processing.

what about creating a view of the first query?

mono
Starting Member

36 Posts

Posted - 2002-01-15 : 12:09:50

Explain a bit more. What is the output you want?

As far as I can see, you should be able to use a simple JOIN.

Lavos
Posting Yak Master

200 Posts

Posted - 2002-01-15 : 14:29:25

Mono is right, a simple join should work. Unless you don't know about derived tables, in which case it'd be a little harder.

Try searching the books online or this site for derived table examples, I think it's what you're looking for.

Simple derived table example:

SELECT *
FROM Table1 AS t1, (SELECT * FROM Table2 WHERE col2 = @val) AS t2
WHERE t1.col1 = t2.col2

Just make sure to use an alias for your derived table.

-- Update, I just read your queries more closely, you don't need derived tables. You can just join the third table in one statment. My first suggestion though is to switch to using the newer join syntax. It's more clear on what the join conditions are and isn't as touchy when it comes to maintenance.

----------------------
"O Theos mou! Echo ten labrida en te mou kephale!"

Edited by - Lavos on 01/15/2002 14:35:55

overcast
Starting Member

6 Posts

Posted - 2002-01-15 : 15:01:49

thanks guys..i appreciate the help

overcast
Starting Member

6 Posts

Posted - 2002-01-15 : 15:44:28

SELECT A.ACCMNAME,O.ORDS1_REF,O.ORDS1_LINE,O.ORDS2_CUS_SUP FROM
(SELECT ORDS1_REF,ORDS1_LINE,ORDS2_CUS_SUP FROM ORDS_DATA WHERE
ORDS3_CO_SITE='10' AND ORDS3_REC_TYPE='30' AND ORDS3_KEY_STAT < '18'
AND ORDS3_PART='02-084') O,(SELECT ACCMNAME,ACCM1_CUS_SUP FROM ACCM_DATA WHERE
ACCM1_CO_SITE='10' AND ACCM1_REC_TYPE='02') A WHERE
A.ACCM1_CUS_SUP = O.ORDS2_CUS_SUP;

Heres what i got. What you think?

Edited by - overcast on 01/15/2002 15:45:16

Nazim
A custom title

1408 Posts

Posted - 2002-01-15 : 23:48:19

SELECT A.ACCMNAME,O.ORDS1_REF,O.ORDS1_LINE,O.ORDS2_CUS_SUP FROM
(SELECT ORDS1_REF,ORDS1_LINE,ORDS2_CUS_SUP FROM ORDS_DATA WHERE
ORDS3_CO_SITE='10' AND ORDS3_REC_TYPE='30' AND ORDS3_KEY_STAT < '18'
AND ORDS3_PART='02-084') O
inner join
(SELECT ACCMNAME,ACCM1_CUS_SUP FROM ACCM_DATA WHERE
ACCM1_CO_SITE='10' AND ACCM1_REC_TYPE='02') A
ON A.ACCM1_CUS_SUP = O.ORDS2_CUS_SUP

Should do better.

----------------------------------
"True love stories don't have endings."

overcast
Starting Member

6 Posts

Posted - 2002-01-16 : 09:02:44

SELECT A.ACCMNAME,O.ORDSSCHED_TYPE,O.ORDS4_REF,O.ORDS4_LINE,O.ORDS4_CUS_SUP FROM
(SELECT ORDSSCHED_TYPE,ORDS4_REF,ORDS4_CO_SITE,ORDS4_LINE,ORDS4_CUS_SUP FROM ORDS_DATA WHERE
ORDS3_CO_SITE='10' AND ORDS3_REC_TYPE='30' AND ORDS3_KEY_STAT < '18'
AND ORDS3_PART='02-084') O,ACCM_DATA A WHERE A.ACCM1_CO_SITE=O.ORDS4_CO_SITE
AND A.ACCM1_CUS_SUP = O.ORDS4_CUS_SUP;

works like butta....oracle doesnt support the inner join syntax =/

Nazim
A custom title

1408 Posts

Posted - 2002-01-16 : 09:32:57

Where does Oracle Come into picture here.
Hey! Did you asked this Query for Oracle??.

quote:

works like butta....oracle doesnt support the inner join syntax =/

----------------------------------
"True love stories don't have endings."

overcast
Starting Member

6 Posts

Posted - 2002-01-16 : 10:00:58

quote:

Where does Oracle Come into picture here.
Hey! Did you asked this Query for Oracle??.

quote:

works like butta....oracle doesnt support the inner join syntax =/

nope i didn't need to. i just was looking for some logic help

----------------------------------
"True love stories don't have endings."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources