Union Select

[Adamhumbug]

Hi All,

I have a select statement which works fine.

WITH consumable as (
            Select * from consumable_price
            group by item_id
        )
        
        SELECT 
                qs.name as sectionName, 
                qi.id as itemId, 
                qi.name as itemName, 
                qi.section_id, 
                qi.GBP,
                qi.USD,
                qi.CAD, 
                cb.charge_by,
                qs.display_order as displayorder,
                cp.id as isConsumable
                from items qi 
                inner join quote_sections qs on qi.section_id = qs.id 
                inner join charge_by cb on qi.charge_by_id = cb.id
                left join consumable cp on qi.id = cp.item_id
                
                order by qs.display_order, qi.name

I have added a new table where custom items are held and have a query that selects them just fine

SELECT 
qs.name
, ci.id
, ci.name
, ci.section_id
, ci.price as GBP
, ci.price as USD
, ci.price as CAD
, ci.charge_by
, qs.display_order
, null as isConsumable 
from custom_item ci 
inner join quote_sections qs on ci.section_id = qs.id

I am trying to union select the second set of data with the first set but having a real hard time as i keep getting the error on the WITH at the start.

Can anyone point me in the right direction - i can provide table structures if required.

Thanks in advance

[kicken]

What is the point of your WITH component?  A select * query with group by doesn't really make any sense.  If you're going to be grouping rows, your query should contain some aggregate function.

Seems like you should be able to remove the WITH component entirely and join consumable_price directly.

 

[Adamhumbug]

If i do a normal join on this table i end up with multiple rows for the items that i select from the consumable price table.  I use this table for price breakpoints so each item is in there several times.  It looks like this

[Barand]

Give us some context - that tells us nothing.

What is the name of that table and what is the query giving the problem?

EDIT: Note - you need a join on item_id AND  qty BETWEEN min AND max

[Adamhumbug]

So all i am trying to do here is populate a dropdown with all of the items that appear in the items table.  I also include items that are in the consumable_price table but as there are several rows per item, i have used the WITH.  I am now letting the user add custom items, they are stored in a different table as they have different properties such as only being available in that "job".  The code that i posted above shows how i select them in the same layout as the initial select that works.

 

So the layout of both of the queries is the same and they return the same columns.

CREATE TABLE `custom_item` (
  `id` int(11) NOT NULL,
  `job_id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `section_id` int(11) NOT NULL,
  `price` float NOT NULL,
  `charge_by` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;

INSERT INTO `custom_item` (`id`, `job_id`, `name`, `section_id`, `price`, `charge_by`) VALUES
(1, 106, 'name', 3, 100, 0),
(2, 22, 'NAME', 2, 9999, 2);


ALTER TABLE `custom_item`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `custom_item`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
COMMIT;

In a nut shell i just need to also show the items from the custom_items table in the select box that i populate with the first piece of code that has the WITH.

This select doesnt care about the different price breaks, it just wants to grab the items and group them by the display_order.

 

The select looks like this currently.

 

Edited October 20, 2023 by Adamhumbug

[kicken]

18 minutes ago, Adamhumbug said:

This select doesnt care about the different price breaks, it just wants to grab the items

I don't understand why you're involving this consumable_price table if you don't care about the information within it.  Since you're joining it to your items table, then presumably all the item names are in the items table and if you only want their names, then you can just select from that table by itself.

SELECT 
	qs.name as sectionName, 
	qi.id as itemId, 
	qi.name as itemName,
	qs.display_order as display_order
from items qi 
inner join quote_sections qs on qi.section_id = qs.id 
union all
select
	qs.name,
	ci.id,
	ci.name,
	qs.display_order
from custom_item ci
inner join quote_sections on qs on qs.id = ci.section_id
order by display_order, itemName

 

[Adamhumbug]

i do care about what is in the consumable table, it has items that should be in the list - it just doesnt have the same format as the normal items table.  The consumables table has several rows per item as it has price breaks.

 

Sorry if that wasnt too clear.

[kicken]

4 hours ago, Adamhumbug said:

i do care about what is in the consumable table, it has items that should be in the list

You are joining it to your items table though, implying that any items in the consumable table must also exist in the items table.  You also say you only need the item names for your list, but your screenshot of the consumable table shows it doesn't store any name information. So what information do you need out of that table?  Do you just need to know if a row exists in the table for the isConsumable flag?

If you only one row from your consumables table, you need to define the criteria that will choose that row.  Just throwing in a "group by" clause is not a correct solution.  If you just need to know if a row exists, you'd get a count() of the relevant rows and check that it's > 0.

[Adamhumbug]

Hi Kicken,

 

I have tied myself up here.

 

The reason for the consumable table here is to set the isConsumable flag and that is the only reason, so i could look to do your greater than 0 method.

The method i used was a hope to just get one of each item id from the consumable table.

Sql is not my strong suit so this may take some time for me to figure out.

[Barand]

LEFT JOIN (
          SELECT item_id, COUNT(*) as tot
          FROM consumables 
          GROUP BY item_id
          ) con USING (item_id)

If you use a subquery like the above, instead of joining directly to the table, it will eliminate the duplicate rows.

[Adamhumbug]

I have rewritten like this

select 
i.name as itemName,
qs.name as sectionName,
i.id as itemId,
i.GBP,
i.USD,
i.CAD,
cb.charge_by
from items i
inner join quote_sections qs on i.section_id=qs.id
inner join charge_by cb on i.charge_by_id = cb.id
union 
select 
ci.name as itemName,
qs.name as sectionName,
concat("CI", ci.id) as itemId,
ci.price as GBP,
ci.price as USD,
ci.price as CAD,
cb.charge_by
from custom_item ci
inner join quote_sections qs on ci.section_id=qs.id
inner join charge_by cb on ci.charge_by = cb.id

but how do i implement your count check into this?

[Barand]

Do you want to show

• all items with indication that it is in consumables, or
• only items where there is a consumables record, or
• only items with no consumables records

(I have to ask as you have not given an example where you used the table)

[Adamhumbug]

I think this has done what i need

select 
	i.name as itemName,
	qs.name as sectionName,
	i.id as itemId,
	i.GBP,
	i.USD,
	i.CAD,
	cb.charge_by,
    if(EXISTS(Select item_id from consumable_price where item_id = i.id),1,0) as icConsumable
from items i
inner join quote_sections qs on i.section_id=qs.id
inner join charge_by cb on i.charge_by_id = cb.id

union 
select 
	ci.name as itemName,
	qs.name as sectionName,
	concat("CI", ci.id) as itemId,
	ci.price as GBP,
	ci.price as USD,
	ci.price as CAD,
	cb.charge_by,
    0 as isConsumable
from custom_item ci
inner join quote_sections qs on ci.section_id=qs.id
inner join charge_by cb on ci.charge_by = cb.id

 

[Barand]

That will work if slow queries are your thing

[Adamhumbug]

They are not! And in answer to your questions, the first one.

 

All items with an indication of whether they are consumable.

[Barand]

perhaps

select 
	i.name as itemName,
	qs.name as sectionName,
	i.id as itemId,
	i.GBP,
	i.USD,
	i.CAD,
	cb.charge_by,
    COUNT(cp.item_id) > 0 as icConsumable
from items i
    inner join quote_sections qs on i.section_id=qs.id
    inner join charge_by cb on i.charge_by_id = cb.id
    left join consumable_price cp ON i.id = cp.item_id
group by i.id
union 
select 
	ci.name as itemName,
	qs.name as sectionName,
	concat("CI", ci.id) as itemId,
	ci.price as GBP,
	ci.price as USD,
	ci.price as CAD,
	cb.charge_by,
    0 as isConsumable
from custom_item ci
inner join quote_sections qs on ci.section_id=qs.id
inner join charge_by cb on ci.charge_by = cb.id

 

[Adamhumbug]

Weirdly i have another issue.

This works:

	$sql =
		"SELECT 
		qs.name as sectionName,
		i.name as itemName,
		i.id as itemId,
		i.GBP,
		i.USD,
		i.CAD,
		cb.charge_by,
		qs.display_order,
		COUNT(cp.item_id) > 0 as isConsumable
	from items i
		inner join quote_sections qs on i.section_id=qs.id
		inner join charge_by cb on i.charge_by_id = cb.id
		left join consumable_price cp ON i.id = cp.item_id
	group by i.id
	union
	SELECT 
		qs.name as sectionName,
		ci.name as itemName,
		concat('CI', ci.id) as itemId,
		ci.price as GBP,
		ci.price as USD,
		ci.price as CAD,
		cb.charge_by,
		qs.display_order,
		0 as isConsumable
	from custom_item ci
	inner join quote_sections qs on ci.section_id=qs.id
	inner join charge_by cb on ci.charge_by = cb.id
	where ci.job_id = 106
	order by display_order, itemName";

	
	$stmt = $pdo->query($sql)->fetchAll(PDO::FETCH_GROUP);

and this does not

	$jobId = 106;

	$sql =
		"SELECT 
		qs.name as sectionName,
		i.name as itemName,
		i.id as itemId,
		i.GBP,
		i.USD,
		i.CAD,
		cb.charge_by,
		qs.display_order,
		COUNT(cp.item_id) > 0 as isConsumable
	from items i
		inner join quote_sections qs on i.section_id=qs.id
		inner join charge_by cb on i.charge_by_id = cb.id
		left join consumable_price cp ON i.id = cp.item_id
	group by i.id
	union
	SELECT 
		qs.name as sectionName,
		ci.name as itemName,
		concat('CI', ci.id) as itemId,
		ci.price as GBP,
		ci.price as USD,
		ci.price as CAD,
		cb.charge_by,
		qs.display_order,
		0 as isConsumable
	from custom_item ci
	inner join quote_sections qs on ci.section_id=qs.id
	inner join charge_by cb on ci.charge_by = cb.id
	where ci.job_id = :jobId
	order by display_order, itemName";

			
				
	$stmt = $pdo->prepare($sql);
	$stmt -> execute([":jobId"=> $jobId]);
	$stmt ->fetchAll(PDO::FETCH_GROUP);

is there something simple i am doing wrong here.

 

Neither has errors but the second does not populate the select box and the first does.

Edited October 21, 2023 by Adamhumbug

[Adamhumbug]

The only thing i have changed is how the statement gets its parameter.

[Barand]

FetchAll() returns an array but in the second version you don't store it anywhere.

[Adamhumbug]

2 minutes ago, Barand said:

FetchAll() returns an array but in the second version you don't store it anywhere.

Ahh