Construction of not-so-easy-as-it-seems query

[rawe]

Hi folks

Hope you can help me with this one - I've been trying to figure out a way to do the following query for about a week now. And not getting anywhere!

I'm building a web-based administration-system for a rock festival and I need to retrieve some information about the next concerts on each stage in the festival.

I have 3 tables: tblConcert, tblBand and tblVenue.

What I would like to retrieve from the database is the following data:
tblConcert.ConcertID
tblConcert.ConcertStartTime
tblBand.BandName
tblVenue.VenueName

But: I need to just retrieve the NEXT concert in all venues. So what I need is a way to retrieve for each venue the concert with the earliest start-time - and that start-time should be later than or equal to Now().

I've been trying with GROUP BY and a lot of other things, but nothing seems to work for me.

Could someone please help?

Best

Rasmus Wehner
Mimia

 

[PruSQLer]

Based on the title of your post, it's pretty certain the following won't work but one might think this is what you'd need to do. I just took a guess at the joins.

select c.ConcertID
, b.BandName
, v.VenueName
, Min(ConcertStartTime)
from tblconcert c
, tblvenue v
, tblBandName b
Where c.BandID = b.BandID
and c.venueID = v.venueID
and ConcertStartTime >= date(currenttimestamp)
Group by c.ConcertID
, b.BandName
, v.VenueName

 

[RiverGuy]

SELECT A.ConcertID,
A.ConcertStartTime,
tblBand.BandName,
tblVenue.VenueName
FROM tblConcert A
INNER JOIN tblVenue ON A.VenueID = tblVenue.VenueID
INNER JOIN tblBand ON A.BandID = tblBand.BandID
WHERE A.ConcertStartTime = (SELECT MIN(ConcertStartTime) FROM tblConcert B WHERE tblVenue.VenueID = B.VenueID AND ConcertStartTime >= GETDATE())

Substitute the GETDATE() function for the current date/time in whichever DBMS you are using.

 

[r937]

this question was posted in the mysql forum on another forum site

here's the answer i gave over there, it uses a self-join instead of a subquery

there has been no answer in 4 days

no answer here, apparently, either

rasmus, what's up with that? people try to help you, the least you can do is say thanks

select C1.ConcertID
     , C1.ConcertStartTime
     , B.BandName
     , V.VenueName
  from tblVenue V
inner
  join tblConcert C1
    on V.ID
     = C1.VenueID
   and C1.ConcertStartTime >= Now()
inner
  join tblConcert C2
    on V.ID
     = C2.VenueID     
   and C2.ConcertStartTime >= Now()
inner
  join tblBand 
    on C1.BandID
     = B.ID
group
    by C1.ConcertID
     , C1.ConcertStartTime
     , B.BandName
     , V.VenueName
having C1.ConcertStartTime
     = max(C2.ConcertStartTime)

rudy

http://r937.com/

SQL Consulting