A
A
av_tyschenko2017-08-29 14:07:43
MySQL
av_tyschenko, 2017-08-29 14:07:43

How to make a query with multi-JOIN?

Dear experts of databases, prompt please.
I have 2 tables "Declarations" and "Additional fields". I need to make a request so that many values ​​\u200b\u200bare indicated in the search filter, and only those that are specified are displayed.
It turns out that I enter several JOINs into the query, and it gives me the following error:
Error Number: 1066
Not unique table/alias: 't2'

SELECT
  `t1`.`ID`          AS `ID`,
  `t1`.`price`       AS `price`,
  `t1`.`title`       AS `title`,
  `t1`.`category_id` AS `category_id`,
  `t1`.`date`        AS `date`,
  `t1`.`type`        AS `type`,
  `t1`.`country_id`  AS `country_id`
FROM `ozago_ads` `t1` INNER JOIN `ozago_ads_meta` `t2` ON `t2`.`id_ad` = `t1`.`ID` AND `t2`.`meta_key` = 44
  INNER JOIN `ozago_ads_meta` `t2` ON `t2`.`id_ad` = `t1`.`ID` AND `t2`.`meta_key` = 370
  INNER JOIN `ozago_ads_meta` `t2` ON `t2`.`id_ad` = `t1`.`ID` AND `t2`.`meta_key` = 51
  INNER JOIN `ozago_ads_meta` `t2` ON `t2`.`id_ad` = `t1`.`ID` AND `t2`.`meta_key` = 46
  INNER JOIN `ozago_ads_meta` `t2` ON `t2`.`id_ad` = `t1`.`ID` AND `t2`.`meta_key` = 49
  INNER JOIN `ozago_ads_meta` `t2` ON `t2`.`id_ad` = `t1`.`ID` AND `t2`.`meta_key` = 50
WHERE `type` = '1' AND `region_id` = '4891' AND `category_id` IN ('198') AND `price` > '40000' AND `price` < '200000';

Filename: models/Loop.php
Line Number: 176

Answer the question

In order to leave comments, you need to log in

2 answer(s)
M
Max, 2017-08-29
@AloneCoder

Why so many JOINs if you can write AND t2 in WHERE. meta_key IN()?

C
Cage, 2017-08-30
@Cage

SELECT DISTINCT
  `t1`.`ID`          AS `ID`,
  `t1`.`price`       AS `price`,
  `t1`.`title`       AS `title`,
  `t1`.`category_id` AS `category_id`,
  `t1`.`date`        AS `date`,
  `t1`.`type`        AS `type`,
  `t1`.`country_id`  AS `country_id`
FROM `ozago_ads` `t1` 
  INNER JOIN `ozago_ads_meta` `t2` ON `t2`.`id_ad` = `t1`.`ID` AND `t2`.`meta_key` IN (44, 370, 51, 46, 49, 50)
WHERE `type` = '1' AND `region_id` = '4891' AND `category_id` IN ('198') AND `price` > '40000' AND `price` < '200000';

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question