CODEWITHSUNDEEP
mysqlsqljoin
Arebhy Sridaran
Arebhy Sridaran

Reputation: 586

joining three queries from same table

I have to join 3 queries from same table. I have tried this code:

SELECT t1.`board`
      ,t1.`price`
      ,t2.`price`
      ,t3.`price`
  from boardtype t1
  RIGHT JOIN(SELECT `price`
      from boardtype
      WHERE `acnonac`='ac' AND `roomtype`='single')
  t2 on t1.`board` = t2.`board`
  RIGHT JOIN(
      SELECT 
      `price`
      from boardtype 
      WHERE `acnonac`='ac' AND `roomtype`='double'
  ) t3 on t1.`board` = t3.`board`
WHERE `acnonac`='ac' AND `roomtype`='triple'
ORDER by id

Simply say I'm trying to join these three queries:

SELECT `board`,`price` FROM boardtype WHERE `acnonac`='ac' AND `roomtype`='single'
SELECT `board`,`price` FROM boardtype WHERE `acnonac`='ac' AND `roomtype`='double'
SELECT `board`,`price` FROM boardtype WHERE `acnonac`='ac' AND `roomtype`='triple'

I don't know where I made mistake.

Upvotes: 1

Views: 292

Answers (2)

MJ Miraj
MJ Miraj

Reputation: 115

Maybe it was more efficient

SELECT `boardtype`.`board`
      ,`boardtype`.`price`
  from `boardtype` WHERE `boardtype`.`acnonac`='ac' AND `boardtype`.`roomtype` IN ('single', 'double', 'triple')

Thanks

Upvotes: 1

acdcjunior
acdcjunior

Reputation: 135752

You have no board in your subselects, and yet you write the ON clauses using them.

Add the board to the subselects:

SELECT t1.`board`
      ,t1.`price`
      ,t2.`price`
      ,t3.`price`
  from boardtype t1
  RIGHT JOIN
      (SELECT `price`, `board`
      from boardtype
      WHERE `acnonac`='ac' AND `roomtype`='single') t2 on t1.`board` = t2.`board`
  RIGHT JOIN(
      SELECT `price`, `board`
      from boardtype
      WHERE `acnonac`='ac' AND `roomtype`='double') t3 on t1.`board` = t3.`board`
WHERE `acnonac`='ac' AND `roomtype`='triple'
ORDER by id

Upvotes: 2

Related Questions