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
70 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 50

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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 

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 50

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

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

734 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