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, Dec 28 '16 at 07:45
It seems unlikely that type would appear in both tables - although of course we don't know what a 'type' is — Strawberry, Dec 28 '16 at 07:50
Possible duplicate of [Join one row to multiple rows in another table](http://stackoverflow.com/questions/2892816/join-one-row-to-multiple-rows-in-another-table) — PereG, Dec 28 '16 at 08:08
In your sample `C_ID=102`, does not have any data in `TABLE B`. But in your expected data, you have `price` value from TABLE B as `10` and `25`. — Viki888, Dec 28 '16 at 08:20
Yeah just Assume it have 10 and 25 respective values in table B — 51j0, Dec 28 '16 at 08:22
This is a display issue - generally best resolved in application code — Strawberry, Dec 28 '16 at 08:27

score 0 · Answer 1 · answered Dec 28 '16 at 07:35

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 '16 at 07:35

additionster

628
4
14

**A.C_ID** Should Be Distinct – 51j0 Dec 28 '16 at 07:39
So you want distinct A.C_ID with multiple C.BID_PRICE? it's impossible. – additionster Dec 28 '16 at 07:42
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 ? – Randheer Singh Chouhan Dec 28 '16 at 07:50
I want Table B and Table C rows as Coloum For respective type '1' and '2' – 51j0 Dec 28 '16 at 07:57

Viki888 · Answer 2 · 2016-12-28T10:24:40.140

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 '16 at 10:24

answered Dec 28 '16 at 09:24

Viki888

2,686
2
13
16

Where are you passing UID ? and **SUM** is adding All the values in table B. – 51j0 Dec 28 '16 at 10:00
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** – Viki888 Dec 28 '16 at 10:04
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 – 51j0 Dec 28 '16 at 10:11
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 Dec 28 '16 at 10:19
@alanabraham I have updated my answer with UserId filter too. – Viki888 Dec 28 '16 at 10:30
SUM Function is adding all the Price Where Type = 1 (i.e 52) – 51j0 Dec 28 '16 at 10:40
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/131687/discussion-between-viki888-and-alanabraham). – Viki888 Dec 28 '16 at 10:46
okayy can you ping me once .. or else can we disccuss somewhere else – 51j0 Dec 28 '16 at 11:05
Join the chat http://chat.stackoverflow.com/rooms/131687/discussion-between-viki888-and-alanabraham – Viki888 Dec 28 '16 at 11:06

score 0 · Answer 3 · answered Dec 28 '16 at 10:23

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 '16 at 10:23

Thorsten Kettner

89,309
7
49
73

Where Should I Pass UID ? – 51j0 Dec 28 '16 at 10:30
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 Dec 28 '16 at 11:01
its taking too much time and its giving the following error " Lost Connection To MySQL server during Query " – 51j0 Dec 28 '16 at 11:10
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. – Thorsten Kettner Dec 28 '16 at 11:16

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

TABLE A#

TABLE B

TABLE C

3 Answers3