CODEWITHSUNDEEP
sql
NimChimpsky
NimChimpsky

Reputation: 47280

basic sql : selecting the same column multiple times in one query, when each occurrence is dependent on different where clause

What is the best way to to perform this query. I have the following table

mytable with columns 

x y 
1 a
2 b
3 c

and I would like to (in pseudo sql)

select x as x1 ,x as x2, x as x3 from mytable where ????

when 

x1 is x where y=a

x2 is x where y=b

x3 is x where y=c

so I would like as a result

1, 2, 3

I am currently using cte's and and a very large dataset, I am trying to reduce the query time, is it always necessary to have 3 table scans ?

Upvotes: 8

Views: 47295

Answers (8)

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

SQL Fiddle

MySQL 5.5.32 Schema Setup:

CREATE TABLE Table1
    (`x` int, `y` varchar(1))
;

INSERT INTO Table1
    (`x`, `y`)
VALUES
    (1, 'a'),
    (2, 'b'),
    (3, 'c')
;

CREATE TABLE mytable
    (`x` int, `y` varchar(1))
;

INSERT INTO mytable
    (`x`, `y`)
VALUES
    (1, 'a'),
    (2, 'b'),
    (3, 'c')
;

Query 1:

SELECT x1.x as x1, x2.x as x2, x3.x as x3
FROM mytable x1
INNER JOIN mytable x2 ON x2.y='b'
INNER JOIN mytable x3 ON x3.y='c'
WHERE x1.y='a'

Results:

| X1 | X2 | X3 |
|----|----|----|
|  1 |  2 |  3 |

Upvotes: 0

aqm
aqm

Reputation: 3034

I would of done this :

SELECT
    tableRowA.x as x1
    tableRowB.x as x2
    tableRowC.x as x3
FROM
    table as tableRowA,
    table as tableRowB,
    table as tableRowC
WHERE
    tableRowA.y = 1
    tableRowB.y = 2
    tableRowC.y = 3

Bit easier to understand, and pull out more info if you need multiple columns from each row

Upvotes: 5

PatrickP61
PatrickP61

Reputation: 76

From your question it appears you would like the last three instances of a condition, or tie three different conditions together. Would the following example satisfy your question:

mytable:
(unique keys 1..n)      (col1)  
student-id | course-id | grade
s1           gen101      g1
s1           cmp202      g2
s1           psy303      g3
s1           c4          g4
s2           c1          g5

Lets say we only want the students which have three specific courses (gen101, cmp202, and psy303) and show those grades ignoring anyone else.

select gen.student-id  as student-id
     , gen.grade       as gen101-gr
     , cmp.grade       as cmp202-gr
     , psy.grade       as psy303-gr
  from mytable  gen
     , mytable  cmp
     , mytable  psy
 where gen.course-id    = 'gen101'
   and gen.student-id   = cmp.student-id
   and cmp.course-id    = 'cmp202'
   and cmp.studnet-id   = psy.student-id
   and psy.course-id    = 'psy303'

This should give one row:

student-id  gen101-gr cmp202-gr psy303-gr
s1          g1        g2        g3

Hope that gives you enough to work on.

Upvotes: 1

user359040
user359040

Reputation:

In the example given, there are only 3 rows of input and one row of output. I assume that there is going to be at least one other column involved, such that input data:

w  x  y
---------
w1 1  a
w1 2  b
w1 3  c
w2 4  a
w2 5  b
w2 6  c
.
.
.

is to become output:

w  x1 x2 x3
-----------
w1 1  2  3
w2 4  5  6
.
.
.

This can be done in a single pass using a query like:

select w,
       max(case when y = 'a' then x end) x1,
       max(case when y = 'b' then x end) x2,
       max(case when y = 'c' then x end) x3
from datatable
where y in ('a','b','c')
group by w

Upvotes: 3

Kalle
Kalle

Reputation: 2293

My recommendation would be to select the results ordered or grouped by column y and the use that information to split the resultset into several lists for an application to process. If you want to do this only in the database I'm afraid multiple table scans (or joins) are necessary.

Another fix is to migrate the information in column y to another table (with a foreign key reference), to be able to join more effectively to that.

Upvotes: 0

Silver Light
Silver Light

Reputation: 45902

Another solution:

SELECT x, y FROM table WHERE y IN ('a', 'b')

You will have a result set:

x | y
-----
1 | a
2 | b

This result set can then be used in an application to get desired result.

Upvotes: 1

Silver Light
Silver Light

Reputation: 45902

You should use 3 queries. It will be a lot faster with proper indexing when self joins. Additionally it will be more readable.

If you would like one query call, it might be this :)

SELECT
(SELECT x FROM table WHERE y=1) AS x1,
(SELECT x FROM table WHERE y=2) AS x2,
(SELECT x FROM table WHERE y=3) AS x3

Upvotes: 15

dotariel
dotariel

Reputation: 1594

SELECT Case When y = 1 Then x1 When y = 2 Then x2 Else x3 End FROM mytable

Upvotes: 0

Related Questions