CODEWITHSUNDEEP
mysql
Ravi Maniyar
Ravi Maniyar

Reputation: 667

How to select one unique value in mysql for the first row then show nothing for that value, BUT show all the records?

there are 2 mysql table like this

table 1

--id-----config_name
--1-----OS
--2-----Control Panel
--3-----Bandwidth

table 2

id -- config_id--- config_Option_name --- Price
1--------1-------------Windows 2008--------20.00
2--------1-------------CentOs 5----------------0.00
3--------2-------------whm/cPanel-----------30.00
4--------2-------------Plesk-------------------50.00

Now I want to show them like this, ONLY USING MYSQL.

  1. OS

    Windows 2008 = 20.00

    CentOs 5 = 00.00

  2. Control Panel

    whm/cPanel= 30.00

    Plesk = 50.00

IS THIS POSSIBLE? So now "os" or "control panel" are selected once although if we use group by or join it comes twice.

Using single SQL statement

Upvotes: 1

Views: 319

Answers (1)

valex
valex

Reputation: 24134

Don't use SQL to format output like this. You should do it on a client side.

Anyway here is a query to do it in SQL only (SQLFiddle demo):

select cfgN from
(
   select concat('\t',
                 CONFIG_OPTION_NAME,
                 '=',FORMAT(Price, 2) ) as cfgN, 
                 config_id,
                 t2.ID,
                 CONFIG_OPTION_NAME,
                 Price 
   from Table2 t2
   join Table1 t1 on (t2.config_id=t1.id)

   union all

   select concat(cast(id as char),
                 '. ',CONFIG_NAME) as cfgN, 
          id config_id,
          null ID,
          CONFIG_NAME, 
          null Price 
   from table1 

) t3 order by config_id,id

Upvotes: 1

Related Questions