MySQL querry

**Andy Hassall** · Jul 17 '05, 12:26 AM

Re: MySQL querry

On Wed, 08 Oct 2003 19:42:22 +0000, Eric Kincl <Eric@Kincl.net > wrote:
[color=blue]
>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_conce rts", and a final
>table, "bands". Concerts has a key CID (concert ID), bands has "bid" (band
>ID). "bands_at_conce rts" 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?[/color]

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

Unless I've missed the point.

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

**Eric Kincl** · Jul 17 '05, 12:26 AM

Re: MySQL querry

Andy Hassall wrote:
[color=blue]
> On Wed, 08 Oct 2003 19:42:22 +0000, Eric Kincl <Eric@Kincl.net > wrote:
>[color=green]
>>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_conce rts", and a
>>final
>>table, "bands". Concerts has a key CID (concert ID), bands has "bid"
>>(band
>>ID). "bands_at_conce rts" 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?[/color]
>
> select bands.*
> from concerts
> inner join bands_at_concer ts using (cid)
> inner join bands using (bid)
> where cid = X
>
> Unless I've missed the point.
>
> --
> Andy Hassall (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)[/color]

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** · Jul 17 '05, 12:26 AM

Re: MySQL querry

On Wed, 08 Oct 2003 20:13:57 +0000, Eric Kincl <Eric@Kincl.net _NO_SPAM_> wrote:
[color=blue]
>Andy Hassall wrote:
>[color=green]
>> On Wed, 08 Oct 2003 19:42:22 +0000, Eric Kincl <Eric@Kincl.net > wrote:
>>[color=darkred]
>>>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_conce rts", and a
>>>final
>>>table, "bands". Concerts has a key CID (concert ID), bands has "bid"
>>>(band
>>>ID). "bands_at_conce rts" 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
>>>informatio n 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?[/color]
>>
>> select bands.*
>> from concerts
>> inner join bands_at_concer ts using (cid)
>> inner join bands using (bid)
>> where cid = X
>>
>> Unless I've missed the point.[/color]
>
>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.[/color]

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 (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

**Eric Kincl** · Jul 17 '05, 12:27 AM

Re: MySQL querry

Andy Hassall wrote:
[color=blue]
> On Wed, 08 Oct 2003 20:13:57 +0000, Eric Kincl <Eric@Kincl.net _NO_SPAM_>
> wrote:
>[color=green]
>>Andy Hassall wrote:
>>[color=darkred]
>>> On Wed, 08 Oct 2003 19:42:22 +0000, Eric Kincl <Eric@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_c oncerts", and a final
>>>>table, "bands". Concerts has a key CID (concert ID), bands has "bid"
>>>>(band
>>>>ID). "bands_at_conce rts" 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
>>>>informati on 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_concer ts using (cid)
>>> inner join bands using (bid)
>>> where cid = X
>>>
>>> Unless I've missed the point.[/color]
>>
>>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.[/color]
>
> 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 (andy@andyh.co. uk) icq(5747695) (http://www.andyh.co.uk)
> Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)[/color]

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.

MySQL querry

MySQL querry

Comment

Comment

Comment

Comment