1

My purpose:

select entries with has_t "1" whenever it is there, if its not there, select entry with has_t "2". All remaining entries for the relevant visit, should go inside "more_items" column

The issue:

The current code is first selecting the has_t "2", instead of has_t "1"

SELECT iu.*,`t`.`o_id`,
v.GID,v.MSID,
GROUP_CONCAT(DISTINCT CONCAT(iu.item_no,'|##|',t.s_amount,'|##|',t.e_item_ordered,'|##|',IFNULL(t.e_item_name,'No-Name-In-T-TBL'),'|##|',IFNULL(t.com,''),'|##|',IFNULL(t.post_s_e,''),'|##|',IFNULL(t.o_id,'')) SEPARATOR '|#|') AS MoreItems,
t.post_s_e,t.e_item_name,t.click_time
FROM `items_unique` iu
LEFT JOIN visits v ON v.id = iu.visit_id
LEFT JOIN trans t ON t.visit_id = iu.visit_id AND t.e_item_ordered = iu.e_item_ordered
WHERE iu.product_id = $PID AND has_t > 0 AND (t.com >= 10 OR has_t = 1)
GROUP BY `v`.`id` ORDER BY v.visited_at DESC LIMIT 10

Note: Previous code version seemed to have done it using non-compliant SQL sub-query-order, which anyway doesn't work on MariaDB:

ORDER BY `iu`.`has_t` LIMIT 18446744073709551615
Rick James
  • 135,179
  • 13
  • 127
  • 222
user8411456
  • 347
  • 1
  • 5
  • 18
  • Questions should include code examples, table structure, sample data, and a tag for the DBMS implementation (e.g. MySQL, PostgreSQL, Oracle, MS SQL Server, IBM DB2, etc.) being used. – rsjaffe Feb 03 '19 at 19:02
  • You should illustrate that question with the tables' structure as `CREATE TABLE` statements, sample data as `INSERT INTO` statements and the expected result with the sample data. Edit the question to do so, don't put it in comments. – sticky bit Feb 03 '19 at 19:03
  • I added MariaDB tag. I don't have other data available right now. – user8411456 Feb 03 '19 at 19:04
  • Then you should consider deleting the question for now and ask a new one including all the information once you have it. – sticky bit Feb 03 '19 at 19:14
  • "Note: Previous code version seemed to have done it using non-compliant SQL sub-query-order, which anyway doesn't work on MariaDB: ORDER BY `iu`.`has_t` LIMIT 18446744073709551615" That should work in MariaDB it's in the [manual](https://mariadb.com/kb/en/library/why-is-order-by-in-a-from-subquery-ignored/).. – Raymond Nijland Feb 04 '19 at 00:22
  • .... Besides i advice you provide example data and expected results, a query only question is almost impossible to answer correctly.. [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Raymond Nijland Feb 04 '19 at 00:25
  • Also `SELECT iu.*,t.o_id, v.GID,v.MSID, ... t.post_s_e,t.e_item_name,t.click_time .... GROUP BY GROUP BY v.id` is invalid usage off GROUP BY.. [(My)SQL mistakes. Do you use GROUP BY correctly?](https://www.psce.com/en/blog/2012/05/15/mysql-mistakes-do-you-use-group-by-correctly/) – Raymond Nijland Feb 04 '19 at 00:26
  • Follow the [groupwise-maximum] tag. – Rick James Feb 05 '19 at 16:35

0 Answers0