mysql - joins with sub queries with sub query where conditions using main query fields

Question

i have a complaint table

|------------------------|
|    cid    |    desc    |
|------------------------|
|     1     |  faulty    |
|     2     |  broken    |
|     3     |  spoiled   |
|------------------------|

and an assignment table

|------------------------------------|
|     aid   |    cid    |    empid   |
|------------------------------------|
|     1     |     1     |     1      |
|     2     |     1     |     5      |
|     3     |     2     |     2      |
|     4     |     2     |            |
|     5     |     3     |     2      |
|     6     |     3     |     7      |
|------------------------------------|

each complaint can be assigned to atmost two employees i need to display a list in the below format

|---------------------------------------------------|
|    cid    |    desc    |   emp1id   |    emp2id   |
|------------------------|--------------------------|
|     1     |  faulty    |     1      |      5      |
|     2     |  broken    |     2      |             |
|     3     |  spoiled   |     2      |      7      |
|------------------------|--------------------------|

i wrote the query like this

select c.cid, c.desc, a1.empid as emp1id, a2.empid as emp2id
from complaint c 
left join (
    select aid, cid, empid
    from assignment aa 
    where aa.cid = c.cid 
    limit 0,1 
) as a1 on a1.cid = c.cid
left join (
    select aid, cid, empid
    from assignment ab
    where ab.cid = c.cid
    limit 1,1
) as a2 on a2.cid = c.cid

but it is not working, i am getting error for c.cid in sub queries. how to do?

Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Raymond Nijland, Feb 07 '19 at 12:32
And it looks as though a complaint can be assigned to nobody. Is that correct? — Strawberry, Feb 07 '19 at 12:35
"Which one is emp1?" @Strawberry the one offcource which is "first" inserted?? laxmi nagireddi SQL is by definition **orderless** you need some kind off column which you can use to identify the order and you can use `ORDER BY` on.. Like a id column with auto_increment option which your data in the assignment table clearly does not have — Raymond Nijland, Feb 07 '19 at 12:36
@RaymondNijland Why 'of course'. Do you have some insider knowledge? — Strawberry, Feb 07 '19 at 12:40
Why 'of course'. Do you have some insider knowledge? @Strawberry No i don't it was just a educated guess don't we see this kind off questions alot on stackoverflow?? Where the topicstarters assume SQL retains insert order when selecting without using `ORDER BY` on a column that identifies the order? — Raymond Nijland, Feb 07 '19 at 12:43

Janine Rawnsley · Accepted Answer · 2019-02-07T12:36:14.027

0

This might work? (I don't have mySql installed)

select  c.cid, 
        c.desc, 
        (
            select aid, cid, empid
            from assignment aa 
            where aa.cid = c.cid 
            limit 0,1 
        ) as emp1id, 
        (
            select aid, cid, empid
            from assignment ab
            where ab.cid = c.cid
            limit 1,1
        ) as emp2id
from complaint c

edited Feb 07 '19 at 12:36

answered Feb 07 '19 at 12:31

Janine Rawnsley

1,240
2
10
20

@RaymondNijland how about the above – Janine Rawnsley Feb 07 '19 at 12:36
@Janine Rawnsley,i posted a simplified version of my actual query, will test it and let you know. – laxmi nagireddi Feb 07 '19 at 12:47
@laxminagireddi did you read our comments? About insert order in SQL? – Raymond Nijland Feb 07 '19 at 12:53

mysql - joins with sub queries with sub query where conditions using main query fields

1 Answers1