multiple joins

am 06.01.2006 10:51:47 von geradeaus

This is a multi-part message in MIME format.

------=_NextPart_000_0010_01C612AF.30A60040
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hello,

I have a problem with the following, first let me explain the database =
situation :

3 tables : One ad has multiple adDate fields, one adDate field has one =
region. E.g.

Ad
adId adTypeId
1 4
2 5
3 4

Addate (level indicates it's a continent, when it's a country, level =3D =
2)
adDateId adId regionId Level
1 1 2 1
2 3 2 1
3 1 3 1
4 2 3 1
5 2 2 1

Region
regionId regionName
2 Europe
3 USA
4 Asia

What do I want to calculate : the number of addates for every (!) region =
and a specific adTypeId

Query : WHERE adTypeId =3D 4, result :
regionName Total
Europe 2
USA 1
Asia 0

Everything works , except the filtering on the adTypeId. :

Query works (without filtering on type) :

SELECT region.regionName, COUNT( addate.regionId ) AS Total
FROM region
LEFT JOIN addate ON (addate.regionId =3D region.regionId AND =
addate.level =3D 1)
GROUP BY addate.regionId

Query doesn't work (with filtering) :

SELECT region.regionName, COUNT( addate.regionId ) AS Total
FROM region
LEFT JOIN addate ON (addate.regionId =3D region.regionId AND =
addate.level =3D 1)
JOIN ad ON (addate.adId =3D ad.adId AND ad.adTypeId =3D 4)
GROUP BY addate.regionId

When I use LEFT JOIN , I get wrong results. when I use a JOIN I only get =
the region for where Total > 0.

Can anybody figure out why? Thanks!

(ps: excuse me for posting in RTF, but otherwhise I couldn't use tabs...)
------=_NextPart_000_0010_01C612AF.30A60040
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

charset=3Diso-8859-1">

Hello,

I have a problem with the following, first let me explain the =
database=20
situation :

3 tables : One ad has multiple adDate fields, one adDate field has =
one=20
region. E.g.

adId adTypeId
1    =20
4
2     5
3     4

Addate (level indicates it's a continent, when it's a country, =
level =
2)
adDateId adId regionId Level
1   &=
nbsp;   =20
    1     2    =20
    1
2       =20
     3     =
2    =20
    1
3        =20
    1     3    =20
    1
4        =20
    2     3    =20
    1
5       =
   =20
2     2     =
   =20
1

Region

regionId     =
regionName
2    =20
        =
Europe
3    =20
        USA
4    =20
        Asia

What do I want to calculate : the number of addates for every =
(!)=20
region and a specific adTypeId

Query : WHERE adTypeId =3D 4, result=20
:
regionName Total
Europe     =
   =20
2
USA            =20
1
Asia             =
0

Everything works , except the filtering on the adTypeId=85 :

Query works (without filtering on type) :

SELECT region.regionName, COUNT( addate.regionId ) AS Total
FROM =

region
LEFT JOIN addate ON (addate.regionId =3D region.regionId AND=20
addate.level =3D 1)
GROUP BY addate.regionId

Query doesn't work (with filtering) :

SELECT region.regionName, COUNT( addate.regionId ) AS Total
FROM =

region
LEFT JOIN addate ON (addate.regionId =3D region.regionId AND=20
addate.level =3D 1)
JOIN ad ON (addate.adId =3D ad.adId AND =
ad.adTypeId =
4)
GROUP BY addate.regionId

When I use LEFT JOIN , I get wrong results=85 when I use a JOIN I =
only get=20
the region for where Total > 0=85

Can anybody figure out why? Thanks!

(ps: excuse me for posting in RTF, but otherwhise I couldn't use=20
tabs...)

------=_NextPart_000_0010_01C612AF.30A60040--

Re: multiple joins

am 06.01.2006 13:37:41 von Shion

Geradeaus wrote:
> Hello,
>
> I have a problem with the following, first let me explain the database situation :
>
> SELECT region.regionName, COUNT( addate.regionId ) AS Total
> FROM region
> LEFT JOIN addate ON (addate.regionId = region.regionId AND addate.level = 1)
> JOIN ad ON (addate.adId = ad.adId AND ad.adTypeId = 4)
> GROUP BY addate.regionId

SELECT region.regionName, COUNT( addate.regionId ) AS Total FROM region LEFT
JOIN addate ON (addate.regionId = region.regionId) JOIN ad ON (addate.adId =
ad.adId) WHERE ad.adTypeId = 4 GROUP BY addate.regionId

You can't have "OPTIONS" in your joins, those you have to place in your WHERE.
http://dev.mysql.com/doc/refman/5.0/en/join.html

//Aho

Re: multiple joins

am 09.01.2006 10:47:39 von geradeaus

"J.O. Aho" schreef in bericht
news:4276klF1h8tplU1@individual.net...
> Geradeaus wrote:
>> Hello,
>>
>> I have a problem with the following, first let me explain the database
>> situation :
>>
>> SELECT region.regionName, COUNT( addate.regionId ) AS Total
>> FROM region
>> LEFT JOIN addate ON (addate.regionId = region.regionId AND addate.level =
>> 1)
>> JOIN ad ON (addate.adId = ad.adId AND ad.adTypeId = 4)
>> GROUP BY addate.regionId
>
> SELECT region.regionName, COUNT( addate.regionId ) AS Total FROM region
> LEFT
> JOIN addate ON (addate.regionId = region.regionId) JOIN ad ON (addate.adId
> =
> ad.adId) WHERE ad.adTypeId = 4 GROUP BY addate.regionId
>
> You can't have "OPTIONS" in your joins, those you have to place in your
> WHERE.
> http://dev.mysql.com/doc/refman/5.0/en/join.html
>
>
> //Aho

Thx for the advice :)