Solved

Sql query in MYSQL

Posted on 2014-03-28
6
211 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 142

Expert Comment

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

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 142

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 40

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

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now