|
|
Posted by jason.langdale@gmail.com on 08/08/06 18:04
Robert Klemme wrote:
> On 08.08.2006 16:31, jason.langdale@gmail.com wrote:
> > I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5
> > and table B has field 1,2,3,4,5. I want to do a union on these. (I have
> > done so successfully if I stop here) I also want to join table C which
> > has field 1,6,7,8,9. I would like to join on field 1 and bring in the
> > other fields. I can join table C to A or B. I can union table A and B
> > but I do not know how to both union A and B then join C. Can someone
> > please help me? Thanks in advance.
>
> What stops you from joining twice? Can't you just do
>
> select 1,2,3,4,5,6,7,8,9
> from tab_a, tab_c
> where tab_a.1 = tab_c.1
> union all
> select 1,2,3,4,5
> from tab_b, tab_c
> where tab_b.1 = tab_c.1
>
> Alternatively
>
> select 1,2,3,4,5,6,7,8,9
> from (
> select 1,2,3,4,5
> from tab_a
> union all
> select 1,2,3,4,5
> from tab_b
> ) unioned, tab_c
> where unioned.1 = tab_c.1
> ...
>
> Of course you can also create a view for the union and then another one
> for the join - this might be more easier to manage and handle. My guess
> would be that the first approach is more efficient but this depends of
> course on your data.
>
> HTH
>
> robert
Thanks for your help. I have rewritten my query but I get an error. It
might be a basic error but I am a Crystal report designer who suddenly
has to create his own views. Please let me know what you think :)
ERROR:
Server: Msg 8156, Level 16, State 1, Procedure historic_sales_2, Line 2
The column 'SOPNUMBE' was specified multiple times for
'historic_sales_2'.
SQL VIEW:
CREATE VIEW [historic_sales_2] as
SELECT
SOP30200.SOPNUMBE,SOP10200.SOPNUMBE,SOP30200.DOCDATE,SOP30200.MSTRNUMB,SOP30200.CUSTNMBR,SOP30200.CUSTNAME,SOP30200.DOCAMNT,SOP30200.LOCNCODE,SOP30200.VOIDSTTS,SOP30200.SOPTYPE,SOP10200.ITEMNMBR,SOP10200.QUANTITY,SOP10200.UNITPRCE,SOP10200.XTNDPRCE,SOP10200.SLPRSNID,SOP10200.SALSTERR
FROM cvi.dbo.SOP30200,cvi.dbo.SOP10200
WHERE SOP30200.SOPNUMBE = SOP10200.SOPNUMBE
UNION ALL
SELECT
SOP10100.SOPNUMBE,SOP10100.DOCDATE,SOP10100.MSTRNUMB,SOP10100.CUSTNMBR,SOP10100.CUSTNAME,SOP10100.DOCAMNT,
SOP10100.LOCNCODE,SOP10100.VOIDSTTS,SOP10100.SOPTYPE
FROM cvi.dbo.SOP10100,cvi.dbo.SOP10200
WHERE SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
Navigation:
[Reply to this message]
|