?
Solved

Sql query in MYSQL

Posted on 2014-03-28
6
Medium Priority
?
219 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
Technology Partners: 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 2000 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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

770 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