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
67 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
  • 3
  • 3
  • 2
  • +2
13 Comments
 
LVL 47

Expert Comment

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

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 142

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 65

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 47

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 48

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 48

Accepted Solution

by:
PortletPaul earned 500 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 47

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

831 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