Pulling in data from many tables.

I am trying to pull data from 4 tables. When I query them and us distinct
with three of them I can get the data I need. But when I try adding the
fourth one I get more rows then I need/or many dup roles. Want am I missing
or how can I only return want I need?Hi
Can you show us DDL+ sample data + expected result?
"DPassed" <DPassed@.discussions.microsoft.com> wrote in message
news:69A1A0B4-0F91-46C9-A4EB-B3338D9D1D06@.microsoft.com...
> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I
missing
> or how can I only return want I need?|||Hi DPassed
You might have missed the Join Condition when u joined Table-4.
Just check your query once or just post the query that you are facing the
problem
thanks and regards
Chandra
"DPassed" wrote:
> I am trying to pull data from 4 tables. When I query them and us distinct
> with three of them I can get the data I need. But when I try adding the
> fourth one I get more rows then I need/or many dup roles. Want am I missing
> or how can I only return want I need?|||select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
,poitem.flstpdate, poitem.fordqty
from rcmast
right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
left join pomast on rcmast.fpono = pomast.fpono
join poitem on rcmast.fpono = poitem.fpono
Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
order by rcmast.fpono, rcitem.fitemno
"Chandra" wrote:
> Hi DPassed
> You might have missed the Join Condition when u joined Table-4.
> Just check your query once or just post the query that you are facing the
> problem
> thanks and regards
> Chandra
>
> "DPassed" wrote:
> > I am trying to pull data from 4 tables. When I query them and us distinct
> > with three of them I can get the data I need. But when I try adding the
> > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > or how can I only return want I need?|||Hi
Try this Query Now:
select
distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
,poitem.flstpdate, poitem.fordqty
from rcmast
right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
left join pomast on rcmast.fpono = pomast.fpono
--CHANGED HERE
join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
--CHANGED HERE
Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
order by rcmast.fpono, rcitem.fitemno
thanks and regards
chandra
"DPassed" wrote:
> select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> ,poitem.flstpdate, poitem.fordqty
> from rcmast
> right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> left join pomast on rcmast.fpono = pomast.fpono
> join poitem on rcmast.fpono = poitem.fpono
> Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> order by rcmast.fpono, rcitem.fitemno
>
> "Chandra" wrote:
> > Hi DPassed
> > You might have missed the Join Condition when u joined Table-4.
> >
> > Just check your query once or just post the query that you are facing the
> > problem
> >
> > thanks and regards
> > Chandra
> >
> >
> > "DPassed" wrote:
> >
> > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > with three of them I can get the data I need. But when I try adding the
> > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > or how can I only return want I need?|||Didn't change rows returned. I do thank you for your help.
"Chandra" wrote:
> Hi
> Try this Query Now:
> select
> distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> ,poitem.flstpdate, poitem.fordqty
> from rcmast
> right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> left join pomast on rcmast.fpono = pomast.fpono
> --CHANGED HERE
> join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> --CHANGED HERE
> Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> order by rcmast.fpono, rcitem.fitemno
>
> thanks and regards
> chandra
>
> "DPassed" wrote:
> > select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > ,poitem.flstpdate, poitem.fordqty
> > from rcmast
> > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > left join pomast on rcmast.fpono = pomast.fpono
> > join poitem on rcmast.fpono = poitem.fpono
> >
> > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > order by rcmast.fpono, rcitem.fitemno
> >
> >
> > "Chandra" wrote:
> >
> > > Hi DPassed
> > > You might have missed the Join Condition when u joined Table-4.
> > >
> > > Just check your query once or just post the query that you are facing the
> > > problem
> > >
> > > thanks and regards
> > > Chandra
> > >
> > >
> > > "DPassed" wrote:
> > >
> > > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > > with three of them I can get the data I need. But when I try adding the
> > > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > > or how can I only return want I need?|||Hi,
Can you try this Now:
select
distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
,poitem.flstpdate, poitem.fordqty
from rcmast
right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
left join pomast on pomast.fpono = rcmast.fpono
join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
regards
Chandra
"DPassed" wrote:
> Didn't change rows returned. I do thank you for your help.
> "Chandra" wrote:
> > Hi
> > Try this Query Now:
> >
> > select
> > distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > ,poitem.flstpdate, poitem.fordqty
> > from rcmast
> > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > left join pomast on rcmast.fpono = pomast.fpono
> > --CHANGED HERE
> > join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> > --CHANGED HERE
> > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> >
> > order by rcmast.fpono, rcitem.fitemno
> >
> >
> > thanks and regards
> > chandra
> >
> >
> > "DPassed" wrote:
> >
> > > select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > > ,poitem.flstpdate, poitem.fordqty
> > > from rcmast
> > > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > > left join pomast on rcmast.fpono = pomast.fpono
> > > join poitem on rcmast.fpono = poitem.fpono
> > >
> > > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > > order by rcmast.fpono, rcitem.fitemno
> > >
> > >
> > > "Chandra" wrote:
> > >
> > > > Hi DPassed
> > > > You might have missed the Join Condition when u joined Table-4.
> > > >
> > > > Just check your query once or just post the query that you are facing the
> > > > problem
> > > >
> > > > thanks and regards
> > > > Chandra
> > > >
> > > >
> > > > "DPassed" wrote:
> > > >
> > > > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > > > with three of them I can get the data I need. But when I try adding the
> > > > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > > > or how can I only return want I need?|||Again thanks for your help, but didn't change the results. I have desided to
just us two of the tables and get as much info out of them as possible.
Thanks again, Have a great day!!
"Chandra" wrote:
> Hi,
> Can you try this Now:
> select
> distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> ,poitem.flstpdate, poitem.fordqty
> from rcmast
> right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> left join pomast on pomast.fpono = rcmast.fpono
> join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> regards
> Chandra
>
> "DPassed" wrote:
> > Didn't change rows returned. I do thank you for your help.
> >
> > "Chandra" wrote:
> >
> > > Hi
> > > Try this Query Now:
> > >
> > > select
> > > distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > > ,poitem.flstpdate, poitem.fordqty
> > > from rcmast
> > > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > > left join pomast on rcmast.fpono = pomast.fpono
> > > --CHANGED HERE
> > > join poitem on rcmast.fpono = poitem.fpono AND pomast.fpono = poitem.fpono
> > > --CHANGED HERE
> > > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > >
> > > order by rcmast.fpono, rcitem.fitemno
> > >
> > >
> > > thanks and regards
> > > chandra
> > >
> > >
> > > "DPassed" wrote:
> > >
> > > > select distinct rcmast.fpono, rcitem.fitemno, rcitem.fpartno,
> > > > rcmast.fdaterecv,rcitem.freceiver, rcitem.fqtyrecv,
> > > > rcitem.fucost, rcmast.fvendno, rcmast.fcompany, pomast.forddate
> > > > ,poitem.flstpdate, poitem.fordqty
> > > > from rcmast
> > > > right JOIN rcitem ON rcitem.freceiver = rcmast.freceiver
> > > > left join pomast on rcmast.fpono = pomast.fpono
> > > > join poitem on rcmast.fpono = poitem.fpono
> > > >
> > > > Where rcmast.fdaterecv between '02/28/2005' and '03/01/2005'
> > > > order by rcmast.fpono, rcitem.fitemno
> > > >
> > > >
> > > > "Chandra" wrote:
> > > >
> > > > > Hi DPassed
> > > > > You might have missed the Join Condition when u joined Table-4.
> > > > >
> > > > > Just check your query once or just post the query that you are facing the
> > > > > problem
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > > "DPassed" wrote:
> > > > >
> > > > > > I am trying to pull data from 4 tables. When I query them and us distinct
> > > > > > with three of them I can get the data I need. But when I try adding the
> > > > > > fourth one I get more rows then I need/or many dup roles. Want am I missing
> > > > > > or how can I only return want I need?