CODEWITHSUNDEEP
phpmysqlmysqli
Jerry Jones
Jerry Jones

Reputation: 796

Get result from three tables using join from which one table contain multiple result

I have three tables with the following columns and data:

table_one

id  | balance
100 | 10.00
101 | 5.00
102 | 8.00

table_two

id | number
100 | 0890980980
100 | 7657657655
101 | 7657657656
102 | 1231231233

table_three

id  | name      | active
100 | nameOne   | 1
101 | nameTwo   | 0
102 | namrThree | 1

Now my query will be

Query 1. SELECT * FROM table_one WHERE balance <= 8
Query 2. SELECT number(only first_matched_row) FROM table_two WHERE table_one.id = table_two.id
Query 3. SELECT name FROM table_three WHERE table_three.id = table_one.id AND table_three.active = 1

How can I join these three queries and get a single query. Please note that table_two will get multiple rows so I want take the first matched row and omit the rest where table_two.id matches.

Expected Result:

id  | name      | number
100 | nameOne   | 0890890890
102 | nameThree | 1231231233

SOLVED ANSWER:

Select onetwo.id, three.name, two.number from 
(Select two.id from
(SELECT id as id1 FROM table_one WHERE balance <= 8)one
inner join
table_two two on one.id1 = two.id
)onetwo
inner join
table_two two on two.id=onetwo.id
inner join
table_three three on three.id = onetwo.id AND three.active = 1 group by two.id

Upvotes: 0

Views: 53

Answers (1)

Haytem BrB
Haytem BrB

Reputation: 1499

Would you try:

      Select onetwo.id, three.name, onetwo.number from 
(Select two.id from
(SELECT id as id1 FROM table_one WHERE balance <= 8)one
inner join
table_two two on one.id1 = two.id
)onetwo
inner join
table_three three on three.id = onetwo.id AND three.active = 1 group by onetwo.id

You can have more elegant queries using advanced RDBMS, but not with MySQL unfortunately.

Upvotes: 1

Related Questions