Bitrix / diff sqls

--------------------------------------------
SELECT a.iblock_id, a.name, b.VALUE
FROM b_iblock_element a
LEFT OUTER JOIN b_iblock_element_property b ON a.id = b.iblock_element_id
WHERE a.name LIKE  '%Break%'
AND a.active =  'Y'
LIMIT 0 , 30

--------------------------------------------
SELECT iblock_id, name, active, property_type, default_value
FROM b_iblock_property WHERE iblock_id IN (1,22) ORDER BY name


--------------------------------------------
SELECT * FROM b_iblock_fields WHERE iblock_id IN (1,22) ORDER BY field_id


--------------------------------------------
SELECT *
FROM b_iblock_element a

WHERE a.name LIKE  '%Break%'
AND a.active =  'Y'
--------------------------------------------
SELECT a.name, b.content_type, b.subdir, b.file_name FROM b_medialib_item a,
b_file b WHERE a.source_id = b.id
AND a.name LIKE '%8FBT6190%'

--------------------------------------------
SELECT a.iblock_id, a.name, c.name, c.code, c.property_type, b.VALUE
FROM b_iblock_element a
LEFT OUTER JOIN b_iblock_element_property b ON a.id = b.iblock_element_id
LEFT OUTER JOIN b_iblock_property c ON b.iblock_property_id = c.id
WHERE a.name LIKE  '%Break%'
AND a.active =  'Y'


--------------------------------------------
SELECT  z.aname, z.cname, z.code, z1.id, z1.name FROM
(SELECT a.iblock_id, a.name aname, c.name cname, c.code, c.property_type, b.VALUE
FROM b_iblock_element a
LEFT OUTER JOIN b_iblock_element_property b ON a.id = b.iblock_element_id
LEFT OUTER JOIN b_iblock_property c ON b.iblock_property_id = c.id
WHERE a.name LIKE  '%Break%'
AND a.active =  'Y' AND c.property_type='G' AND a.iblock_id=1) z,

b_iblock_section z1 WHERE z.VALUE = z1.id

--------------------------------------------
SELECT a.id,a.name
FROM b_iblock_section_element b, b_iblock_element a
WHERE b.iblock_element_id=a.id AND b.iblock_section_id=39

--------------------------------------------
SELECT els.iblock_type_id, els.block_id, els.block_name,
els.section_id, els.section_name,
els.element_id, els.element_name,
els.preview_picture,
 els.detail_picture, els.active,
 img4.content_type, img4.subdir, img4.file_name,
 img5.content_type, img5.subdir, img5.file_name
FROM

(SELECT
d.iblock_type_id, d.id block_id, d.name block_name,
c.id section_id, c.name section_name,
a.id element_id, a.name element_name,
a.preview_picture, a.detail_picture, a.active

FROM b_iblock_section_element b, b_iblock_element a,
b_iblock_section c, b_iblock d
WHERE b.iblock_element_id=a.id AND
b.iblock_section_id = c.id AND c.iblock_id= d.id AND
b.iblock_section_id=39) els

LEFT OUTER JOIN
(SELECT b.id,  b.content_type, b.subdir, b.file_name FROM  b_file b)
img4  ON  els.preview_picture=img4.id

LEFT OUTER JOIN
(SELECT b.id,  b.content_type, b.subdir, b.file_name FROM  b_file b)
img5  ON  els.detail_picture=img5.id


--------------------------------------------
SELECT  els.iblock_type_id, block_id, block_name,
 els.section_id, els.section_name, els.element_id, els.element_name,
els.detail_picture, els.active, img.content_type, img.subdir, img.file_name,
 prev.content_type,  prev.subdir, prev.file_name
 FROM

(SELECT d.iblock_type_id, d.id block_id, d.name block_name,
b.id section_id, b.name section_name, a.id element_id ,a.name element_name,
a.preview_picture, a.detail_picture, a.active
FROM b_iblock_element a, b_iblock_section b, b_iblock d
WHERE  a.iblock_section_id=b.id AND b.iblock_id=d.id AND
a.iblock_section_id=39) els

LEFT OUTER JOIN  (SELECT b.id,  b.content_type, b.subdir, b.file_name FROM  b_file b)  img
      ON  els.detail_picture=img.id

LEFT OUTER JOIN (SELECT b.id, b.content_type, b.subdir, b.file_name FROM b_file b)  prev
     ON els.preview_picture=prev.id

--------------------------------------------

SELECT  z.el_name, z.property_name, z.property_code,
z1.id, z1.name,
z2.name block_name, z2.iblock_type_id
 FROM
(SELECT a.iblock_id, a.name el_name, c.name property_name, c.code property_code ,
 c.property_type, b.VALUE property_value
FROM b_iblock_element a
LEFT OUTER JOIN b_iblock_element_property b ON a.id = b.iblock_element_id
LEFT OUTER JOIN b_iblock_property c ON b.iblock_property_id = c.id
WHERE  a.id=380 AND c.property_type='G' AND a.iblock_id=1) z,

b_iblock_section z1,
b_iblock z2
WHERE z.property_value = z1.id
AND z1.iblock_id=z2.id

--------------------------------------------
SELECT gal.section_id, gal.section_name, el.element_name, el.element_id,
el.active, el.block_id, el.block_name, el.iblock_type_id
FROM

(SELECT a.id section_id, b.iblock_type_id, b.id block_id, b.name block_name, a.name section_name
FROM b_iblock_section a, b_iblock b
WHERE a.iblock_id=b.id AND a.id=50) gal
 LEFT OUTER JOIN
(SELECT  a.id element_id, a.name element_name, a.active,
 d.id block_id, d.name block_name, d.iblock_type_id,
b.VALUE  FROM b_iblock_element a, b_iblock_element_property b,
b_iblock_property c , b_iblock d
WHERE b.iblock_element_id=a.id AND b.iblock_property_id=c.id  AND c.property_type='G'
AND a.iblock_id=d.id
) el
ON gal.section_id = el.VALUE

--------------------------------------------
SELECT  a.id section_id, a.name section_name, a.picture, a.detail_picture,
img4.id, img4.content_type, img4.subdir, img4.file_name,
img5.id, img5.content_type, img5.subdir, img5.file_name
FROM b_iblock_section a
LEFT OUTER JOIN
  (SELECT  c.id, c.content_type, c.subdir, c.file_name
  FROM  b_file c)  img4

ON a.picture = img4.id

LEFT OUTER JOIN
  (SELECT  c.id,  c.content_type, c.subdir, c.file_name
  FROM  b_file c)  img5
ON a.detail_picture = img5.id

WHERE a.id=49

--------------------------------------------
SELECT  d.iblock_type_id, d.id block_id,  d.name block_name,
a.id section_id, a.name section_name,
c.id element_id, c.name element_name

FROM b_iblock_section a, b_iblock_section_element b, b_iblock d,
b_iblock_element c
WHERE b.iblock_section_id = a.id  AND a.iblock_id = d.id AND b.iblock_element_id=c.id
AND c.iblock_section_id IS NULL

--------------------------------------------

SELECT *
FROM (

SELECT d.iblock_type_id, d.id block_id, d.name block_name, a.id section_id, a.name section_name, c.id element_id, c.name element_name, c.iblock_section_id alt_section_id
FROM b_iblock_section a, b_iblock_section_element b, b_iblock d, b_iblock_element c
WHERE b.iblock_section_id = a.id
AND a.iblock_id = d.id
AND b.iblock_element_id = c.id
)inf
WHERE section_id <> ifnull( alt_section_id, 0 )

--------------------------------------------
SELECT element_id, element_name, property_id, property_name,
 VALUE, linked_section_name, linked_block_id, linked_block_name,
iblock_type_id
FROM
(SELECT a.id element_id, a.name element_name,
c.id property_id, c.name property_name
 FROM b_iblock_element a,
b_iblock b,
b_iblock_property c
WHERE

a.iblock_id = b.id AND
 c.iblock_id=b.id AND
  c.property_type='G'  AND
   a.iblock_id IN (22,23,24,25)) el
INNER JOIN

(SELECT a.iblock_property_id, a.iblock_element_id,
a.VALUE, b.name linked_section_name,
c.id linked_block_id, c.name linked_block_name, c.iblock_type_id
FROM b_iblock_element_property a, b_iblock_section b, b_iblock c
WHERE a.VALUE=b.id AND b.iblock_id = c.id
) f
ON el.property_id=f.iblock_property_id
AND el.element_id = f.iblock_element_id

--------------------------------------------
SELECT
a.iblock_type_id, a.id block_id, a.name block_name,
b.id section_id, b.name section_name,
c.id small_pic_id, concat('../upload/' , c.subdir ,'/', c.file_name) small_pic_addr,
d.id pic_id,  concat( '../upload/'  , d.subdir ,'/', d.file_name) pic_addr

FROM b_iblock a, b_iblock_section b
LEFT OUTER JOIN b_file c ON b.picture=c.id
LEFT OUTER JOIN b_file d ON b.detail_picture=d.id

WHERE a.id=b.iblock_id  AND NOT(isnull(c.id) AND isnull(d.id))

ORDER BY a.iblock_type_id, a.id, b.id

--------------------------------------------
SELECT
a.iblock_type_id, a.id block_id, a.name block_name,
b.id element_id, b.name element_name,
c.id small_pic_id, concat('../upload/' , c.subdir , '/', c.file_name) small_pic_addr,
d.id pic_id,  concat( '../upload/'  , d.subdir ,'/', d.file_name) pic_addr

FROM b_iblock a, b_iblock_element b
LEFT OUTER JOIN b_file c ON b.preview_picture=c.id
LEFT OUTER JOIN b_file d ON b.detail_picture=d.id

WHERE a.id=b.iblock_id  AND NOT(isnull(c.id) AND isnull(d.id))

ORDER BY a.iblock_type_id, a.id, b.id
--------------------------------------------
--------------------------------------------
--------------------------------------------
--------------------------------------------