Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 191
  • Last Modified:

Join tables - extra rows

I've got 2 tables:
tblSystem (systemId, System)
tblClient (clientID, client, systemID)
There are joined on the systemID

My result now:
SystemX - ClientY
SystemX - ClientZ
SystemZ - ClientV

How can I transform it (add extra rows), so I get something like this:
ALL
SystemX - ALL
SystemX - ClientY
SystemX - ClientZ
SystemZ - ALL
SystemZ - ClientV
0
Dhaest
Asked:
Dhaest
  • 3
  • 3
  • 2
1 Solution
 
rafranciscoCommented:
Try this:

SELECT 'ALL', ''
UNION ALL
SELECT A.System, B.Client
FROM tblSystem A INNER JOIN tblClient B
    ON A.SystemID = B.SystemID
UNION ALL
SELECT A.System, B.Client
FROM tblSystem A, (SELECT 'ALL' AS Client) B
0
 
DhaestAuthor Commented:
Ok, that works almost like I wanted.

Is there a possibility to put the "ALL" always in front (like the example I gave)
But there i a system known as A by us, so it falls alphabetically before ALL, but I want all first.
0
 
rafranciscoCommented:
Try this:

SELECT 'ALL', ''
UNION ALL
SELECT A.System, B.Client
FROM tblSystem A,(SELECT SystemID, ClientID, Client FROM tblClient
                             UNION ALL
                             SELECT 'ALL' AS SystemID, 'ALL' AS ClientID, 'ALL' AS Client) B
WHERE EXISTS (SELECT 'X' FROM tblSystem X INNER JOIN tblClient Y
                                             ON X.SystemID = Y.SystemID
                         WHERE A.SystemID = X.SystemID AND
                                     B.ClientID = Y.ClientID) OR
           B.ClientID = 'ALL'
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
Scott PletcherSenior DBACommented:

SELECT sys.System, cli.client
FROM tblSystem sys
INNER JOIN (
    SELECT clientID, client, systemID
    FROM tblClient
    UNION ALL
    SELECT 0, 'ALL', 0
) AS cli ON cli.systemID = sys.systemID OR client = 'ALL'
ORDER BY sys.System,
    CASE WHEN cli.client = 'ALL' THEN 0 ELSE 1 END,
    cli.client
0
 
DhaestAuthor Commented:
To rafrancisco :l'm getting an error-message: Syntax error converting the varchar value 'ALL' to a column of data type int

To ScottPletcher: I'm missing the first line : "ALL"
0
 
Scott PletcherSenior DBACommented:
>> To ScottPletcher: I'm missing the first line : "ALL" <<

Hmm, interesting, worked perfectly for me.  Perhaps you could post the exact statement you are running.
0
 
rafranciscoCommented:
Try this one:

SELECT 'ALL', ''
UNION ALL
SELECT A.System, B.Client
FROM tblSystem A,(SELECT SystemID, ClientID, Client FROM tblClient
                             UNION ALL
                             SELECT 0 AS SystemID, 0 AS ClientID, 'ALL' AS Client) B
WHERE EXISTS (SELECT 'X' FROM tblSystem X INNER JOIN tblClient Y
                                             ON X.SystemID = Y.SystemID
                         WHERE A.SystemID = X.SystemID AND
                                     B.ClientID = Y.ClientID) OR
           B.Client = 'ALL'
0
 
DhaestAuthor Commented:
>>>> To ScottPletcher: I'm missing the first line : "ALL" <<

>>Hmm, interesting, worked perfectly for me.  Perhaps you could post the exact statement you are running.

I just copied your entire sql...
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now