MySQL querry

[Eric Kincl]

Hey everyone,
I know this isn't a SQL group, but I'm on my colleges news server and they
don't offer one. Hopefully someone here will be able to help me.

I have a database that is normalized with a many-to-one ratio. Basically I
have one table, "concerts", another table "bands_at_concerts", and a final
table, "bands". Concerts has a key CID (concert ID), bands has "bid" (band
ID). "bands_at_concerts" is just two columns, CID and BID, both of which
have several of the same #'s. (For example, many bands play at one
concert, and one band plays at many concerts) I was wondering if there is
a way to retrieve all this information JOINED on to a querry I do to find
information about the concert. ie: SELECT * FROM concert WHERE cid = X <-
Is there a way to change that so that it also somehow returns all the bands
that would be playing there?

Thanks in advance,
-Eric Kincl

[Andy Hassall]

On Wed, 08 Oct 2003 19:42:22 +0000, Eric Kincl <Er**@Kincl.net> wrote:

Hey everyone,
I know this isn't a SQL group, but I'm on my colleges news server and they
don't offer one. Hopefully someone here will be able to help me.

I have a database that is normalized with a many-to-one ratio. Basically I
have one table, "concerts", another table "bands_at_concerts", and a final
table, "bands". Concerts has a key CID (concert ID), bands has "bid" (band
ID). "bands_at_concerts" is just two columns, CID and BID, both of which
have several of the same #'s. (For example, many bands play at one
concert, and one band plays at many concerts) I was wondering if there is
a way to retrieve all this information JOINED on to a querry I do to find
information about the concert. ie: SELECT * FROM concert WHERE cid = X <-
Is there a way to change that so that it also somehow returns all the bands
that would be playing there?

select bands.*
from concerts
inner join bands_at_concerts using (cid)
inner join bands using (bid)
where cid = X

Unless I've missed the point.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Eric Kincl]

Andy Hassall wrote:

On Wed, 08 Oct 2003 19:42:22 +0000, Eric Kincl <Er**@Kincl.net> wrote:

Hey everyone,
I know this isn't a SQL group, but I'm on my colleges news server and they
don't offer one. Hopefully someone here will be able to help me.

I have a database that is normalized with a many-to-one ratio. Basically
I have one table, "concerts", another table "bands_at_concerts", and a
final
table, "bands". Concerts has a key CID (concert ID), bands has "bid"
(band
ID). "bands_at_concerts" is just two columns, CID and BID, both of which
have several of the same #'s. (For example, many bands play at one
concert, and one band plays at many concerts) I was wondering if there is
a way to retrieve all this information JOINED on to a querry I do to find
information about the concert. ie: SELECT * FROM concert WHERE cid = X <-
Is there a way to change that so that it also somehow returns all the
bands that would be playing there?

select bands.*
from concerts
inner join bands_at_concerts using (cid)
inner join bands using (bid)
where cid = X

Unless I've missed the point.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Hey,
Thanks, it looks like it'll work. One question though. What sort of result
will it return? Will it be multiple lines or what? If it works like I
would like it to, it would be something like one line about the concert and
the rest bands that will be there, or all lines about the concert and each
line would have a different band.

Thanks again,
-Eric Kincl

[Andy Hassall]

On Wed, 08 Oct 2003 20:13:57 +0000, Eric Kincl <Er**@Kincl.net_NO_SPAM_> wrote:

Andy Hassall wrote:

On Wed, 08 Oct 2003 19:42:22 +0000, Eric Kincl <Er**@Kincl.net> wrote:

Hey everyone,
I know this isn't a SQL group, but I'm on my colleges news server and they
don't offer one. Hopefully someone here will be able to help me.

I have a database that is normalized with a many-to-one ratio. Basically
I have one table, "concerts", another table "bands_at_concerts", and a
final
table, "bands". Concerts has a key CID (concert ID), bands has "bid"
(band
ID). "bands_at_concerts" is just two columns, CID and BID, both of which
have several of the same #'s. (For example, many bands play at one
concert, and one band plays at many concerts) I was wondering if there is
a way to retrieve all this information JOINED on to a querry I do to find
information about the concert. ie: SELECT * FROM concert WHERE cid = X <-
Is there a way to change that so that it also somehow returns all the
bands that would be playing there?

select bands.*
from concerts
inner join bands_at_concerts using (cid)
inner join bands using (bid)
where cid = X

Unless I've missed the point.

Thanks, it looks like it'll work. One question though. What sort of result
will it return? Will it be multiple lines or what? If it works like I
would like it to, it would be something like one line about the concert and
the rest bands that will be there, or all lines about the concert and each
line would have a different band.

It'll be each bands row that is part of the relation with the concert.

If you want the concert information too, add it to the select list, it'll be
repeated per band.

It doesn't make any sense to be getting one row for the concert and and
several for the bands in the same result set; you can't have rows of different
'types' in the same result set.

If you only want to fetch the concert fields once, do two queries; the one
above to get band information, and 'select * from concerts where CID = X' to
get concert information.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

[Eric Kincl]

Andy Hassall wrote:

On Wed, 08 Oct 2003 20:13:57 +0000, Eric Kincl <Er**@Kincl.net_NO_SPAM_>
wrote:

Andy Hassall wrote:

On Wed, 08 Oct 2003 19:42:22 +0000, Eric Kincl <Er**@Kincl.net> wrote:

Hey everyone,
I know this isn't a SQL group, but I'm on my colleges news server and
they
don't offer one. Hopefully someone here will be able to help me.

I have a database that is normalized with a many-to-one ratio.
Basically I have one table, "concerts", another table
"bands_at_concerts", and a final
table, "bands". Concerts has a key CID (concert ID), bands has "bid"
(band
ID). "bands_at_concerts" is just two columns, CID and BID, both of
which
have several of the same #'s. (For example, many bands play at one
concert, and one band plays at many concerts) I was wondering if there
is a way to retrieve all this information JOINED on to a querry I do to
find
information about the concert. ie: SELECT * FROM concert WHERE cid = X
<- Is there a way to change that so that it also somehow returns all the
bands that would be playing there?

select bands.*
from concerts
inner join bands_at_concerts using (cid)
inner join bands using (bid)
where cid = X

Unless I've missed the point.

Thanks, it looks like it'll work. One question though. What sort of
result
will it return? Will it be multiple lines or what? If it works like I
would like it to, it would be something like one line about the concert
and the rest bands that will be there, or all lines about the concert and
each line would have a different band.

It'll be each bands row that is part of the relation with the concert.

If you want the concert information too, add it to the select list, it'll
be
repeated per band.

It doesn't make any sense to be getting one row for the concert and and
several for the bands in the same result set; you can't have rows of
different 'types' in the same result set.

If you only want to fetch the concert fields once, do two queries; the
one
above to get band information, and 'select * from concerts where CID = X'
to get concert information.

--
Andy Hassall (an**@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Alright dude! Thanks! It works great. For future reference to those of
you out there, it returns 1 row per multiple instance (in this case 1 row
per band.) This means its returning the other info (this case, the concert
info) multiple times, but you can obviously just ignore that.