Mysql – Help with this query dealing with aggregates

MySQL

I have a database that stores information about artists, bands and albums and I'm trying to select the band names and album titles for bands with more than 1 album.

band_id, name,            year_formed
1,       blink182,        1990
2,       fall out boy,    1997
3,       u2,              1982
4,       coldplay,        1990
5,       zach brown band, 2002

and album

album_id, title,                          band_id
1,        take off your pants and jacket, 1
2,        stars,                          3
3,        fbgm,                           3
4,        reach,                          3
5,        yup,                            4

This doesn't seem too hard of a problem but I cannot figure it out for the life of me.
The problem seems to be with getting both the album title and band name from the select statement.

SELECT b.name, alb.title
FROM band as b, album as alb
WHERE exists (SELECT * FROM album as al WHERE b.bid = alb.bid)

Returns the all band names and their album titles. My guess is to have the subquery handle finding the bands with more than one album but I just am at a complete loss for how to do this. Subqueries have been throwing me for a loop in general, so any nice description of how they work conceptually would be appreciated.

Best Answer

To determine which bands that have more than 1 album you can do something like:

SELECT b.bid 
FROM band as b
JOIN album as a
   ON b.bid = a.bid
GROUP BY b.bid
HAVING COUNT(*) > 1

Since band_name and year_formed is likely to be f.d. of bid:

SELECT b.bid, max(b.name), max(b.year_formed) 
FROM band as b
JOIN album as a
   ON b.bid = a.bid
GROUP BY b.bid
HAVING COUNT(*) > 1

Related Solutions

Mysql – Help with modeling this table

One question I have is how does the Company relate to a phone number? I mean, is there just a list of internal numbers, and will all of these phone numbers be associated to an employee. If they are you can simply just make your phone number table and have it relate to an employee. For example.

create table company
(
company_id int PRIMARY KEY IDENTITY, --(Primary Key)
company_name varchar(100)
)

create table employee
(
employee_id int PRIMARY KEY IDENTITY,  --(Primary Key)
employee_name varchar(100),
company_id int not null  --(Foreign Key)
)

create table phone
(
phone_id int PRIMARY KEY IDENTITY,
phone_number varchar(15), -- 555-555-5555
phone_type varchar(10),   -- Home | Cell | Work
employee_id int not null  --(Foreign Key)
)

employee_id being your foreign key to your employees table. If each employee only works for one company, then to get all of the phone numbers available for that company, you could do something like this

select * from phone as p join employee as e on e.employee_id = p.employee_id where e.company_id = 1

I hope this helps :)

Mysql – Help creating this query

Are you looking for this?

SELECT m.id,m.seen, m.uid, m.hash, m.date_created,m.subject,
       m.textplain,m.texthtml, CONCAT_WS('@', a.localpart, a.domain) address,
       (
         SELECT COUNT(*) 
           FROM messages parent JOIN messages child
             ON child.parent_id = parent.id
          WHERE parent.id = m.id
       ) child_count
  FROM messages m LEFT JOIN message_address_fields maf 
    ON maf.message_id = m.id LEFT JOIN addresses a 
    ON a.id = maf.address_id
 WHERE maf.field_id = 4 
   AND m.user_id = 1 
 LIMIT 30

Sample output:

| ID | SEEN | UID | ... | CHILD_COUNT |
------------------- ... ---------------
|  1 |    0 |   2 | ... |           2 |
|  2 |    0 |   3 | ... |           0 |
|  3 |    0 |   4 | ... |           0 |
|  4 |    0 |   5 | ... |           0 |

Here is SQLFiddle demo

Related Question