MySQL Fetch Join Multiple Table With Multiple Row In A Single Query

Question

TABLE A#

C_ID   | C_NAME| C_DATE
-------+-------+--------
100    | ABC   | 11/12/16      
101    | EFG   | 12/12/16     
102    | HIJ   | 22/12/16

TABLE B

ID   | CID  | TYPE | PRICE
-----+------+----------------------
201  | 100  | 1    | 10
202  | 100  | 2    | 25
203  | 101  | 1    | 32
204  | 101  | 2    | 22
205  | 102  | 1    | 10
206  | 102  | 2    | 25

TABLE C

ID   | UID   | CID    | TYPE | BID PRICE
-----+-------+--------+------+-----
201  | 1001  | 100    | 1    | 20
202  | 1001  | 101    | 2    | 40
203  | 1001  | 102    | 1    | 50
204  | 1001  | 102    | 2    | 100
205  | 1002  | 101    | 1    | 20
206  | 1002  | 101    | 2    | 40
207  | 1002  | 102    | 1    | 50
208  | 1002  | 102    | 2    | 100

Hi folks,

I want to fetch all rows from table A, if the user(i.e 1001) who requested have any entry in Table C then the correspoding value (i.e bid_price) should return other wise bidprice should be null

For Given UID 1001 this will be the expected result

   A.C_ID | A.C_NAME| B.C_PRICE_1  | B.C_PRICE_2 | C.C_BID_PRICE_1 | C.C_BID_PRICE_2
    -------+---------+--------------+-------------+-----------------+---------------
100        |ABC      |10            |25           |20               | null
101        |EFG      |32            |22           |null             | 40
102        |HIJ      |10            |25           |50               |100

Where B.C_Price 1 is WHere b.Type = 1 and B.C_Price 2 is WHere b.Type = 2

It would be clear if you provide expected data from your sample data. — Viki888
– Viki888, Commented Dec 28, 2016 at 7:45
It seems unlikely that type would appear in both tables - although of course we don't know what a 'type' is — Strawberry
– Strawberry, Commented Dec 28, 2016 at 7:50
Possible duplicate of Join one row to multiple rows in another table — PereG
– PereG, Commented Dec 28, 2016 at 8:08
This is a display issue - generally best resolved in application code — Strawberry
– Strawberry, Commented Dec 28, 2016 at 8:27

additionster · Accepted Answer · 2016-12-28 07:35:56Z

0

Is the following what you want?

SELECT A.C_ID,A.C_NAME,B.TYPE,B.PRICE,C.BID_PRICE
FROM TABLE_A A, TABLE_B B, TABLE_C C
WHERE A.C_ID = B.CID AND B.CID = C.CID
AND C.UOD = {yourUID};

answered Dec 28, 2016 at 7:35

additionster

6284 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

51j0 Over a year ago

A.C_ID Should Be Distinct

additionster Over a year ago

So you want distinct A.C_ID with multiple C.BID_PRICE? it's impossible.

Randheer Singh Chouhan Over a year ago

As per your illustration it seems that you have one to many relationship between Table A & Table B as Table B contain multiple CID, Do you want to fetch max value of min valution from Table B and same goes with Table C ?

51j0 Over a year ago

I want Table B and Table C rows as Coloum For respective type '1' and '2'

Thorsten Kettner · Accepted Answer · 2016-12-28 10:23:37Z

0

Simply join the tables b and c twice:

select 
  a.c_id, 
  a.c_name, 
  b1.price as c_price_1,
  b2.price as c_price_2,
  c1.bid_price as c_bid_price_1,
  c2.bid_price as c_bid_price_2
from a
left join (select * from b where type = 1) b1 on b1.cid =  a.c_id
left join (select * from b where type = 2) b2 on b2.cid =  a.c_id
left join (select * from c where type = 1) c1 on c1.cid =  a.c_id
left join (select * from c where type = 2) c2 on c2.cid =  a.c_id;

or

select 
  a.c_id, 
  a.c_name, 
  b1.price as c_price_1,
  b2.price as c_price_2,
  c1.bid_price as c_bid_price_1,
  c2.bid_price as c_bid_price_2
from a
left join b b1 on b1.cid =  a.c_id and b1.type = 1
left join b b2 on b2.cid =  a.c_id and b2.type = 2
left join c c1 on c1.cid =  a.c_id and c1.type = 1
left join c c2 on c2.cid =  a.c_id and c2.type = 2;

answered Dec 28, 2016 at 10:23

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

4 Comments

51j0 Over a year ago

Where Should I Pass UID ?

51j0 Over a year ago

select a.id, a.heading, b1.share as c_price_1, b2.share as c_price_2, c1.points as c_bid_price_1, c2.points as c_bid_price_2 from news as a left join jk_newsbudget b1 on b1.newsId = a.id and b1.type = 4 left join jk_newsbudget b2 on b2.newsId = a.id and b2.type = 5 left join tbl_wallet c1 on c1.cid = a.id and c1.by_earn = 4 and c1.userId = 1001 left join tbl_wallet c2 on c2.cid = a.id and c2.by_earn = 5 and c2.userId = 1001 Order By a.id Limit 50

51j0 Over a year ago

its taking too much time and its giving the following error " Lost Connection To MySQL server during Query "

Thorsten Kettner Over a year ago

You should have appropriate indexes. I suggest: news(id, heading), jk_newsbudget(newsId, type, share), and tbl_wallet(cid, userId, by_earn, points). These are composite covering indexes which should make your query extremely fast.

Viki888 · Accepted Answer · 2016-12-28 10:24:40Z

0

If you are looking for below result, then try this solution.

Below is the query, you can make use of,

SELECT 
    a.C_ID, 
    a.C_NAME, 
    SUM(CASE WHEN b.TYPE = 1 THEN b.PRICE END) C_PRICE_1, 
    SUM(CASE WHEN b.TYPE = 2 THEN b.PRICE END) C_PRICE_2,
    SUM(CASE WHEN c.TYPE = 1 THEN c.BID_PRICE END) C_BID_PRICE_1, 
    SUM(CASE WHEN c.TYPE = 2 THEN c.BID_PRICE END) C_BID_PRICE_2 
FROM tableA a
 JOIN tableB b 
  ON a.C_ID = b.CID
 LEFT JOIN tableC c 
  ON a.C_ID = c.CID 
   AND b.TYPE = c.TYPE
   AND c.UID = 1001
GROUP BY a.C_ID, a.C_NAME;

You can try the SQL demo

Hope this would help you.

edited Dec 28, 2016 at 10:24

answered Dec 28, 2016 at 9:24

Viki888

2,7742 gold badges15 silver badges16 bronze badges

9 Comments

51j0 Over a year ago

Where are you passing UID ? and SUM is adding All the values in table B.

Viki888 Over a year ago

There is no UID in your expected result. And I am using case statement inside SUM function, hence the sum will happen only if the condition is met

51j0 Over a year ago

I Went to fetch all rows from table A, if the user who requested have any entry in Table C then the correspoding value [bid_price] should return other wise bidprice should be null

Viki888 Over a year ago

Yeah, that is the way the above query works. If you notice, for CID=100, there is no record for TYPE=2, hence column C_BID_PRICE_2 is having null

Viki888 Over a year ago

@alanabraham I have updated my answer with UserId filter too.

|

Collectives™ on Stack Overflow

MySQL Fetch Join Multiple Table With Multiple Row In A Single Query

TABLE A#

TABLE B

TABLE C

3 Answers 3

4 Comments

4 Comments

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

TABLE A#

TABLE B

TABLE C

3 Answers 3

4 Comments

4 Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related