Блог

Я наконец-то написал все свои запросы (спасибо всем, кто мне помог).

Первый:

 SELECT 
  cards.card_id, 
  concat(cards.title, ' By Amy') AS TitleConcat, 
  cards.meta_description,
  cards.description, 
  '' as Bob,
  '' as Weight,
  '' as UPC,
  cards.seo_keywords,
  concat('http://www.amyadele.com/attachments//cards/',cards.card_id,'/',cards.card_image) AS ImageURL,
  card_import.artist,
  concat('ARTIST - ', card_import.artist) AS Brand,
  min(card_lookup_values.card_price) AS LowPrice,
 replace(lower(concat( 'http://www.amyadele.com/', pcat.seoname,'/',cat.seoname, '/', cards.seoname, '.htm' )),' ',' ') AS link,
            concat(pcat.name,'>',cat.name) as Merchant


FROM
  cards
  join card_cheapest on cards.card_id = card_cheapest.card_id
  left join card_import on card_import.card_id = cards.card_id
  join card_lookup_values on card_lookup_values.card_id = cards.card_id
    INNER JOIN card_categories cc ON cards.card_id = cc.card_id AND cards.card_live = 'y' AND cards.active = 'y' AND cc.active = 'Y'
          INNER JOIN categories cat ON cat.category_id = cc.category_id AND cat.active = 'Y'
          INNER JOIN categories pcat ON cat.parent_category_id = pcat.category_id


WHERE card_lookup_values.card_price > 0
GROUP BY
  cards.card_id
ORDER BY
  cards.card_id                 
  

И второй:

 SELECT cards.card_id, round(min(card_lookup_values.card_price), 2) AS 'price', min(cast(lookup_details.value as signed)) as 'quantity'
FROM cards
INNER JOIN card_lookup_values ON cards.card_id = card_lookup_values.card_id
INNER JOIN lookup_details ON card_lookup_values.lookup_detail_id = lookup_details.lookup_detail_id
WHERE card_lookup_values.lookup_id = 7
-- AND c.card_id = 'al007'
GROUP BY cards.card_id;
  

Я пару раз пытался заставить это работать (моя последняя попытка)

 SELECT 
  cards.card_id, 
  concat(cards.title, ' By Amy') AS TitleConcat, 
  cards.meta_description,
  cards.description, 
  '' as Bob,
  '' as Weight,
  '' as UPC,
  cards.seo_keywords,
  concat('http://www.amyadele.com/attachments//cards/',cards.card_id,'/',cards.card_image) AS ImageURL,
  card_import.artist,
  concat('ARTIST - ', card_import.artist) AS Brand,
  min(card_lookup_values.card_price) AS LowPrice,
 replace(lower(concat( 'http://www.amyadele.com/', pcat.seoname,'/',cat.seoname, '/', cards.seoname, '.htm' )),' ',' ') AS link,
            concat(pcat.name,'>',cat.name) as Merchant,
 round(min(card_lookup_values.card_price), 2) AS 'price',
 min(cast(lookup_details.value as signed)) as 'quantity'

FROM
  cards
  join card_cheapest on cards.card_id = card_cheapest.card_id
  left join card_import on card_import.card_id = cards.card_id
  join card_lookup_values on card_lookup_values.card_id = cards.card_id
    INNER JOIN card_categories cc ON cards.card_id = cc.card_id AND cards.card_live = 'y' AND cards.active = 'y' AND cc.active = 'Y'
          INNER JOIN categories cat ON cat.category_id = cc.category_id AND cat.active = 'Y'
          INNER JOIN categories pcat ON cat.parent_category_id = pcat.category_id
INNER JOIN card_lookup_values ON cards.card_id = card_lookup_values.card_id
INNER JOIN lookup_details ON card_lookup_values.lookup_detail_id = lookup_details.lookup_detail_id

WHERE card_lookup_values.card_price > 0 and where card_lookup_values.lookup_id = 7
GROUP BY
  cards.card_id
ORDER BY
  cards.card_id                 
  

Но я продолжаю получать сообщение об ошибке, в котором говорится: Не уникальная таблица / псевдоним: ‘card_lookup_values’

Кто-нибудь знает, что я делаю не так?

Вы дважды присоединяетесь к card_lookup_values таблице. Вы должны присвоить каждой ссылке на эту таблицу уникальный псевдоним, чтобы различать их. Я использовал clv1 и clv2 во фрагменте ниже для иллюстрации.

 ...
join card_lookup_values clv1 on clv1.card_id = cards.card_id
INNER JOIN card_categories cc ON cards.card_id = cc.card_id AND cards.card_live = 'y' AND cards.active = 'y' AND cc.active = 'Y'
      INNER JOIN categories cat ON cat.category_id = cc.category_id AND cat.active = 'Y'
      INNER JOIN categories pcat ON cat.parent_category_id = pcat.category_id
INNER JOIN card_lookup_values clv2 ON cards.card_id = clv2.card_id
...