sql - MySQL Union?? Query Help -

-

i have 2 tables. 1 has album information, , other actual copies of albums in inventory. these have different prices based on various factors. each row in inventory table doesn't store album information directly, id of album in "albums" table.

the query want write me artist name, album name, image, etc albums table highest , lowest prices found in inventory table.

i know basic mysql i'm having lot of trouble one. can expert please me here? lot!

albums table:

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

inventory table:

  • id(pk)
  • albumid (corresponds id in albums table)
  • price
  • condition
  • mono

the query i've attempted looks giving me error:

select albums.artist, albums.title, albums.imgurl, albums.id   albums  union   select max(inventory.price), min(inventory.price)  albums.id = inventory.albumid   limit 30  select * `albums`

the 2 parts of (two-part) union must have same number of columns, , types need same or treatable if same.

your second part doesn't have clause.

and final line separate query should separated previous 1 semi-colon.

what want is, more or less:

 select a.artist, a.title, a.imgurl, a.id, max(i.price), min(i.price)    albums       join inventory on a.id = i.albumid --where ...conditions on artist or album...   group a.artist, a.title, a.imgurl, a.id

note how using table aliases allows the select-list fit on 1 line. won't always, of course, using aliases can provide succinctness, , encourages tagging columns origin table, helps make explicit data comes (thereby improving readability , clarity , other fine qualities).


Comments

Post a Comment

Popular posts from this blog

Javascript line number mapping -

-
magically formatted comments change reported line number of javascript errors in browsers; this: //@line n "f" n line number , f file name. unfortunately, //@line appears ungoogleable. know there documentation on feature, , browsers support it? (i found references here , here .) from can find available in mozilla based browsers. it using js_setoptions command can turn these options on or off depending on values passed. https://developer.mozilla.org/en/js_setoptions
Read more

c# - Is it possible to remove an existing registration from Autofac container builder? -

-
something along lines: builder.registertype<mytype>().as<itype>(); builder.registertype<mytype2>().as<itype>(); builder.deregistertype<mytype>().as<itype>() var container = builder.build(); var types = container.resolve<ienumerable<itype>>(); assert.istrue(types.count == 1); assert.istrue(types[0].gettype == typeof(mytype2)); scenario: go through bunch of assemblies , go register types want make sure have 1 implementation of given type. need before create container. track on own nice if autofac me bit. this cannot done directly using containerbuilder , unless start on new one. mind you, having first built container should able construct new container filtering away unwanted types , reusing registrations first container. this: ... var container = builder.build(); builder = new containerbuilder(); var components = container.componentregistry.registrations .where(cr => cr.activator.limittype != typ...
Read more

php - Mysql PK and FK char(36) vs int(10) -

-
i have read mysql can handle indexes , searches better if tables keys integers rather char(36) uuid's. is worth converting int(10)? note: use 1 server , have have plans use multiple databases concurrently therefore removing 1 of big reasons using uuid. our tables innodb if makes difference. thanks in advance. usually rdbms can handle integer keys pk more efficient, other datatypes. reason how build index column, yes: long dont require string (or other typed) keys, should use integers. however: char(36) , int(10) far away being equal, because int(10) much smaller, char(36). dont know, if require many different keys, should keep in mind. update, complete last paragraph: int(10) 32-bit, char(36) 36 - ^byte^ (= 288-bit). not mean, int consumes less space, means, char(36) has 4 times more different keys.
Read more