Solved

Sql query in MYSQL

Posted on 2014-03-28
6
214 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

How our DevOps Teams Maximize Uptime

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

Question has a verified solution.

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

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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…

756 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