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
66 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 46

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
 

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 46

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 46

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Job Dependencies in SQL Server Agent: 1 30
Minus first query 1 36
sql query help 4 44
Query Peformance + mulitple query plans 9 47
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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

932 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

11 Experts available now in Live!

Get 1:1 Help Now