MySQL Union?? Query Help

I have two tables. One has album information, and the other is actual copies of those albums in the inventory. These have different prices based on various factors. Each row in the inventory table doesn't store the album information directly, just the id of the album in the "albums" table.

The query I want to write will get me the Artist name, Album Name, image, etc from the albums table as well as the highest and lowest prices found in the inventory table.

I know basic MySQL but I'm having a lot of trouble with this one. Can some expert please help me here? Thanks开发者_Go百科 a lot!

Albums table:

• id (pk)
• title
• artist
• year
• genre

Inventory table:

• id(pk)
• albumId (corresponds to an id in albums table)
• price
• condition
• mono

The query I've attempted looks like this but is giving me an error:

SELECT albums.artist, albums.title, albums.imgurl, albums.id
  FROM albums

UNION 

SELECT max(inventory.price), min(inventory.price)
 WHERE albums.id = inventory.albumId 
 LIMIT 30

SELECT * FROM `albums`

The two parts of a (two-part) UNION must have the same number of columns, and the types need to be the same or treatable as if they were the same.

Your second part doesn't even have a FROM clause.

And the final line is a separate query that should be separated from the previous one by a semi-colon.

What you want is, more or less:

 SELECT a.artist, a.title, a.imgurl, a.id, MAX(i.price), MIN(i.price)
   FROM albums    AS a
   JOIN inventory AS i ON a.id = i.albumId
--WHERE ...conditions on artist or album...
  GROUP BY a.artist, a.title, a.imgurl, a.id

Note how using table aliases allows the the select-list to fit on one line. It won't always, of course, but using aliases can provide succinctness, and encourages tagging all columns with the origin table, which helps make it explicit where the data comes from (thereby improving readability and clarity and other fine qualities).

SELECT albums.artist, albums.title, albums.imgurl, albums.id,
       max(inventory.price), min(inventory.price)
 FROM albums JOIN inventory ON albums.id = inventory.albumId 
GROUP BY albums.id

You can't use UNION like that. The result sets from both queries would have to be the same (i.e. same number of columns and same types) for a UNION to work:

Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

You'll have to do separate queries.