MySQL ODBC Connector - Help with a join query

Author

Help with a join query

Yoed Anis

2006-02-25, 9:43 am

------ =_Part_33222_2319848
7.1140671818182
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all,

I'm trying to do the following.

I have three table:

Table a has address information:
address_id | City | State | Zip
1 Austin TX 78758
2 Dallas TX 77000
3 Galveston TX 77550

Table b has information about the location:
address_id | Location_id | Location_name
1 1 The Place
1 2 The Place Before
2 3 A shop

Table c has montlhy sales history
Locationid | MonthYear | Sales
1 2005-01-01 299
1 2005-02-01 100
1 2005-10-01 300
2 2005-01-01 154
3 2005-10-10 99

Not every location has sales information.
I am trying to create a query where I can SELECT the Locationname, City,
State, Zip, and the SUM(sales) if the place has sales. So far, despite
playing around with joins for more hours than one should ever dedicated to
the matter, I haven't been able to include SUM(sales) without excluding
listings without sales.

So far this is my best shot:
SELECT locationname, city, state, zip, SUM(sales) as 'Sales'
FROM a, b LEFT JOIN c ON (b.locationid =3D c.locationid)
WHERE a.address_id =3D b.address_id AND monthyear > "2005-01-01"
GROUP BY c.locationid

This however, will return only records with Sales and not those without it.
I haven't been able to force adding empty rows from table c... Doing "AND
c.locationid IS NULL" returns no results at all.

Any help would GREATLY be appreciated!!!

Thank you!!

------ =_Part_33222_2319848
7.1140671818182--

gerald_clark

2006-02-25, 9:43 am

Yoed Anis wrote:

>Hi all,
>
>I'm trying to do the following.
>
>I have three table:
>
>Table a has address information:
>address_id | City | State | Zip
>1 Austin TX 78758
>2 Dallas TX 77000
>3 Galveston TX 77550
>
>Table b has information about the location:
>address_id | Location_id | Location_name
>1 1 The Place
>1 2 The Place Before
>2 3 A shop
>
>Table c has montlhy sales history
>Locationid | MonthYear | Sales
>1 2005-01-01 299
>1 2005-02-01 100
>1 2005-10-01 300
>2 2005-01-01 154
>3 2005-10-10 99
>
>Not every location has sales information.
>I am trying to create a query where I can SELECT the Locationname, City,
>State, Zip, and the SUM(sales) if the place has sales. So far, despite
>playing around with joins for more hours than one should ever dedicated to
>the matter, I haven't been able to include SUM(sales) without excluding
>listings without sales.
>
>So far this is my best shot:
>SELECT locationname, city, state, zip, SUM(sales) as 'Sales'
>FROM a, b LEFT JOIN c ON (b.locationid = c.locationid)
>WHERE a.address_id = b.address_id AND monthyear > "2005-01-01"
>GROUP BY c.locationid
>
>
>
SELECT locationname, city, state, zip, SUM(sales) as 'Sales'
FROM a INNER JOIN b ON a.address_id = b.address_id
LEFT JOIN c ON b.locationid = c.locationid and monthyear>"2005-01-10"
GROUP BY c.locationid

>This however, will return only records with Sales and not those without it.
>I haven't been able to force adding empty rows from table c... Doing "AND
>c.locationid IS NULL" returns no results at all.
>
>Any help would GREATLY be appreciated!!!
>
>Thank you!!
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links