Styjun

Representing power series as a function - what to do with the constant after integration?

Why can't I see bouncing of a switch on an oscilloscope?

What do you call something that goes against the spirit of the law, but is legal when interpreting the law to the letter?

Download, install and reboot computer at night if needed

How can bays and straits be determined in a procedurally generated map?

Suffixes -unt and -ut-

Is the language p and n are natural numbers and there's no prime number in [p,p+n] belongs to NP class?

A Journey Through Space and Time

What are these boxed doors outside store fronts in New York?

"You are your self first supporter", a more proper way to say it

How to get the available space of $HOME as a variable in shell scripting?

Email Account under attack (really) - anything I can do?

Why Is Death Allowed In the Matrix?

declaring a variable twice in IIFE

TGV timetables / schedules?

GPS Rollover on Android Smartphones

I’m planning on buying a laser printer but concerned about the life cycle of toner in the machine

Why are 150k or 200k jobs considered good when there are 300k+ births a month?

Should I join office cleaning event for free?

Why are only specific transaction types accepted into the mempool?

How can the DM most effectively choose 1 out of an odd number of players to be targeted by an attack or effect?

Patience, young "Padovan"

Infinite past with a beginning?

Symplectic equivalent of commuting matrices

mysql Create named table from values in select

Insert into … values ( SELECT … FROM … )Add a column with a default value to an existing table in SQL ServerTable Naming Dilemma: Singular vs. Plural NamesUPDATE multiple tables in MySQL using LEFT JOINHow do I UPDATE from a SELECT in SQL Server?Finding duplicate values in a SQL tableWhat are the options for storing hierarchical data in a relational database?Insert into a MySQL table or update if existsCreate a temporary table in a SELECT statement without a separate CREATE TABLESQL select only rows with max value on a column

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

2

I have a table, System, with a bunch of fields including System.serial.
I have a list of serial numbers that I want to get the status of.

Simple enough:

Select * from System where System.serial in ('s1','s2', 'sn');

However the list of serial numbers also has serials NOT IN the System table.
Obviously they are not in the results.

I want the missing serials to show in the results also but with no data.

The best way I can think of doing this is to make a temporary table with one column, serial, and then left join System on it.

How can I do this without creating a temporary table?

Something like:

Select listOfSerials.serial, System.* 
from (Select ('s1','s2', 'sn') as serial ) as ListOfSerials 
left join System on System.serial = ListOfSerials.serial;

Thanks,
Ryan

mysql sql

share|improve this question

edited Mar 22 at 0:54

GMB

21.7k51028

asked Mar 22 at 0:41

Ryan LeeRyan Lee

5241819

add a comment | 

2

I have a table, System, with a bunch of fields including System.serial.
I have a list of serial numbers that I want to get the status of.

Simple enough:

Select * from System where System.serial in ('s1','s2', 'sn');

However the list of serial numbers also has serials NOT IN the System table.
Obviously they are not in the results.

I want the missing serials to show in the results also but with no data.

The best way I can think of doing this is to make a temporary table with one column, serial, and then left join System on it.

How can I do this without creating a temporary table?

Something like:

Select listOfSerials.serial, System.* 
from (Select ('s1','s2', 'sn') as serial ) as ListOfSerials 
left join System on System.serial = ListOfSerials.serial;

Thanks,
Ryan

mysql sql

share|improve this question

edited Mar 22 at 0:54

GMB

21.7k51028

asked Mar 22 at 0:41

Ryan LeeRyan Lee

5241819

add a comment | 

I have a table, System, with a bunch of fields including System.serial.
I have a list of serial numbers that I want to get the status of.

Simple enough:

Select * from System where System.serial in ('s1','s2', 'sn');

However the list of serial numbers also has serials NOT IN the System table.
Obviously they are not in the results.

I want the missing serials to show in the results also but with no data.

The best way I can think of doing this is to make a temporary table with one column, serial, and then left join System on it.

How can I do this without creating a temporary table?

Something like:

Select listOfSerials.serial, System.* 
from (Select ('s1','s2', 'sn') as serial ) as ListOfSerials 
left join System on System.serial = ListOfSerials.serial;

Thanks,
Ryan

mysql sql

share|improve this question

edited Mar 22 at 0:54

GMB

21.7k51028

asked Mar 22 at 0:41

Ryan LeeRyan Lee

5241819

Select * from System where System.serial in ('s1','s2', 'sn');

Select listOfSerials.serial, System.* 
from (Select ('s1','s2', 'sn') as serial ) as ListOfSerials 
left join System on System.serial = ListOfSerials.serial;

share|improve this question

edited Mar 22 at 0:54

GMB

21.7k51028

asked Mar 22 at 0:41

Ryan LeeRyan Lee

5241819

share|improve this question

edited Mar 22 at 0:54

GMB

21.7k51028

asked Mar 22 at 0:41

Ryan LeeRyan Lee

5241819

edited Mar 22 at 0:54

GMB

21.7k51028

edited Mar 22 at 0:54

GMB

21.7k51028

asked Mar 22 at 0:41

Ryan LeeRyan Lee

5241819

asked Mar 22 at 0:41

Ryan LeeRyan Lee

5241819

2 Answers
2

active

oldest

votes

1

You're on the right track with your solution of creating a virtual table with which to do LEFT JOIN against your real data.

You can create a derived table as a series of UNIONed SELECT statements that select literal values with no table reference.

SELECT listOfSerials.serial, System.* 
FROM (
 SELECT 's1' AS serial
 UNION SELECT 's2'
 UNION SELECT 'sn'
) AS ListOfSerials 
LEFT JOIN System ON System.serial = ListOfSerials.serial;

You only need to define a column alias in the first SELECT in the UNION. The rest are required to use that column alias.

share|improve this answer

answered Mar 22 at 0:51

Bill KarwinBill Karwin

385k64520679

add a comment | 

0

Creating a reference table to store the list of serials is probably your best option. That would allow you to write a query like:

SELECT r.serial reference_serial, s.serial system_serial
FROM reference_table r
LEFT JOIN system_table s ON s.serial = r.serial

With the LEFT JOIN, serials declared in the reference table but unavailable in the system table will have second column set to NULL.

A quick and dirty work around is to use UNIONed subqueries to emulate the reference table:

SELECT r.serial reference_serial, s.serial system_serial
FROM (
 SELECT 'serial1' AS serial
 UNION ALL SELECT 'serial2'
 UNION ALL SELECT 'serial2'
 ...
) r
LEFT JOIN system_table s ON s.serial = r.serial

share|improve this answer

answered Mar 22 at 0:53

GMBGMB

21.7k51028

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55291263%2fmysql-create-named-table-from-values-in-select%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

You're on the right track with your solution of creating a virtual table with which to do LEFT JOIN against your real data.

You can create a derived table as a series of UNIONed SELECT statements that select literal values with no table reference.

SELECT listOfSerials.serial, System.* 
FROM (
 SELECT 's1' AS serial
 UNION SELECT 's2'
 UNION SELECT 'sn'
) AS ListOfSerials 
LEFT JOIN System ON System.serial = ListOfSerials.serial;

You only need to define a column alias in the first SELECT in the UNION. The rest are required to use that column alias.

share|improve this answer

answered Mar 22 at 0:51

Bill KarwinBill Karwin

385k64520679

add a comment | 

1

You're on the right track with your solution of creating a virtual table with which to do LEFT JOIN against your real data.

You can create a derived table as a series of UNIONed SELECT statements that select literal values with no table reference.

SELECT listOfSerials.serial, System.* 
FROM (
 SELECT 's1' AS serial
 UNION SELECT 's2'
 UNION SELECT 'sn'
) AS ListOfSerials 
LEFT JOIN System ON System.serial = ListOfSerials.serial;

You only need to define a column alias in the first SELECT in the UNION. The rest are required to use that column alias.

share|improve this answer

answered Mar 22 at 0:51

Bill KarwinBill Karwin

385k64520679

add a comment | 

You're on the right track with your solution of creating a virtual table with which to do LEFT JOIN against your real data.

You can create a derived table as a series of UNIONed SELECT statements that select literal values with no table reference.

SELECT listOfSerials.serial, System.* 
FROM (
 SELECT 's1' AS serial
 UNION SELECT 's2'
 UNION SELECT 'sn'
) AS ListOfSerials 
LEFT JOIN System ON System.serial = ListOfSerials.serial;

You only need to define a column alias in the first SELECT in the UNION. The rest are required to use that column alias.

share|improve this answer

answered Mar 22 at 0:51

Bill KarwinBill Karwin

385k64520679

SELECT listOfSerials.serial, System.* 
FROM (
 SELECT 's1' AS serial
 UNION SELECT 's2'
 UNION SELECT 'sn'
) AS ListOfSerials 
LEFT JOIN System ON System.serial = ListOfSerials.serial;

share|improve this answer

answered Mar 22 at 0:51

Bill KarwinBill Karwin

385k64520679

answered Mar 22 at 0:51

Bill KarwinBill Karwin

385k64520679

answered Mar 22 at 0:51

Bill KarwinBill Karwin

385k64520679

0

Creating a reference table to store the list of serials is probably your best option. That would allow you to write a query like:

SELECT r.serial reference_serial, s.serial system_serial
FROM reference_table r
LEFT JOIN system_table s ON s.serial = r.serial

With the LEFT JOIN, serials declared in the reference table but unavailable in the system table will have second column set to NULL.

A quick and dirty work around is to use UNIONed subqueries to emulate the reference table:

SELECT r.serial reference_serial, s.serial system_serial
FROM (
 SELECT 'serial1' AS serial
 UNION ALL SELECT 'serial2'
 UNION ALL SELECT 'serial2'
 ...
) r
LEFT JOIN system_table s ON s.serial = r.serial

share|improve this answer

answered Mar 22 at 0:53

GMBGMB

21.7k51028

add a comment | 

0

Creating a reference table to store the list of serials is probably your best option. That would allow you to write a query like:

SELECT r.serial reference_serial, s.serial system_serial
FROM reference_table r
LEFT JOIN system_table s ON s.serial = r.serial

With the LEFT JOIN, serials declared in the reference table but unavailable in the system table will have second column set to NULL.

A quick and dirty work around is to use UNIONed subqueries to emulate the reference table:

SELECT r.serial reference_serial, s.serial system_serial
FROM (
 SELECT 'serial1' AS serial
 UNION ALL SELECT 'serial2'
 UNION ALL SELECT 'serial2'
 ...
) r
LEFT JOIN system_table s ON s.serial = r.serial

share|improve this answer

answered Mar 22 at 0:53

GMBGMB

21.7k51028

add a comment | 

Creating a reference table to store the list of serials is probably your best option. That would allow you to write a query like:

SELECT r.serial reference_serial, s.serial system_serial
FROM reference_table r
LEFT JOIN system_table s ON s.serial = r.serial

With the LEFT JOIN, serials declared in the reference table but unavailable in the system table will have second column set to NULL.

A quick and dirty work around is to use UNIONed subqueries to emulate the reference table:

SELECT r.serial reference_serial, s.serial system_serial
FROM (
 SELECT 'serial1' AS serial
 UNION ALL SELECT 'serial2'
 UNION ALL SELECT 'serial2'
 ...
) r
LEFT JOIN system_table s ON s.serial = r.serial

share|improve this answer

answered Mar 22 at 0:53

GMBGMB

21.7k51028

SELECT r.serial reference_serial, s.serial system_serial
FROM reference_table r
LEFT JOIN system_table s ON s.serial = r.serial

SELECT r.serial reference_serial, s.serial system_serial
FROM (
 SELECT 'serial1' AS serial
 UNION ALL SELECT 'serial2'
 UNION ALL SELECT 'serial2'
 ...
) r
LEFT JOIN system_table s ON s.serial = r.serial

share|improve this answer

answered Mar 22 at 0:53

GMBGMB

21.7k51028

answered Mar 22 at 0:53

GMBGMB

21.7k51028

answered Mar 22 at 0:53

GMBGMB

21.7k51028