Solved

Sql query in MYSQL

Posted on 2014-03-28
6
216 Views
Last Modified: 2014-05-26
SELECT `vcproductid`, `vcoptionids` FROM `isc_product_variation_combinations` WHERE `vcproductid` in (3,365) 

Open in new window


vcproductid       vcoptionids       
3                         19,21,22
365                   8915

SELECT `voptionid`, `voname`, `vovalue` FROM `isc_product_variation_options` WHERE `voptionid` in (19,21,22,8915)

Open in new window


voptionid       voname       vovalue
19                   color       black
21                   size         27
22                   size               30
8915             size               44

I am unable to use join since there is a comma in vcoptionsid.

Required Output  is
vcproductid voname vovalue
3                   color  black
3                   size    27,30
365                size    44

If possible , please help on below output too .

vcproductid color   size
3                  black   27,30
365                         44

Thanks
0
Comment
Question by:magento
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39960943
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39960947
so, please start with this:
SELECT pc.`vcproductid`
, pc.`vcoptionids` 
, po.`voname`
, po.`vovalue` 
FROM `isc_product_variation_combinations` pc
JOIN `isc_product_variation_options`  po
   ON FIND_IN_SET( po.`voptionid`, pc.`vcoptionids`) > 0
WHERE pc.`vcproductid` in (3,365) 

Open in new window


you can then apply group_contact on it.
if that works, we can proceed to the last output, which shall contain a CASE expression inside the GROUP_CONCAT function
0
 
LVL 9

Expert Comment

by:selva_kongu
ID: 39960948
Create a sql function to seperate the varchar into Bigint
CREATE FUNCTION dbo.fGetBigIntList(@P_KeyWordList AS Varchar(4000) )  
RETURNS  
@Result  
TABLE(OID BIGINT)  
AS  
BEGIN  
 DECLARE @V_Keyword VARCHAR(20),  
 @CIndex Int  
 IF(@P_KeyWordList is not null )  
 BEGIN  
  SET @CIndex = CHARINDEX(',',@p_KeyWordList)  
  WHILE (@CIndex > 0 )  
  BEGIN  
   SET @V_Keyword = SUBSTRING(@P_KeyWordList,1,@CIndex-1)  
   SET @P_KeyWordList = SUBSTRING(@P_KeyWordList,@CIndex+1,LEN(@P_KeyWordList)-@CIndex)  
   INSERT INTO @Result values (@V_Keyword)  
   SET @CIndex = CHARINDEX(',',@p_KeyWordList)  
  END  
  SET @V_Keyword = @P_KeyWordList  
  INSERT INTO @Result values (@V_Keyword)  
 END  
 RETURN  
END  

Open in new window

 

And Execute this Query
create table #t1 (vcid int, vcoid varchar(44))
insert into #t1
	select 3, '19,21,22'
	union all 
	select 365, '8915'
	
create table #t2 (vcoid int, name varchar(10), value varchar(10))
insert into #t2 
select 19,'color','black'
union all
select 21,'size','27' union all
select 22,'size','30' union all
select 8915,'size','44'


SELECT t1.vcid,
       Name =REPLACE( (SELECT t2.value AS [data()]
           FROM #t2 t2
          WHERE t2.vcoid in (select oid from dbo.fGetBigIntList(t1.vcoid))
            FOR XML PATH('')
            ), ' ', ',')
      FROM #t1 t1

Open in new window

The output will be

vcid      Name
3      black,27,30
365      44
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39960957
selva_kongu, your code shall work nicely in MS SQL Server, but not in MySQL ...
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39962466
Guy Hengel is correct. You can try like this.
SELECT pc.`vcproductid`
, po.`voname`
, GROUP_CONCAT(po.`vovalue`) vovalue
FROM `isc_product_variation_combinations` pc
JOIN `isc_product_variation_options`  po
   ON FIND_IN_SET( po.`voptionid`, pc.`vcoptionids`) > 0
WHERE pc.`vcproductid` in (3,365)
GROUP BY pc.`vcproductid`
, po.`voname`;

SELECT vcproductid,
MAX(CASE voname WHEN 'color' THEN vovalue END) color,
MAX(CASE voname WHEN 'size' THEN vovalue END) size
FROM (
SELECT pc.`vcproductid`
, po.`voname`
, GROUP_CONCAT(po.`vovalue`) vovalue 
FROM `isc_product_variation_combinations` pc
JOIN `isc_product_variation_options`  po
   ON FIND_IN_SET( po.`voptionid`, pc.`vcoptionids`) > 0
WHERE pc.`vcproductid` in (3,365)
GROUP BY pc.`vcproductid`
, po.`voname`) t1
GROUP BY vcproductid; 

Open in new window

http://sqlfiddle.com/#!2/5274b/10
0
 
LVL 5

Author Comment

by:magento
ID: 39974033
Sharath,

I tried ur example , works fine .

Thanks
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question