Help Me out with this Query

mrkeivan

Hey guys, I've got 4 tables Table A ID B_ID Table B ID Name C_ID D_ID Table C ID Name Table D ID Name Now I want to make this selection:

Select A.ID, B.Name, C.Name, D.Name

Getting B.Name is simple by using a inner join but I', confused about how to get C.Name and D.Name based on B.ID (If the selection was one record It would be easier but sometimes it's multi-records) regards,

Garth J Lancaster

Im not exactely sure what your issue is getting C.Name and D.Name should be easy, but thats a seperate problem from your suggestion

mrkeivan wrote:

but sometimes it's multi-records

which implies that you may be looking for distinct rows - the data is what the data is - if you get multiple rows and you only want single rows, then you could have a fundamental constraint issue in your database design, or you're going to have to use distinct/unique keywords and hope its what you need why dont you post the sql you think you'd use - its also not obvious to the rest of us wether the relationship between the tables are 1:1 or 1:m for example... 'g'

Mycroft Holmes

Me thinks you need to do some reading on sql queries, this is a very simple problem. A is linked to B via BID B is linked to C via CID B is linked to D via DID So do the joins and select your fields.

Never underestimate the power of human stupidity RAH

Garth J Lancaster

you gave him the answer - I was going to make him work for it, then again, Ive been at work on a long weekend so Im a grumpy b@stard today (my team would say Im a grumpy b@stard everyday, but thats different :-) ) I was more interested in his multiple rows issues - I tend to find bad database design everywhere, forcing people to use distinct, max, min etc to get unique rows when it could have been prevented (and its a pet hate of mine if someone doesnt know why they are using such a qualifier) 'g'

Mycroft Holmes

Garth J Lancaster wrote:

Ive been at work on a long weekend so Im a grumpy b@stard today

So at least your getting paid to faff around with code today, I'm just bored at the moment.

Garth J Lancaster wrote:

you gave him the answer

The answer, yes, the code not a chance. He still needs to do some reseach into the exact syntax. Desperately needs to do some study if this has stumped him.

Never underestimate the power of human stupidity RAH

mrkeivan

Let me clarify what I just said, using a simple select I get BID,

Select BID FROM A WHERE ID = x

If I was sure I had one record returned I would write a function to do the job but most of the time I get more than one BID, No for each BID I get I have to Fetch C.Name ON B.ID = C.B_ID I Can't use inner join cause I have to have all the Fkeys in the main table A. regards,

David Skelly

I still don't quite understand your problem, I must be missing something. You select B_ID from A. Then you use this to join to B to get all matching C_ID and D_ID values. Then you use these to join to C to get C.Name and to D to get D.Name. If there will be any missing rows or null values anywhere along the line, use an outer join instead of an inner join. You can do all of this in one single SELECT statement. No need for functions, no need for FETCH or cursors or anything like that. If there are any N:M relationships along the chain, you may end up with more than one row for any given value of A.ID value. By using outer joins, you can ensure that you get at least one row for every A.ID value. I think I must be missing something in your problem because it seems quite straightforward to me.

Corporal Agarn

Try a left or right join for which ever table has the multiple.

Niladri_Biswas

Try this

Select a.Id, x.BName, x.CName,x.DName
From TableA a
Join(
Select
b.B_Id
,BName = b.Name
,CName = c.Name
,DName = d.Name

From TableB b
Join TableC c On b.C\_Id = c.C\_Id
Join TableD d On b.D\_Id = d.D\_Id)x

On a.B_Id = x.B_Id

Hope this helps

Niladri Biswas