Query Help

[Jacco Schalkwijk]

SELECT s.SeminarID, s.Title
FROM SEMINARS s
INNER JOIN SEMINAR_DETAILS sd
ON s.SeminarID = sd.SeminarID
GROUP BY s.SeminarID, s.Title
ORDER BY MIN(sd.Startdate)

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"MF" <mfrederick1@hotmail.com> wrote in message
news:lFhNa.55609$a51.52087@news02.bloor.is.net.cab le.rogers.com...

> I have two tables. I will leave out the columns unrelated to this

question.

>
> SEMINARS
> SeminarID int
> Title varchar(50)
>
> SEMINAR_DETAILS
> SeminarDetailsID int
> SeminarID int
> StartDate datetime
>
> If I do an INNER JOIN on the two tables I would get something like this
>
> SeminarID Title SeminarDetailsID StartDate
> 1 Seminar 1 1 July 6 2003
> 1 Seminar 1 2 July 4 2003
> 2 Seminar 2 3 July 8 2003
> 2 Seminar 2 4 July 7 2003
>
>
> If I do an INNER JOIN on the two tables ORDER BY nearest StartDate I would
> get something like this
>
> SeminarID Title SeminarDetailsID StartDate
> 1 Seminar 1 2 July 4 2003
> 1 Seminar 1 1 July 6 2003
> 2 Seminar 2 4 July 7 2003
> 2 Seminar 2 3 July 8 2003
>
> But what I want is this, and I can't figure out how to get it.
>
> SeminarID Title
> 1 Seminar 1
> 2 Seminar 2
>
> I just want a list of seminars (each seminar only once) ORDER BY the

nearest

> startdate. I have tried GROUP BY but it doesn't work because to ORDER BY
> StartDate I have to include StartDate in the GROUP BY clause and then I

end

> up with each individual seminar more than once. Any ideas?
>
>
>
>
>

[MF]

Thank you very much Jacco. It does exactly what I wanted.

"Jacco Schalkwijk" <NOSPAMjaccos@eurostop.co.uk> wrote in message
news:O3uTOlkQDHA.3880@tk2msftngp13.phx.gbl...

> SELECT s.SeminarID, s.Title
> FROM SEMINARS s
> INNER JOIN SEMINAR_DETAILS sd
> ON s.SeminarID = sd.SeminarID
> GROUP BY s.SeminarID, s.Title
> ORDER BY MIN(sd.Startdate)
>
> --
> Jacco Schalkwijk MCDBA, MCSD, MCSE
> Database Administrator
> Eurostop Ltd.
>
>
> "MF" <mfrederick1@hotmail.com> wrote in message
> news:lFhNa.55609$a51.52087@news02.bloor.is.net.cab le.rogers.com...

> > I have two tables. I will leave out the columns unrelated to this

> question.

> >
> > SEMINARS
> > SeminarID int
> > Title varchar(50)
> >
> > SEMINAR_DETAILS
> > SeminarDetailsID int
> > SeminarID int
> > StartDate datetime
> >
> > If I do an INNER JOIN on the two tables I would get something like this
> >
> > SeminarID Title SeminarDetailsID StartDate
> > 1 Seminar 1 1 July 6 2003
> > 1 Seminar 1 2 July 4 2003
> > 2 Seminar 2 3 July 8 2003
> > 2 Seminar 2 4 July 7 2003
> >
> >
> > If I do an INNER JOIN on the two tables ORDER BY nearest StartDate I

would

> > get something like this
> >
> > SeminarID Title SeminarDetailsID StartDate
> > 1 Seminar 1 2 July 4 2003
> > 1 Seminar 1 1 July 6 2003
> > 2 Seminar 2 4 July 7 2003
> > 2 Seminar 2 3 July 8 2003
> >
> > But what I want is this, and I can't figure out how to get it.
> >
> > SeminarID Title
> > 1 Seminar 1
> > 2 Seminar 2
> >
> > I just want a list of seminars (each seminar only once) ORDER BY the

> nearest

> > startdate. I have tried GROUP BY but it doesn't work because to ORDER

BY

> > StartDate I have to include StartDate in the GROUP BY clause and then I

> end

> > up with each individual seminar more than once. Any ideas?
> >
> >
> >
> >
> >

>
>

[Laurent Lemire]

Is this what you tried?

Select SeminarID,Title
from SeminarTable
Order By StartDate
Group By SeminarID,title.

I don't see why startdate would need to be in the group by.
Let me know if that is the case and I'll investigate and
find out.

>-----Original Message-----
>I have two tables. I will leave out the columns

unrelated to this question.

>
>SEMINARS
>SeminarID int
>Title varchar(50)
>
>SEMINAR_DETAILS
>SeminarDetailsID int
>SeminarID int
>StartDate datetime
>
>If I do an INNER JOIN on the two tables I would get

something like this

>
>SeminarID Title SeminarDetailsID

StartDate

>1 Seminar 1

1 July 6 2003

>1 Seminar 1

2 July 4 2003

>2 Seminar 2

3 July 8 2003

>2 Seminar 2

4 July 7 2003

>
>
>If I do an INNER JOIN on the two tables ORDER BY nearest

StartDate I would

>get something like this
>
>SeminarID Title SeminarDetailsID

StartDate

>1 Seminar 1

2 July 4 2003

>1 Seminar 1

1 July 6 2003

>2 Seminar 2

4 July 7 2003

>2 Seminar 2

3 July 8 2003

>
>But what I want is this, and I can't figure out how to

get it.

>
>SeminarID Title
>1 Seminar 1
>2 Seminar 2
>
>I just want a list of seminars (each seminar only once)

ORDER BY the nearest

>startdate. I have tried GROUP BY but it doesn't work

because to ORDER BY

>StartDate I have to include StartDate in the GROUP BY

clause and then I end

>up with each individual seminar more than once. Any

ideas?

>
>
>
>
>
>.
>

[Magical_Trevor]

I don't know if anyone can help me, but i'm trying to make a query in
coldfusion, qeurying a database, and i want to show a result that has a value
greater or less than a certain amount. Here is my code:


<cfparam name="URL.rstName" default="1">
<cfparam name="URL.cuisine" default="">
<cfparam name="URL.lowPrice" type="numeric" default="">
<cfparam name="URL.highPrice" default="">
<cfparam name="URL.smoke" default="">
<cfquery name="rs" datasource="db">
SELECT *
FROM Restaurants
WHERE RstName LIKE '%#URL.RstName#%' AND Cuisine = '#URL.cuisine#' AND
RstSmoking = '#URL.smoke#' AND RstPrice > '#lowPrice#' AND RstPrice <
'#highPrice#'
</cfquery>


It keeps giving me this error:

Data type mismatch in criteria expression.

I don't see what the problem is?:(

[Stressed_Simon]

RstPrice is being referenced as a string as you are putting apostraphes around
the variable. You cannot use the > and < operators on a text field. So you have
one of two problems either RstPrice is a textfield in which case you must make
it and numerical field or it already is and you need to take put the
apostraphes.

HTH

[Magical_Trevor]

Yeah, it was a number field. That was a pretty stupid mistake huh? Nearly as
stupid as leaving out a "=" and it not returning all of the results i wanted.
*sigh* I love programming, I really do.

Thanks for your help.

[s6868]

I am trying to create a new order report, but here is the problem. I have an
order table, a order line table. Order line contains specific items while order
table holds order total amount. but the report I am going create got to splite
the order amount at every item row, so showing the total amount of that item,
shipping, tax and unit price (shipping and tax is only handling at the order
level),
Here is the current report result:
orderId, item order total, tax, shipping, order line id, quantity, unit price,
item name, user_id
1, $191 $9, $3.5, --------
---- -------- -------- 4510

-------- ---- -------- -------- 23,
10, $10, Notebook, ------
-------- -------- -------- -------- 24,
10, $8, Stapler, ------

the result of the new report should like this:

orderId, order line id, item order total, tax, shipping, quantity, unit price,
item name, user_id, charge code
1, 23, $107 $5, $2,
10, $10, Notebook, 4510, ws1
-- 24, $84 $4, $1.5,
10, $8, Stapler, 4510, ws2

The reason for doing this is charge code will control the tax and shipping, it
is not flat same.


Is there a way that will alow me to do this with a query? or i have to do all
the calculation with coding.

Thanks.

[dm2000t]

<cfquery name="getInfo" datasource="IHLAmembers">
Select hotels.hotelID,
hotels.hotelname,
hotels.address,
hotels.city,
hotels.state,
hotels.zip,
hotels.memberID,
contacts.hotelID,
contacts.firstname,
contacts.lastname,
contacts.Position,
contacts.email,
contacts.keycontact
from Hotels, contacts
Where 0=0
AND hotels.HotelID = Contacts.hotelID
<cfif #form.keycontact# neq "">AND contacts.KeyContact = 1</cfif>
<cfif #form.hotelname# neq ""> AND hotelname LIKE '%#form.hotelname#%'</cfif>
<cfif #form.membertypeID# neq "">AND membertypeID = #form.membertypeID#</cfif>
<cfif #form.memberID# neq "">AND MemberID = '#form.memberID#'</cfif>
<cfif #form.city# neq ""> AND city LIKE '%#form.city#%'</cfif>
ORDER BY City,HotelName
</cfquery>

As you can see, I am using a form to create my Where statement along with a
simple join. This works great IF I have "keycontacts" checked on the form. If
not, I get "page cannot be displayed". If I remove the <cfif> relating to
keycontact, it works just fine. Does anyone see a error here?

Thanks in advance.

[Velvett Fog]

Hi :)

Do a cfdump of the form structure. My guess is that keycontact is a checkbox?
if so, then it is only passed if it is checked. I'd probablly do a cfparam
above the query, to make sure that form.keycontact does exist. Then a mild
adjustment to your query...

<cfparam name="form.keycontact" default="0">
<cfif #form.keycontact# neq 0>AND contacts.KeyContact = 1</cfif>

Also .. if it was me.. I'd change this too...

from Hotels, contacts
Where 0=0
AND hotels.HotelID = Contacts.hotelID

to this ...

from Hotels, contacts
Where hotels.HotelID = Contacts.hotelID

[dm2000t]

Velvett Fog....

I have so much to learn.

Thank you so much.