SQL Query Help needed

Guys help needed,



I have a table X

DocNo | InvNo | VAT | Anount
----------------------------------------
1 | a1 | 7 | 100
1 | a1 | 22 | 200
2 | a2 | 0 | 50
3 | a3 | 22 | 300
3 | a3 | 7 | 10
3 | a3 | 0 | 30



I need to create a view which will look like this

DocNo | InvNo | VAT7 | Am7 | VAT22 | Am22 | VAT0 | Am0
------------------------------------------------------------ -------------
1 | a1 | 7 | 100 | 22 | 200 | 0
| 0
2 | a2 | 0 | 0 | 0 | 0 |
0 | 50
3 | a3 | 7 | 10 | 22 | 300 | 0
| 30


Thanks
Paul

Re: SQL Query Help needed

Do you need the SQL code to add additional columns to the table? Do you
have the ability to modify the table directly through your web hosting?

Re: SQL Query Help needed

Mwahahahahaaahaa wrote:
> Do you need the SQL code to add additional columns to the table? Do you
> have the ability to modify the table directly through your web hosting?

Now why would he want to add extra columns to his table?
All the data in the results table is already in the input table. He is
asking for a view that aggregates the information for a single document
into one result row.

Re: SQL Query Help needed

Pawe=B3 Zalewski wrote:
> Guys help needed,
>
>
>
> I have a table X
>
> DocNo | InvNo | VAT | Anount
> ----------------------------------------
> 1 | a1 | 7 | 100
> 1 | a1 | 22 | 200
> 2 | a2 | 0 | 50
> 3 | a3 | 22 | 300
> 3 | a3 | 7 | 10
> 3 | a3 | 0 | 30
>
>
>
> I need to create a view which will look like this
>
> DocNo | InvNo | VAT7 | Am7 | VAT22 | Am22 | VAT0 | Am0
> ------------------------------------------------------------ -------------
> 1 | a1 | 7 | 100 | 22 | 200 | 0
> | 0
> 2 | a2 | 0 | 0 | 0 | 0 =
|
> 0 | 50
> 3 | a3 | 7 | 10 | 22 | 300 | =
0
> | 30
>
>
> Thanks
> Paul

Since there appears to be a 1-1 relationship between DocNo and InvNo,
you really should normalise this and remove one or other to a separate
table.
However apart from that, if you turn the following query into a view,
it shoud do what you ask:
(I assumed that Anount was supposed to be Amount)

SELECT
xi.DocNo,
xi.InvNo,
IFNULL(x7.VAT,0) VAT7,
IFNULL(x7.Amount,0) Am7,
IFNULL(x22.VAT,0) VAT22,
IFNULL(x22.Amount,0) Am22,
IFNULL(x0.VAT,0) VAT0,
IFNULL(x0.Amount,0) Am0
FROM X xi
LEFT JOIN X x7 ON xi.InvNo =3D x7.InvNo AND x7.VAT =3D 7
LEFT JOIN X x22 ON xi.InvNo =3D x22.InvNo AND x22.VAT =3D 22
LEFT JOIN X x0 ON xi.InvNo =3D x0.InvNo AND x0.VAT =3D 0

Re: SQL Query Help needed

Just realised that there was a word missing. It should say:

SELECT DISTINCT
xi.DocNo,
xi.InvNo,
IFNULL(x7.VAT,0) VAT7,
IFNULL(x7.Amount,0) Am7,
IFNULL(x22.VAT,0) VAT22,
IFNULL(x22.Amount,0) Am22,
IFNULL(x0.VAT,0) VAT0,
IFNULL(x0.Amount,0) Am0
FROM X xi
LEFT JOIN X x7 ON xi.InvNo = x7.InvNo AND x7.VAT = 7
LEFT JOIN X x22 ON xi.InvNo = x22.InvNo AND x22.VAT = 22
LEFT JOIN X x0 ON xi.InvNo = x0.InvNo AND x0.VAT = 0