How can I display data from the same table side by side
database, inner-join, mysql, php, sql
Solution
You can use a self-join, in other words you can join on your table twice similar to the way you started. Since you stated that `S=1` will always exist, then you can use the following query:
select t1.id,
t1.num S1,
t2.num S2
from yourtable t1
left join yourtable t2
on t1.id = t2.id
and t2.s = 2
where t1.s = 1;
See SQL Fiddle with Demo. Using a LEFT JOIN on your table will return all rows with the value of `S=1` even if `S=2` doesn't exist in your table.
You could also use an aggregate function with a CASE expression to get the result:
select
id,
sum(case when s = 1 then num end) S1,
sum(case when s = 2 then num end) S2
from yourtable
group by id;
See SQL Fiddle with Demo
Problem
Say I have 1 table in mysql (called get) like this: ``` ID S Num 00 1 506 00 2 620 01 1 562 01 2 564 02 1 548 02 2 484 03 1 488 03 2 895 ``` I am trying to get it in this format: ``` ID S1 S2 00 506 620 01 562 564 02 548 484 03 488 895 ``` So far I have this, but it gives me an error: ``` select id,d.S1,c.S2 from (select S as S1 from get where S=1)d inner join (select s as S2 from get where S=2)c using (id); ``` I am still not too sure about joins, but this seems to make sense. EDIT: S can have only 1 value at times, in these times, this value will be S1