Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

TSQL - I need to loop through a table and run two queries - queries may return zero or many rows

Posted on 2014-12-09
13
Medium Priority
?
73 Views
Last Modified: 2015-01-28
TSQL - I need to loop through a table and run two queries - queries may return zero or many rows

I am trying to paste together a string - a static first part
The second is a lookup into a table that may return none or many rows (one string)
The third is a lookup into another table that may return none or many rows (one string)
The forth part is a static string

static - lookup + lookup - static
but looping though a table of ids.
0
Comment
Question by:ralphp1355
[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
  • 3
  • 3
  • 2
  • +2
13 Comments
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40488825
Can you explain with examples? What do you already have?
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40488828
(1)  Post your code.  The content above is hard to conceptualize without code.
(2)  Spell out your question in clear details, as the title is more of a statement than a question.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40488830
I presume what you need is some left join or some "group_concat" function/code for the lookups ...
waiting for the examples to eventually build some code
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

Author Comment

by:ralphp1355
ID: 40488834
Table of prodution items
Loop through ids of items results will say "Production items ' + result of query 1 which I have to pass the id from the looping table - + results of query 2 - which I have to pass the id from the looping table + "thank you'

If that make sense
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40488836
not enough sense yet.
please provide table structure, related sample data and requested resulting data
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40488842
Nope.  Code please.

Keep in mind that experts here don't work at your company, so the schema of your tables is not going to be very intuitive to us.  For example, my last three gigs where health care, airlines, and agribusiness, so 'Production Items' can mean multiple different things in my experience from your situation.
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40488867
Keep in mind that experts here don't work at your company, so the schema of your tables is not going to be very intuitive to us.
Also we don't have guess powers (at least I don't).
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40489938
Just use a query that outputs the 2 constants and a cross apply using "for xml path" for each of the 2 queries.

(If that answer is too brief or lacking in detail, please consider that your question's detail is approximately the same)
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40490316
Perhaps this will help:

Sample Data:
    CREATE TABLE MysteryTable1
    	([ProductionItem] int)
    ;
    	
    INSERT INTO MysteryTable1
    	([ProductionItem])
    VALUES
    	(1),
    	(2),
    	(3)
    ;
    
    CREATE TABLE MysteryTable2
    	([ProductionItem] int, [MysteryField1] varchar(7))
    ;
    	
    INSERT INTO MysteryTable2
    	([ProductionItem], [MysteryField1])
    VALUES
    	(1, 'aaa'),
    	(1, 'bbb'),
    	(1, 'ccc'),
    	(2, 'aaaa'),
    	(2, 'bbbb'),
    	(2, 'cccc'),
    	(3, 'ababa'),
    	(3, 'bcbcbc'),
    	(3, 'cdcdcdc')
    ;
    
    CREATE TABLE MysteryTable3
    	([ProductionItem] int, [MysteryField2] varchar(9))
    ;
    	
    INSERT INTO MysteryTable3
    	([ProductionItem], [MysteryField2])
    VALUES
    	(1, 'x-aaa'),
    	(1, 'x-bbb'),
    	(1, 'x-ccc'),
    	(2, 'y-aaaa'),
    	(2, 'y-bbbb'),
    	(2, 'y-cccc'),
    	(3, 'z-ababa'),
    	(3, 'z-bcbcbc'),
    	(3, 'z-cdcdcdc')
    ;

Open in new window


Expected Result:
    
    | PRODUCTIONITEM |       MYSTERYCONCAT1 |             MYSTERYCONCAT2 |
    |----------------|----------------------|----------------------------|
    |              1 |          aaa,bbb,ccc |          x-aaa,x-bbb,x-ccc |
    |              2 |       aaaa,bbbb,cccc |       y-aaaa,y-bbbb,y-cccc |
    |              3 | ababa,bcbcbc,cdcdcdc | z-ababa,z-bcbcbc,z-cdcdcdc |

Open in new window


Sample Answer:
    select
          productionItem
        , ca1.*
        , ca2.*
    from MysteryTable1
    cross apply (
                select STUFF(
                              (
                              select ',' + MysteryField1
                              from MysteryTable2
                              where MysteryTable1.productionItem = MysteryTable2.productionItem
                              FOR XML PATH ('')
                              )
                 , 1, 1, '')
                ) as ca1 (MysteryConcat1)
    cross apply (
                select STUFF(
                              (
                              select ',' + MysteryField2
                              from MysteryTable3
                              where MysteryTable1.productionItem = MysteryTable3.productionItem
                              FOR XML PATH ('')
                              )
                 , 1, 1, '')
                ) as ca2 (MysteryConcat2)
    

Open in new window


See this working at: http://sqlfiddle.com/#!3/c9f4b/2
0
 

Author Comment

by:ralphp1355
ID: 40572960
I've requested that this question be deleted for the following reason:

Found solution
0
 
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40572942
Can  you share the solution with us?
0
 

Author Closing Comment

by:ralphp1355
ID: 40573010
This XML Path is exactly what I used.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

670 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