I have this code below:
SET @sql = CONCAT('SELECT s.name AS size,',
(SELECT REPLACE(
GROUP_CONCAT(COLUMN_NAME),
'ps.product_id, ps.status, ps.created_by, ps.created_at, ps.updated_at, ps.deleted_at,', ''
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'product_sku'
AND TABLE_SCHEMA = 'swafap'), ' FROM product_sku AS ps
INNER JOIN size_code AS s ON s.id = ps.size_code_id
WHERE ps.product_id = 4741');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
I have a lot of columns I need to select from product_sku. I looked at the popular answer post to figure this out. I selected other columns and stuff with no problem, but as soon as I do a join, it says id is ambiguous which makes sense because id exists in both tables..
However, I'm not really sure on how to fix this properly since I'm still trying to wrap my head around what it's doing right here.
In product_sku, is has a column called 'size_code_id' which I only need to reference the name of that size code in the size_code table.
Any help would be greatly appreciated. I hope I can translate this into Laravel using fluent/eloquent =) Because I really like this solution. My original query looks like this:
SELECT ps.manufacturer_sku, ps.sku, ps.units, s.name AS size, ps.smis, ps.purchase_cost, ps.branch_cost,
ps.annual_sales_unit, ps.annual_sales_dollar, ps.pipeline_unit, ps.pipeline_dollar, ps.upc_barcode, ps.lvl_1_scc_barcode,
ps.mast_carton_scc_barcode, ps.pallet_scc_barcode, ps.prod_manufacturer_comp, ps.part_no_comp, ps.wd_price_comp,
ps.jobber_price_comp, ps.refinisher_price_comp, ps.retail_price_comp, ps.initial_lead_time, ps.reorder_lead_time,
ps.min_quant, ps.manufacturer_location, ps.country_code, ps.tariff_code, ps.weight, ps.lngth, ps.height, ps.width,
ps.shipping_desc, ps.lvl_1_quant, ps.master_carton_quant, ps.pallet_quant, ps.wd_price_rec, ps.jobber_price_rec,
ps.refinisher_price_rec, ps.retail_price_rec
FROM product_sku AS ps
INNER JOIN size_code AS s ON s.id = ps.size_code_id
WHERE ps.product_id = 4741;
Very ugly..
