Help Constructing Query

------=_NextPart_000_0000_01C51FF8.84EA1150
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hello,



I am trying to read all the values in one column, the listingID, of a table
(listings) that have a "MI" in the "state" column and compare those
listingID values to another table (invotes) to see how many times each
listingID occurs.



Then, I need to print out the results in decrementing order and ALSO list
the ones that never occurred (count of 0) in table "invotes" at the end.
When I'm printing them out I don't need to print the listingID, I need to
print other column values that are in the same row as the listingID of table
"listings".



I hope this makes sense.



Can anyone please help?



Best regards,

Ryan




------=_NextPart_000_0000_01C51FF8.84EA1150--

Re: Help Constructing Query

Just to be sure I understand what you exactly want give me some example
data.

Sample rows in table listings.
Sample rows in table invotes

Resultset based on sample rows.

Tell me what mysql version you are using.

I will try to help

Ryan Schefke wrote:

>Hello,
>
>
>
>I am trying to read all the values in one column, the listingID, of a table
>(listings) that have a "MI" in the "state" column and compare those
>listingID values to another table (invotes) to see how many times each
>listingID occurs.
>
>
>
>Then, I need to print out the results in decrementing order and ALSO list
>the ones that never occurred (count of 0) in table "invotes" at the end.
>When I'm printing them out I don't need to print the listingID, I need to
>print other column values that are in the same row as the listingID of table
>"listings".
>
>
>
>I hope this makes sense.
>
>
>
>Can anyone please help?
>
>
>
>Best regards,
>
>Ryan
>
>
>
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Help Constructing Query

Hello Juan - Thanks! See below:

MySQL Version: MySQL 4.0.15-standard

Sample rows in table invotes:
*this is a sample row. Additional rows will have different invoteID (primary
key being auto-incremented), ip, and date; however, the listingID COULD be
the same. So, I want to count the occurrences of it.

Column Value
invoteID 3 (primary key auto increment)
ip 63.246.173.212
listingID 5
date 2005-02-22


Sample rows in table listings.

Column Value
listingID 1 (primary key auto increment)
siteAddress http://www.tgwedding.com
siteTitle Don's Studio Photography
siteDescr Professional photography for weddings and family. ...
state MI
dateAdded 2005-02-17 23:16:50




See
http://www.weddingarea.com/topweddingsites/best-wedding-webs ites.php?st=CT
which is what I currently have. This lists the businesses in the state of
"CT" (see st=CT at end of URL), which reads from table "listings" of rows
with value "CT" in "state" column. Then, counts the occurances of this
listingID in table invotes and prints the information out. However, it's
ordering them by invotes (number of occurances of listingID in table
invotes).



I can send you the current script for the above URL that I have so you can
see my existing queries.




-----Original Message-----
From: Juan Pedro Reyes Molina [mailto:jreymol@terra.es]
Sent: Thursday, March 03, 2005 2:21 PM
To: win32@lists.mysql.com
Subject: Re: Help Constructing Query


Just to be sure I understand what you exactly want give me some example
data.

Sample rows in table listings.
Sample rows in table invotes

Resultset based on sample rows.

Tell me what mysql version you are using.

I will try to help

Ryan Schefke wrote:

>Hello,
>
>
>
>I am trying to read all the values in one column, the listingID, of a table
>(listings) that have a "MI" in the "state" column and compare those
>listingID values to another table (invotes) to see how many times each
>listingID occurs.
>
>
>
>Then, I need to print out the results in decrementing order and ALSO list
>the ones that never occurred (count of 0) in table "invotes" at the end.
>When I'm printing them out I don't need to print the listingID, I need to
>print other column values that are in the same row as the listingID of
table
>"listings".
>
>
>
>I hope this makes sense.
>
>
>
>Can anyone please help?
>
>
>
>Best regards,
>
>Ryan
>
>
>
>
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=ryan@triomfgroup.com






--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

Re: Help Constructing Query

Ryan,

I'm going to substitute a couple of generic columns from invotes, as I don'=
t know your table structure.
Let's give it a shot:

SELECT listings.listingid,
listings.state, =

listings.column1, =

listings.column2, =

listings.column3,
COUNT(invotes.listingID) as count =

FROM listings
LEFT OUTER JOIN invotes ON listings.listingID =3D invotes.listingID
WHERE listings.state =3D 'MI'
GROUP BY listings.listingid,
listings.state, =

listings.column1, =

listings.column2, =

listings.column3
ORDER BY 5 DESC

Here's the key. List all columns from listings that you wish to report. I h=
ave included listings.state, although it's noit really necessary for your p=
articular request. =


The 'LEFT OUTER JOIN' will include all rows from listings, even if there is=
no entry in invotes. =


The 'GROUP BY' clause needs to include all columns from the query that are =
not aggregate functions. In this case, COUNT(invotes.listingID) is the only=
aggregate function. Do not use COUNT(*), as it will count null values--the=
lowest count returned will be 1 not 0.

The 'ORDER BY' clause specifies the sort order by the column number from th=
e query. 'DESC' says sort in descending order.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: "Ryan Schefke"
> To: win32@lists.mysql.com
> Date: Thu, Mar-3-2005 12:53 PM
> Subject: Help Constructing Query
>
> Hello,
> =

> =

> =

> I am trying to read all the values in one column, the listingID, of a =

> table
> (listings) that have a "MI" in the "state" column and compare those
> listingID values to another table (invotes) to see how many times each
> listingID occurs. =

> =

> =

> =

> Then, I need to print out the results in decrementing order and ALSO =

> list
> the ones that never occurred (count of 0) in table "invotes" at the =

> end.
> When I'm printing them out I don't need to print the listingID, I need =

> to
> print other column values that are in the same row as the listingID of =

> table
> "listings".
> =

> =

> =

> I hope this makes sense.
> =

> =

> =

> Can anyone please help?
> =

> =

> =

> Best regards,
> =

> Ryan
> =

> =

> =

> =



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: Help Constructing Query

Randy - I can't believe it worked! YES!

I had to change one value though, in the ORDER BY 5 DESC, I had to change to
ORDER BY count DESC.

Thanks a lot!

-----Original Message-----
From: Randy Clamons [mailto:randy@novaspace.com]
Sent: Thursday, March 03, 2005 2:42 PM
To: Ryan Schefke; win32@lists.mysql.com
Subject: Re: Help Constructing Query

Ryan,

I'm going to substitute a couple of generic columns from invotes, as I don't
know your table structure.
Let's give it a shot:

SELECT listings.listingid,
listings.state,
listings.column1,
listings.column2,
listings.column3,
COUNT(invotes.listingID) as count
FROM listings
LEFT OUTER JOIN invotes ON listings.listingID = invotes.listingID
WHERE listings.state = 'MI'
GROUP BY listings.listingid,
listings.state,
listings.column1,
listings.column2,
listings.column3
ORDER BY 5 DESC

Here's the key. List all columns from listings that you wish to report. I
have included listings.state, although it's noit really necessary for your
particular request.

The 'LEFT OUTER JOIN' will include all rows from listings, even if there is
no entry in invotes.

The 'GROUP BY' clause needs to include all columns from the query that are
not aggregate functions. In this case, COUNT(invotes.listingID) is the only
aggregate function. Do not use COUNT(*), as it will count null values--the
lowest count returned will be 1 not 0.

The 'ORDER BY' clause specifies the sort order by the column number from the
query. 'DESC' says sort in descending order.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: "Ryan Schefke"
> To: win32@lists.mysql.com
> Date: Thu, Mar-3-2005 12:53 PM
> Subject: Help Constructing Query
>
> Hello,
>
>
>
> I am trying to read all the values in one column, the listingID, of a
> table
> (listings) that have a "MI" in the "state" column and compare those
> listingID values to another table (invotes) to see how many times each
> listingID occurs.
>
>
>
> Then, I need to print out the results in decrementing order and ALSO
> list
> the ones that never occurred (count of 0) in table "invotes" at the
> end.
> When I'm printing them out I don't need to print the listingID, I need
> to
> print other column values that are in the same row as the listingID of
> table
> "listings".
>
>
>
> I hope this makes sense.
>
>
>
> Can anyone please help?
>
>
>
> Best regards,
>
> Ryan
>
>
>
>






--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Help Constructing Query

Oops! I added a column to the query after writing the 'ORDER BY' clause. Yo=
u can use either the column number or the column name (or even a formula) i=
n either the 'GROUP BY' or the 'ORDER BY'. The column number is most useful=
when dealing with a 'UNION'.

Glad it worked.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: "Ryan Schefke"
> To: win32@lists.mysql.com
> Date: Thu, Mar-3-2005 2:07 PM
> Subject: RE: Help Constructing Query
>
> Randy - I can't believe it worked! YES!
> =

> I had to change one value though, in the ORDER BY 5 DESC, I had to =

> change to
> ORDER BY count DESC.
> =

> Thanks a lot!
> =

> -----Original Message-----
> From: Randy Clamons [mailto:randy@novaspace.com] =

> Sent: Thursday, March 03, 2005 2:42 PM
> To: Ryan Schefke; win32@lists.mysql.com
> Subject: Re: Help Constructing Query
> =

> Ryan,
> =

> I'm going to substitute a couple of generic columns from invotes, as I =

> don't
> know your table structure.
> Let's give it a shot:
> =

> SELECT listings.listingid,
> listings.state, =

> listings.column1, =

> listings.column2, =

> listings.column3,
> COUNT(invotes.listingID) as count =

> FROM listings
> LEFT OUTER JOIN invotes ON listings.listingID =3D invotes.listingID
> WHERE listings.state =3D 'MI'
> GROUP BY listings.listingid,
> listings.state, =

> listings.column1, =

> listings.column2, =

> listings.column3
> ORDER BY 5 DESC
> =

> Here's the key. List all columns from listings that you wish to report. =

> I
> have included listings.state, although it's noit really necessary for =

> your
> particular request. =

> =

> The 'LEFT OUTER JOIN' will include all rows from listings, even if =

> there is
> no entry in invotes. =

> =

> The 'GROUP BY' clause needs to include all columns from the query that =

> are
> not aggregate functions. In this case, COUNT(invotes.listingID) is the =

> only
> aggregate function. Do not use COUNT(*), as it will count null =

> values--the
> lowest count returned will be 1 not 0.
> =

> The 'ORDER BY' clause specifies the sort order by the column number =

> from the
> query. 'DESC' says sort in descending order.
> =

> Randy Clamons
> Systems Programming
> Novaspace.com
> =

> =

> > ------------Original Message------------
> > From: "Ryan Schefke"
> > To: win32@lists.mysql.com
> > Date: Thu, Mar-3-2005 12:53 PM
> > Subject: Help Constructing Query
> >
> > Hello,
> > =

> > =

> > =

> > I am trying to read all the values in one column, the listingID, of a =

> =

> > table
> > (listings) that have a "MI" in the "state" column and compare those
> > listingID values to another table (invotes) to see how many times =

> each
> > listingID occurs. =

> > =

> > =

> > =

> > Then, I need to print out the results in decrementing order and ALSO =

> > list
> > the ones that never occurred (count of 0) in table "invotes" at the =

> > end.
> > When I'm printing them out I don't need to print the listingID, I =

> need =

> > to
> > print other column values that are in the same row as the listingID =

> of =

> > table
> > "listings".
> > =

> > =

> > =

> > I hope this makes sense.
> > =

> > =

> > =

> > Can anyone please help?
> > =

> > =

> > =

> > Best regards,
> > =

> > Ryan
> > =

> > =

> > =

> > =

> =

> =

> =

> =

> =

> =

> -- =

> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: =

> http://lists.mysql.com/win32?unsub=3Drandy@novaspace.com
> =

> =



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: Help Constructing Query

Randy - Your last statement confused me a bit. If your statement is true:

" You can use either the column number or the column name (or even a
formula) in either the 'GROUP BY' or the 'ORDER BY'"

Then how come it didn't work when I used

ORDER BY 6 DESC

....instead of

ORDER BY count DESC

Ryan

-----Original Message-----
From: Randy Clamons [mailto:randy@novaspace.com]
Sent: Thursday, March 03, 2005 3:41 PM
To: Ryan Schefke; win32@lists.mysql.com
Subject: RE: Help Constructing Query

Oops! I added a column to the query after writing the 'ORDER BY' clause. You
can use either the column number or the column name (or even a formula) in
either the 'GROUP BY' or the 'ORDER BY'. The column number is most useful
when dealing with a 'UNION'.

Glad it worked.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: "Ryan Schefke"
> To: win32@lists.mysql.com
> Date: Thu, Mar-3-2005 2:07 PM
> Subject: RE: Help Constructing Query
>
> Randy - I can't believe it worked! YES!
>
> I had to change one value though, in the ORDER BY 5 DESC, I had to
> change to
> ORDER BY count DESC.
>
> Thanks a lot!
>
> -----Original Message-----
> From: Randy Clamons [mailto:randy@novaspace.com]
> Sent: Thursday, March 03, 2005 2:42 PM
> To: Ryan Schefke; win32@lists.mysql.com
> Subject: Re: Help Constructing Query
>
> Ryan,
>
> I'm going to substitute a couple of generic columns from invotes, as I
> don't
> know your table structure.
> Let's give it a shot:
>
> SELECT listings.listingid,
> listings.state,
> listings.column1,
> listings.column2,
> listings.column3,
> COUNT(invotes.listingID) as count
> FROM listings
> LEFT OUTER JOIN invotes ON listings.listingID = invotes.listingID
> WHERE listings.state = 'MI'
> GROUP BY listings.listingid,
> listings.state,
> listings.column1,
> listings.column2,
> listings.column3
> ORDER BY 5 DESC
>
> Here's the key. List all columns from listings that you wish to report.
> I
> have included listings.state, although it's noit really necessary for
> your
> particular request.
>
> The 'LEFT OUTER JOIN' will include all rows from listings, even if
> there is
> no entry in invotes.
>
> The 'GROUP BY' clause needs to include all columns from the query that
> are
> not aggregate functions. In this case, COUNT(invotes.listingID) is the
> only
> aggregate function. Do not use COUNT(*), as it will count null
> values--the
> lowest count returned will be 1 not 0.
>
> The 'ORDER BY' clause specifies the sort order by the column number
> from the
> query. 'DESC' says sort in descending order.
>
> Randy Clamons
> Systems Programming
> Novaspace.com
>
>
> > ------------Original Message------------
> > From: "Ryan Schefke"
> > To: win32@lists.mysql.com
> > Date: Thu, Mar-3-2005 12:53 PM
> > Subject: Help Constructing Query
> >
> > Hello,
> >
> >
> >
> > I am trying to read all the values in one column, the listingID, of a
>
> > table
> > (listings) that have a "MI" in the "state" column and compare those
> > listingID values to another table (invotes) to see how many times
> each
> > listingID occurs.
> >
> >
> >
> > Then, I need to print out the results in decrementing order and ALSO
> > list
> > the ones that never occurred (count of 0) in table "invotes" at the
> > end.
> > When I'm printing them out I don't need to print the listingID, I
> need
> > to
> > print other column values that are in the same row as the listingID
> of
> > table
> > "listings".
> >
> >
> >
> > I hope this makes sense.
> >
> >
> >
> > Can anyone please help?
> >
> >
> >
> > Best regards,
> >
> > Ryan
> >
> >
> >
> >
>
>
>
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=randy@novaspace.com
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=lists@tek-tools.com






--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Help Constructing Query

I'm not sure. Why don't you send your query? I should be able to explain af=
ter seeing it.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: "Ryan Schefke"
> To: randy@novaspace.com, win32@lists.mysql.com
> Date: Thu, Mar-3-2005 2:46 PM
> Subject: RE: Help Constructing Query
>
> Randy - Your last statement confused me a bit. If your statement is =

> true:
> =

> " You can use either the column number or the column name (or even a
> formula) in either the 'GROUP BY' or the 'ORDER BY'"
> =

> Then how come it didn't work when I used
> =

> ORDER BY 6 DESC
> =

> ...instead of
> =

> ORDER BY count DESC
> =

> Ryan
> =

> -----Original Message-----
> From: Randy Clamons [mailto:randy@novaspace.com] =

> Sent: Thursday, March 03, 2005 3:41 PM
> To: Ryan Schefke; win32@lists.mysql.com
> Subject: RE: Help Constructing Query
> =

> Oops! I added a column to the query after writing the 'ORDER BY' =

> clause. You
> can use either the column number or the column name (or even a formula) =

> in
> either the 'GROUP BY' or the 'ORDER BY'. The column number is most =

> useful
> when dealing with a 'UNION'.
> =

> Glad it worked.
> =

> Randy Clamons
> Systems Programming
> Novaspace.com
> =

> =

> > ------------Original Message------------
> > From: "Ryan Schefke"
> > To: win32@lists.mysql.com
> > Date: Thu, Mar-3-2005 2:07 PM
> > Subject: RE: Help Constructing Query
> >
> > Randy - I can't believe it worked! YES!
> > =

> > I had to change one value though, in the ORDER BY 5 DESC, I had to =

> > change to
> > ORDER BY count DESC.
> > =

> > Thanks a lot!
> > =

> > -----Original Message-----
> > From: Randy Clamons [mailto:randy@novaspace.com] =

> > Sent: Thursday, March 03, 2005 2:42 PM
> > To: Ryan Schefke; win32@lists.mysql.com
> > Subject: Re: Help Constructing Query
> > =

> > Ryan,
> > =

> > I'm going to substitute a couple of generic columns from invotes, as =

> I =

> > don't
> > know your table structure.
> > Let's give it a shot:
> > =

> > SELECT listings.listingid,
> > listings.state, =

> > listings.column1, =

> > listings.column2, =

> > listings.column3,
> > COUNT(invotes.listingID) as count =

> > FROM listings
> > LEFT OUTER JOIN invotes ON listings.listingID =3D invotes.listingID
> > WHERE listings.state =3D 'MI'
> > GROUP BY listings.listingid,
> > listings.state, =

> > listings.column1, =

> > listings.column2, =

> > listings.column3
> > ORDER BY 5 DESC
> > =

> > Here's the key. List all columns from listings that you wish to =

> report. =

> > I
> > have included listings.state, although it's noit really necessary for =

> =

> > your
> > particular request. =

> > =

> > The 'LEFT OUTER JOIN' will include all rows from listings, even if =

> > there is
> > no entry in invotes. =

> > =

> > The 'GROUP BY' clause needs to include all columns from the query =

> that =

> > are
> > not aggregate functions. In this case, COUNT(invotes.listingID) is =

> the =

> > only
> > aggregate function. Do not use COUNT(*), as it will count null =

> > values--the
> > lowest count returned will be 1 not 0.
> > =

> > The 'ORDER BY' clause specifies the sort order by the column number =

> > from the
> > query. 'DESC' says sort in descending order.
> > =

> > Randy Clamons
> > Systems Programming
> > Novaspace.com
> > =

> > =

> > > ------------Original Message------------
> > > From: "Ryan Schefke"
> > > To: win32@lists.mysql.com
> > > Date: Thu, Mar-3-2005 12:53 PM
> > > Subject: Help Constructing Query
> > >
> > > Hello,
> > > =

> > > =

> > > =

> > > I am trying to read all the values in one column, the listingID, of =

> a =

> > =

> > > table
> > > (listings) that have a "MI" in the "state" column and compare those
> > > listingID values to another table (invotes) to see how many times =

> > each
> > > listingID occurs. =

> > > =

> > > =

> > > =

> > > Then, I need to print out the results in decrementing order and =

> ALSO =

> > > list
> > > the ones that never occurred (count of 0) in table "invotes" at the =

> =

> > > end.
> > > When I'm printing them out I don't need to print the listingID, I =

> > need =

> > > to
> > > print other column values that are in the same row as the listingID =

> =

> > of =

> > > table
> > > "listings".
> > > =

> > > =

> > > =

> > > I hope this makes sense.
> > > =

> > > =

> > > =

> > > Can anyone please help?
> > > =

> > > =

> > > =

> > > Best regards,
> > > =

> > > Ryan
> > > =

> > > =

> > > =

> > > =

> > =

> > =

> > =

> > =

> > =

> > =

> > -- =

> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe: =

> > http://lists.mysql.com/win32?unsub=3Drandy@novaspace.com
> > =

> > =

> =

> =

> -- =

> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: =

> http://lists.mysql.com/win32?unsub=3Dlists@tek-tools.com
> =

> =

> =

> =

> =

> =

> -- =

> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe: =

> http://lists.mysql.com/win32?unsub=3Dbrian@tek-tools.com
> =

> =



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: Help Constructing Query

Randy - did you get my email with the .php file attached? If not, I'll
resend.

/* THE QUERY */
$query_state = "SELECT listings.listingID, listings.state,
listings.preferred, listings.siteAddress, listings.siteTitle,
listings.siteDescription, COUNT(invotes.listingID) AS count FROM listings
LEFT OUTER JOIN invotes ON listings.listingID = invotes.listingID WHERE
listings.state ='$st' GROUP BY listings.listingID, listings.state,
listings.preferred, listings.siteAddress, listings.siteTitle,
listings.siteDescription ORDER BY count DESC";
/* THE QUERY */

-----Original Message-----
From: Randy Clamons [mailto:randy@novaspace.com]
Sent: Thursday, March 03, 2005 3:55 PM
To: Ryan Schefke; win32@lists.mysql.com
Subject: RE: Help Constructing Query

I'm not sure. Why don't you send your query? I should be able to explain
after seeing it.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: "Ryan Schefke"
> To: randy@novaspace.com, win32@lists.mysql.com
> Date: Thu, Mar-3-2005 2:46 PM
> Subject: RE: Help Constructing Query
>
> Randy - Your last statement confused me a bit. If your statement is
> true:
>
> " You can use either the column number or the column name (or even a
> formula) in either the 'GROUP BY' or the 'ORDER BY'"
>
> Then how come it didn't work when I used
>
> ORDER BY 6 DESC
>
> ...instead of
>
> ORDER BY count DESC
>
> Ryan
>
> -----Original Message-----
> From: Randy Clamons [mailto:randy@novaspace.com]
> Sent: Thursday, March 03, 2005 3:41 PM
> To: Ryan Schefke; win32@lists.mysql.com
> Subject: RE: Help Constructing Query
>
> Oops! I added a column to the query after writing the 'ORDER BY'
> clause. You
> can use either the column number or the column name (or even a formula)
> in
> either the 'GROUP BY' or the 'ORDER BY'. The column number is most
> useful
> when dealing with a 'UNION'.
>
> Glad it worked.
>
> Randy Clamons
> Systems Programming
> Novaspace.com
>
>
> > ------------Original Message------------
> > From: "Ryan Schefke"
> > To: win32@lists.mysql.com
> > Date: Thu, Mar-3-2005 2:07 PM
> > Subject: RE: Help Constructing Query
> >
> > Randy - I can't believe it worked! YES!
> >
> > I had to change one value though, in the ORDER BY 5 DESC, I had to
> > change to
> > ORDER BY count DESC.
> >
> > Thanks a lot!
> >
> > -----Original Message-----
> > From: Randy Clamons [mailto:randy@novaspace.com]
> > Sent: Thursday, March 03, 2005 2:42 PM
> > To: Ryan Schefke; win32@lists.mysql.com
> > Subject: Re: Help Constructing Query
> >
> > Ryan,
> >
> > I'm going to substitute a couple of generic columns from invotes, as
> I
> > don't
> > know your table structure.
> > Let's give it a shot:
> >
> > SELECT listings.listingid,
> > listings.state,
> > listings.column1,
> > listings.column2,
> > listings.column3,
> > COUNT(invotes.listingID) as count
> > FROM listings
> > LEFT OUTER JOIN invotes ON listings.listingID = invotes.listingID
> > WHERE listings.state = 'MI'
> > GROUP BY listings.listingid,
> > listings.state,
> > listings.column1,
> > listings.column2,
> > listings.column3
> > ORDER BY 5 DESC
> >
> > Here's the key. List all columns from listings that you wish to
> report.
> > I
> > have included listings.state, although it's noit really necessary for
>
> > your
> > particular request.
> >
> > The 'LEFT OUTER JOIN' will include all rows from listings, even if
> > there is
> > no entry in invotes.
> >
> > The 'GROUP BY' clause needs to include all columns from the query
> that
> > are
> > not aggregate functions. In this case, COUNT(invotes.listingID) is
> the
> > only
> > aggregate function. Do not use COUNT(*), as it will count null
> > values--the
> > lowest count returned will be 1 not 0.
> >
> > The 'ORDER BY' clause specifies the sort order by the column number
> > from the
> > query. 'DESC' says sort in descending order.
> >
> > Randy Clamons
> > Systems Programming
> > Novaspace.com
> >
> >
> > > ------------Original Message------------
> > > From: "Ryan Schefke"
> > > To: win32@lists.mysql.com
> > > Date: Thu, Mar-3-2005 12:53 PM
> > > Subject: Help Constructing Query
> > >
> > > Hello,
> > >
> > >
> > >
> > > I am trying to read all the values in one column, the listingID, of
> a
> >
> > > table
> > > (listings) that have a "MI" in the "state" column and compare those
> > > listingID values to another table (invotes) to see how many times
> > each
> > > listingID occurs.
> > >
> > >
> > >
> > > Then, I need to print out the results in decrementing order and
> ALSO
> > > list
> > > the ones that never occurred (count of 0) in table "invotes" at the
>
> > > end.
> > > When I'm printing them out I don't need to print the listingID, I
> > need
> > > to
> > > print other column values that are in the same row as the listingID
>
> > of
> > > table
> > > "listings".
> > >
> > >
> > >
> > > I hope this makes sense.
> > >
> > >
> > >
> > > Can anyone please help?
> > >
> > >
> > >
> > > Best regards,
> > >
> > > Ryan
> > >
> > >
> > >
> > >
> >
> >
> >
> >
> >
> >
> > --
> > MySQL Windows Mailing List
> > For list archives: http://lists.mysql.com/win32
> > To unsubscribe:
> > http://lists.mysql.com/win32?unsub=randy@novaspace.com
> >
> >
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=lists@tek-tools.com
>
>
>
>
>
>
> --
> MySQL Windows Mailing List
> For list archives: http://lists.mysql.com/win32
> To unsubscribe:
> http://lists.mysql.com/win32?unsub=brian@tek-tools.com
>
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=ryan@triomfgroup.com






--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org

RE: Help Constructing Query

The column number must match the number of the column in the select_express=
ion (column list). In your query, 'count' is column number 7. If you specif=
y "ORDER BY 6", the list will be sorted by the siteDescription. If you spec=
ify "ORDER BY 7", your list will be sorted by the count.

I'm pretty sure this list does not forward attachments.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: "Ryan Schefke"
> To: randy@novaspace.com, win32@lists.mysql.com
> Date: Thu, Mar-3-2005 2:58 PM
> Subject: RE: Help Constructing Query
>
> Randy - did you get my email with the .php file attached? If not, I'll
> resend.
> =

> /* THE QUERY */
> $query_state =3D "SELECT listings.listingID, listings.state,
> listings.preferred, listings.siteAddress, listings.siteTitle,
> listings.siteDescription, COUNT(invotes.listingID) AS count FROM =

> listings
> LEFT OUTER JOIN invotes ON listings.listingID =3D invotes.listingID WHERE
> listings.state =3D'$st' GROUP BY listings.listingID, listings.state,
> listings.preferred, listings.siteAddress, listings.siteTitle,
> listings.siteDescription ORDER BY count DESC"; =

> /* THE QUERY */
> =

> -----Original Message-----
> From: Randy Clamons [mailto:randy@novaspace.com] =

> Sent: Thursday, March 03, 2005 3:55 PM
> To: Ryan Schefke; win32@lists.mysql.com
> Subject: RE: Help Constructing Query
> =

> I'm not sure. Why don't you send your query? I should be able to =

> explain
> after seeing it.
> =

> Randy Clamons
> Systems Programming
> Novaspace.com
> =

> =

> > ------------Original Message------------
> > From: "Ryan Schefke"
> > To: randy@novaspace.com, win32@lists.mysql.com
> > Date: Thu, Mar-3-2005 2:46 PM
> > Subject: RE: Help Constructing Query
> >
> > Randy - Your last statement confused me a bit. If your statement is =

> > true:
> > =

> > " You can use either the column number or the column name (or even a
> > formula) in either the 'GROUP BY' or the 'ORDER BY'"
> > =

> > Then how come it didn't work when I used
> > =

> > ORDER BY 6 DESC
> > =

> > ...instead of
> > =

> > ORDER BY count DESC
> > =

> > Ryan
> > =

> > -----Original Message-----
> > From: Randy Clamons [mailto:randy@novaspace.com] =

> > Sent: Thursday, March 03, 2005 3:41 PM
> > To: Ryan Schefke; win32@lists.mysql.com
> > Subject: RE: Help Constructing Query
> > =

> > Oops! I added a column to the query after writing the 'ORDER BY' =

> > clause. You
> > can use either the column number or the column name (or even a =

> formula) =

> > in
> > either the 'GROUP BY' or the 'ORDER BY'. The column number is most =

> > useful
> > when dealing with a 'UNION'.
> > =

> > Glad it worked.
> > =

> > Randy Clamons
> > Systems Programming
> > Novaspace.com
> > =

> > =

> > > ------------Original Message------------
> > > From: "Ryan Schefke"
> > > To: win32@lists.mysql.com
> > > Date: Thu, Mar-3-2005 2:07 PM
> > > Subject: RE: Help Constructing Query
> > >
> > > Randy - I can't believe it worked! YES!
> > > =

> > > I had to change one value though, in the ORDER BY 5 DESC, I had to =

> > > change to
> > > ORDER BY count DESC.
> > > =

> > > Thanks a lot!
> > > =

> > > -----Original Message-----
> > > From: Randy Clamons [mailto:randy@novaspace.com] =

> > > Sent: Thursday, March 03, 2005 2:42 PM
> > > To: Ryan Schefke; win32@lists.mysql.com
> > > Subject: Re: Help Constructing Query
> > > =

> > > Ryan,
> > > =

> > > I'm going to substitute a couple of generic columns from invotes, =

> as =

> > I =

> > > don't
> > > know your table structure.
> > > Let's give it a shot:
> > > =

> > > SELECT listings.listingid,
> > > listings.state, =

> > > listings.column1, =

> > > listings.column2, =

> > > listings.column3,
> > > COUNT(invotes.listingID) as count =

> > > FROM listings
> > > LEFT OUTER JOIN invotes ON listings.listingID =3D invotes.listingID
> > > WHERE listings.state =3D 'MI'
> > > GROUP BY listings.listingid,
> > > listings.state, =

> > > listings.column1, =

> > > listings.column2, =

> > > listings.column3
> > > ORDER BY 5 DESC
> > > =

> > > Here's the key. List all columns from listings that you wish to =

> > report. =

> > > I
> > > have included listings.state, although it's noit really necessary =

> for =

> > =

> > > your
> > > particular request. =

> > > =

> > > The 'LEFT OUTER JOIN' will include all rows from listings, even if =

> > > there is
> > > no entry in invotes. =

> > > =

> > > The 'GROUP BY' clause needs to include all columns from the query =

> > that =

> > > are
> > > not aggregate functions. In this case, COUNT(invotes.listingID) is =

> > the =

> > > only
> > > aggregate function. Do not use COUNT(*), as it will count null =

> > > values--the
> > > lowest count returned will be 1 not 0.
> > > =

> > > The 'ORDER BY' clause specifies the sort order by the column number =

> =

> > > from the
> > > query. 'DESC' says sort in descending order.
> > > =

> > > Randy Clamons
> > > Systems Programming
> > > Novaspace.com
> > > =

> > > =

> > > > ------------Original Message------------
> > > > From: "Ryan Schefke"
> > > > To: win32@lists.mysql.com
> > > > Date: Thu, Mar-3-2005 12:53 PM
> > > > Subject: Help Constructing Query
> > > >
> > > > Hello,
> > > > =

> > > > =

> > > > =

> > > > I am trying to read all the values in one column, the listingID, =

> of =

> > a =

> > > =

> > > > table
> > > > (listings) that have a "MI" in the "state" column and compare =

> those
> > > > listingID values to another table (invotes) to see how many times =

> =

> > > each
> > > > listingID occurs. =

> > > > =

> > > > =

> > > > =

> > > > Then, I need to print out the results in decrementing order and =

> > ALSO =

> > > > list
> > > > the ones that never occurred (count of 0) in table "invotes" at =

> the =

> > =

> > > > end.
> > > > When I'm printing them out I don't need to print the listingID, I =

> =

> > > need =

> > > > to
> > > > print other column values that are in the same row as the =

> listingID =

> > =

> > > of =

> > > > table
> > > > "listings".
> > > > =

> > > > =

> > > > =

> > > > I hope this makes sense.
> > > > =

> > > > =

> > > > =

> > > > Can anyone please help?
> > > > =

> > > > =

> > > > =

> > > > Best regards,
> > > > =

> > > > Ryan
> =



--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=3Dgcdmw-win32@m.gmane.org

RE: Help Constructing Query

Ahh...my mistake was counting the columns after the SELECT statement
starting at 0, not 1. So, I was off by one.

Thanks,
Ryan

-----Original Message-----
From: Randy Clamons [mailto:randy@novaspace.com]
Sent: Thursday, March 03, 2005 4:04 PM
To: Ryan Schefke; win32@lists.mysql.com
Subject: RE: Help Constructing Query

The column number must match the number of the column in the
select_expression (column list). In your query, 'count' is column number 7.
If you specify "ORDER BY 6", the list will be sorted by the siteDescription.
If you specify "ORDER BY 7", your list will be sorted by the count.

I'm pretty sure this list does not forward attachments.

Randy Clamons
Systems Programming
Novaspace.com


> ------------Original Message------------
> From: "Ryan Schefke"
> To: randy@novaspace.com, win32@lists.mysql.com
> Date: Thu, Mar-3-2005 2:58 PM
> Subject: RE: Help Constructing Query
>
> Randy - did you get my email with the .php file attached? If not, I'll
> resend.
>
> /* THE QUERY */
> $query_state = "SELECT listings.listingID, listings.state,
> listings.preferred, listings.siteAddress, listings.siteTitle,
> listings.siteDescription, COUNT(invotes.listingID) AS count FROM
> listings
> LEFT OUTER JOIN invotes ON listings.listingID = invotes.listingID WHERE
> listings.state ='$st' GROUP BY listings.listingID, listings.state,
> listings.preferred, listings.siteAddress, listings.siteTitle,
> listings.siteDescription ORDER BY count DESC";
> /* THE QUERY */
>
> -----Original Message-----
> From: Randy Clamons [mailto:randy@novaspace.com]
> Sent: Thursday, March 03, 2005 3:55 PM
> To: Ryan Schefke; win32@lists.mysql.com
> Subject: RE: Help Constructing Query
>
> I'm not sure. Why don't you send your query? I should be able to
> explain
> after seeing it.
>
> Randy Clamons
> Systems Programming
> Novaspace.com
>
>
> > ------------Original Message------------
> > From: "Ryan Schefke"
> > To: randy@novaspace.com, win32@lists.mysql.com
> > Date: Thu, Mar-3-2005 2:46 PM
> > Subject: RE: Help Constructing Query
> >
> > Randy - Your last statement confused me a bit. If your statement is
> > true:
> >
> > " You can use either the column number or the column name (or even a
> > formula) in either the 'GROUP BY' or the 'ORDER BY'"
> >
> > Then how come it didn't work when I used
> >
> > ORDER BY 6 DESC
> >
> > ...instead of
> >
> > ORDER BY count DESC
> >
> > Ryan
> >
> > -----Original Message-----
> > From: Randy Clamons [mailto:randy@novaspace.com]
> > Sent: Thursday, March 03, 2005 3:41 PM
> > To: Ryan Schefke; win32@lists.mysql.com
> > Subject: RE: Help Constructing Query
> >
> > Oops! I added a column to the query after writing the 'ORDER BY'
> > clause. You
> > can use either the column number or the column name (or even a
> formula)
> > in
> > either the 'GROUP BY' or the 'ORDER BY'. The column number is most
> > useful
> > when dealing with a 'UNION'.
> >
> > Glad it worked.
> >
> > Randy Clamons
> > Systems Programming
> > Novaspace.com
> >
> >
> > > ------------Original Message------------
> > > From: "Ryan Schefke"
> > > To: win32@lists.mysql.com
> > > Date: Thu, Mar-3-2005 2:07 PM
> > > Subject: RE: Help Constructing Query
> > >
> > > Randy - I can't believe it worked! YES!
> > >
> > > I had to change one value though, in the ORDER BY 5 DESC, I had to
> > > change to
> > > ORDER BY count DESC.
> > >
> > > Thanks a lot!
> > >
> > > -----Original Message-----
> > > From: Randy Clamons [mailto:randy@novaspace.com]
> > > Sent: Thursday, March 03, 2005 2:42 PM
> > > To: Ryan Schefke; win32@lists.mysql.com
> > > Subject: Re: Help Constructing Query
> > >
> > > Ryan,
> > >
> > > I'm going to substitute a couple of generic columns from invotes,
> as
> > I
> > > don't
> > > know your table structure.
> > > Let's give it a shot:
> > >
> > > SELECT listings.listingid,
> > > listings.state,
> > > listings.column1,
> > > listings.column2,
> > > listings.column3,
> > > COUNT(invotes.listingID) as count
> > > FROM listings
> > > LEFT OUTER JOIN invotes ON listings.listingID = invotes.listingID
> > > WHERE listings.state = 'MI'
> > > GROUP BY listings.listingid,
> > > listings.state,
> > > listings.column1,
> > > listings.column2,
> > > listings.column3
> > > ORDER BY 5 DESC
> > >
> > > Here's the key. List all columns from listings that you wish to
> > report.
> > > I
> > > have included listings.state, although it's noit really necessary
> for
> >
> > > your
> > > particular request.
> > >
> > > The 'LEFT OUTER JOIN' will include all rows from listings, even if
> > > there is
> > > no entry in invotes.
> > >
> > > The 'GROUP BY' clause needs to include all columns from the query
> > that
> > > are
> > > not aggregate functions. In this case, COUNT(invotes.listingID) is
> > the
> > > only
> > > aggregate function. Do not use COUNT(*), as it will count null
> > > values--the
> > > lowest count returned will be 1 not 0.
> > >
> > > The 'ORDER BY' clause specifies the sort order by the column number
>
> > > from the
> > > query. 'DESC' says sort in descending order.
> > >
> > > Randy Clamons
> > > Systems Programming
> > > Novaspace.com
> > >
> > >
> > > > ------------Original Message------------
> > > > From: "Ryan Schefke"
> > > > To: win32@lists.mysql.com
> > > > Date: Thu, Mar-3-2005 12:53 PM
> > > > Subject: Help Constructing Query
> > > >
> > > > Hello,
> > > >
> > > >
> > > >
> > > > I am trying to read all the values in one column, the listingID,
> of
> > a
> > >
> > > > table
> > > > (listings) that have a "MI" in the "state" column and compare
> those
> > > > listingID values to another table (invotes) to see how many times
>
> > > each
> > > > listingID occurs.
> > > >
> > > >
> > > >
> > > > Then, I need to print out the results in decrementing order and
> > ALSO
> > > > list
> > > > the ones that never occurred (count of 0) in table "invotes" at
> the
> >
> > > > end.
> > > > When I'm printing them out I don't need to print the listingID, I
>
> > > need
> > > > to
> > > > print other column values that are in the same row as the
> listingID
> >
> > > of
> > > > table
> > > > "listings".
> > > >
> > > >
> > > >
> > > > I hope this makes sense.
> > > >
> > > >
> > > >
> > > > Can anyone please help?
> > > >
> > > >
> > > >
> > > > Best regards,
> > > >
> > > > Ryan
>


--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=brian@tek-tools.com






--
MySQL Windows Mailing List
For list archives: http://lists.mysql.com/win32
To unsubscribe: http://lists.mysql.com/win32?unsub=gcdmw-win32@m.gmane.org