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
69 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 49

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 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 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 49

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 49

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Insert parts by customer 12 53
What can the "OPTION" in the GROUP help me in my query? 4 36
sql 2016 data tools breakdown.. 1 28
point in time restore in SQL server 26 45
This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

726 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