Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Sql query in MYSQL

Posted on 2014-03-28
6
Medium Priority
?
225 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
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

581 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