MySQL union query with counting. Easy Points.

Hi, this query seems complex, but is not really. For each Purchase Order (PO), I'm looking at the line items. I want to know if there are all four product profiles in the line items for a given PO:

type a
type b
type c or d
type e or f

The query is attached. I am using mysql, and the error returned is unknown column os.poIndex.
Thanks for the help!
select `db`.poIndex from orders as os where (
Select count(uos.product) from (

(Select product from `db`.po_lines where poIndex = os.poIndex AND product like 'a%' Limit 1) 
Union 
(Select product from `db`.po_lines where poIndex = os.poIndex AND product like 'b%' Limit 1) 
Union
(Select product from `db`.po_lines where poIndex = os.poIndex AND (product like 'c%' or product like 'd%') Limit 1)
Union 
(Select product from `db`.po_lines where poIndex = os.poIndex AND product like ('e%'  or product like 'f%') Limit 1)
) as uos) = 4;

Open in new window

airvectorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
nasirbestConnect With a Mentor Commented:
here is a solution for you.  I have used temp values like "a b cd ef" just to satisfy COUNT DISTINCT
SELECT o.poIndex, COUNT(DISTINCT 
                      IF(l.product LIKE 'a%', 'a', NULL), 
                      IF(l.product LIKE 'b%', 'b', NULL), 
                      IF(l.product LIKE 'c%' OR l.product LIKE 'd%', 'cd', NULL),
                      IF(l.product LIKE 'e%' OR l.product LIKE 'f%', 'ef', NULL)
                ) AS product_groups
FROM orders o LEFT JION po_lines l ON o.poIndex = l.poIndex
GROUP BY o.poIndex
HAVING product_groups >= 4;

Open in new window

0
 
airvectorAuthor Commented:
Your solution was very close. What actually worked was

SELECT o.poIndex, COUNT(DISTINCT IF(l.product LIKE 'a%', 'a', NULL)) as A,
                  COUNT(DISTINCT IF(l.product LIKE 'b%', 'a', NULL)) as B,
                  COUNT(DISTINCT IF(l.product LIKE 'c%' OR l.product LIKE 'd%', 'cd', NULL)) as CD,
                  COUNT(DISTINCT IF(l.product LIKE 'e%' OR l.product LIKE 'f%', 'ef', NULL)) as EF

FROM `megalfadb_r0`.orders o LEFT JOIN `megalfadb_r0`.po_lines l ON o.poIndex = l.poIndex
GROUP BY o.poIndex
Having A+B+CD+EF >=4;
0
 
airvectorAuthor Commented:
The query in a code box, for clarity. Thanks nasirbest!
SELECT o.poIndex, COUNT(DISTINCT IF(l.product LIKE 'a%', 'a', NULL)) as A,
COUNT(DISTINCT IF(l.product LIKE 'b%', 'a', NULL)) as B,
COUNT(DISTINCT IF(l.product LIKE 'c%' OR l.product LIKE 'd%', 'cd', NULL)) as CD,
COUNT(DISTINCT IF(l.product LIKE 'e%' OR l.product LIKE 'f%', 'ef', NULL)) as EF

FROM `megalfadb_r0`.orders o LEFT JOIN `megalfadb_r0`.po_lines l ON o.poIndex = l.poIndex
GROUP BY o.poIndex
Having A+B+CD+EF >=4;

Open in new window

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
airvectorAuthor Commented:
Something a little similar, nasirbest. Just a question of nesting the ifs:
SELECT o.poIndex, COUNT(DISTINCT
                      IF(l.product LIKE 'a%', 'a',
                      IF(l.product LIKE 'b%', 'b',
                      IF(l.product LIKE 'c%' or l.product LIKE 'd%', 'cd',
                      IF(l.product LIKE 'e%' or l.product LIKE 'f%', 'ef', NULL ))))

                ) AS product_groups
FROM `db`.orders o LEFT JOIN `db`.po_lines l ON o.poIndex = l.poIndex
GROUP BY o.poIndex
HAVING product_groups >= 4;

Open in new window

0
 
nasirbestCommented:
my first idea was this. But I wrote the previous query to keep things simple. it should work!
0
 
airvectorAuthor Commented:
To be precise, your solution worked, only it didn't give me accurate values.

And the reason for that is because of how count distinct works. For a test, I tried this and it yielded 1, just as your solution was giving me either 0 (when all values were NULL) or 1 (for any other combination, including all strings):
SELECT o.poIndex,
                  Count(Distinct '1','2','3','4') as test
FROM `db`.orders o LEFT JOIN `db`.po_lines l ON o.poIndex = l.poIndex
group by o.poIndex

Open in new window

0
All Courses

From novice to tech pro — start learning today.