SQLTeam.com | Weblogs | Forums

How Can I write this query?


#1

Mates,

I have two tables as shown below. I need an output as mentioned below. Can you help me in writing a query for same.
Table one : mytable
myId aID bID cID dID eID
4050 166 186 171 75 175
8888 26 36 46 56 76

Table Two : childtable

childId mType Make serialnum
166 ramu h 12
186 gopu g 13
171 vishnu v 14
75 mini m 15
175 jinu z 16

From table one we can get a 'myId ' and its related other id's such as (aid,bid,cid etc)
I need to fetch the details of these each Id from the child table

Output will be some what like this . I.e each Id in master is replaced with its detailed info from child table.
4050 ramu h 12 gopu g 13 vishnu v 14 mini m 15 jinu z 16


#2

try this

select pARENT.mYid,A.Mytpe,A.make,A.serialNum
,B.Mytpe,B.make,B.serialNum
,C.Mytpe,C.make,C.serialNum
,D.Mytpe,D.make,D.serialNum
from Mytable Parent
left join CHildtable A on A.childId = Parent.AID
left join CHildtable B on B.childId = Parent.BID
left join CHildtable C on C.childId = Parent.CID
left join CHildtable D on D.childId = Parent.DID


#3

Thanks alot mate. But i need to restrict output only to one userID as mentioned in my question i.e
for eg myId =4050 will also be one of the filter criteria. Where can i specify that as well in query given above?

i need to get output details corresponding only to the myId i specify .


#4

add where clause to the soln that @sqlps has provided
e.g
where myid =4050

you can also use inner join for this particular example


#5

select mYid,A.Mytpe,A.make,A.serialNum
,B.Mytpe,B.make,B.serialNum
,C.Mytpe,C.make,C.serialNum
,D.Mytpe,D.make,D.serialNum
from Mytable Parent
inner join CHildtable A on A.childId = Parent.AID
inner join CHildtable B on B.childId = Parent.BID
inner join CHildtable C on C.childId = Parent.CID
inner join CHildtable D on D.childId = Parent.DID
where mYid= 4050


#6

Thanks All mates for wonderful help