Multiple joins to same table killing me

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)

Multiple joins to same table killing me

Author

Topic

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-09 : 17:16:29

I have a number of tables which I'm joining but there's one in particular, for whatever reason, which resides in a database with form:

Date Code Item Value

from which I need to return a number of records. The only trouble is that I need to return the Items as columns, despite the way the table stores the data (Thus, I need to return Item1, Item2, Item3 for given sets of Codes and Dates). It probably makes sense why the table is stored the way it is because the items are several hundred per date and code.

Anyway, so in my primitive thinking, I thought let's just inner join the table to itself for however many columns of 'Items' I need, which has left me with 'c' to 'o' number of tables which, as you can imagine, is killing me. To illustrate the sheer stupidity of my query, I offer it below (I have unique names for where 'FiledNoX' appears as column headings):

select c.Date_, a.Id, b.Name, "FieldNoX" = c.Value_, "FieldNoX" = d.Value_, "FieldNoX" = e.Value_, "FieldNoX" = f.Value_, "FieldNoX" = g.Value_, "FieldNoX" = h.Value_, "FieldNoX" = i.Value_, "FieldNoX" = j.Value_, "FieldNoX" = k.Value_, "FieldNoX" = l.Value_, "FieldNoX" = m.Value_, "FieldNoX" = n.Value_, "FieldNoX" = o.Value_

from PrcMap a,
csinfo b,
largedatatable c,
largedatatable d,
largedatatable e,
largedatatable f,
largedatatable g,
largedatatable h,
largedatatable i,
largedatatable j,
largedatatable k,
largedatatable l,
largedatatable m,
largedatatable n,
largedatatable o

where a.Code = b.Code
and a.Code = c.Code
and a.Code = d.Code
and a.Code = e.Code
and a.Code = f.Code
and a.Code = g.Code
and a.Code = h.Code
and a.Code = i.Code
and a.Code = j.Code
and a.Code = k.Code
and a.Code = l.Code
and a.Code = m.Code
and a.Code = n.Code
and a.Code = o.Code
and c.Item = 123
and d.Item = 108
and e.Item = 308
and f.Item = 311
and g.Item = 313
and h.Item = 115
and i.Item = 127
and j.Item = 70
and k.Item = 6
and l.Item = 233
and m.Item = 323
and n.Item = 54
and o.Item = 171
and a.Code = 1356
order by c.Date_

Why do I need to do this? Because I actually need to very carefully check these data items for small sets of records I'll be running and perform arithmetic operations across the columns I generate which need to be visually legible.

Any ideas how I could do this better?

Many thanks to the forgiving soul wishing to help a git in need.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-05-09 : 17:36:44

Try something like this:

select
	c.Date_,
	a.Id,
	b.Name, 
	[FieldNoX] = 
		max(case when c.Item = 123 then c.Value_ else '' end),
	[FieldNoX  =
		max(case when c.Item = 108 then c.Value_ else '' end),

	etc
from
	PrcMap a
	join
	csinfo b
	on a.code = b.code
	join
	largedatatable c
	on a.code = c.code
group by
	c.Date_,
	a.Id,
	b.Name

CODO ERGO SUM

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-09 : 18:03:47

Soooo much better! Thank You.

One question - how would I return the value if I have a negative number? I was thinking of replacing 'max' with 'min' but then I'm not sure when I'll have a positive and when a negative to compare with null ''. Is there a way to combine these conditions and return only when there is a value?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-09 : 18:20:45

Don't ask -- try it. create a small table, put in some values with at least 1 null, see what happens when you try MIN() or MAX() and what is returned. Try different datatypes, differnt values, practive different pivot queries and techniques to see how you can get a small list of values to come up in a different format.

Get into the habit of learning yourself you'll be much, much better off than if you ask too many questions and get answers w/o really taking the time to think about the situation.

- Jeff

KidSQL
Yak Posting Veteran

88 Posts

Posted - 2005-05-09 : 18:22:27

I was afraid you'd say that. Okay, I'll give it a try.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources