SELECT JOIN - A Failed Query

[iwato]

Before i implement the suggested code I have several questions.

Firstly,  I have noticed that the number of columns and their respective definitions must match when combining SELECT statements with the UNION operator.  Does this mean that I must repeat the phrase "p.id, p.usertype, p.username, c.userbio" for each SELECT statement similarly combined.

Secondly, the aliases in the proposed complementary statement are declared after they are used.  This suggests that there is such a thing as scope in their application.  Does the scope of the alias extend beyond the SELECT statement in which it is employed?  Does it, for example, apply to the entire SQL statement?

Thirdly, is there a pecking order in the use of SELECT statements.  Does the first SELECT statement determine what is returned for all subsequent SELECT statement?  Or, does each SELECT statement determine for itself what will be returned.

Finally, although I can understand how designating specific columns would reduce the redundancy in the result set, I cannot understand why it would cause values to appear that have not already appeared with the use of the * wildcard operator. Can you explain this?

Although i can well appreciate the need for the speedy and orderly storage and retrieval of information, my mind is not built like a symmetric associative array, and I am having trouble dealing with the matrix-like nature of SQL and its Chinese-like grammatical syntax.

Roddy

[iwato]

Also, provided that the row data contained within a table column is compatible with the new definition is it possible to redefine a MySQL column without destroying the data?

Roddy

[justsomeguy]

Firstly,  I have noticed that the number of columns and their respective definitions must match when combining SELECT statements with the UNION operator.  Does this mean that I must repeat the phrase "p.id, p.usertype, p.username, c.userbio" for each SELECT statement similarly combined.

Yes, you need the same number and type of columns returned.  You don't need to use the same table aliases necessarily, but it makes sense to use the same column names.

Secondly, the aliases in the proposed complementary statement are declared after they are used.  This suggests that there is such a thing as scope in their application.

That's not a correct assumption, if you remove the table aliases but keep them in the list of columns you'll get an error indicating that it doesn't know what p or c is.

Does the scope of the alias extend beyond the SELECT statement in which it is employed?

In that specific example, I don't think so, I think those aliases only apply to the select query where they are used.  I could be wrong about that though, you might be able to use one in the other select query, but I don't think that would work.

Or, does each SELECT statement determine for itself what will be returned.

Yes.

I cannot understand why it would cause values to appear that have not already appeared with the use of the * wildcard operator.

It won't, it's just being explicit about what you're asking for.  If you wanted each ID from the two tables separately, for example, you should give each one a unique alias instead of returning 2 columns with the same name.

Also, provided that the row data contained within a table column is compatible with the new definition is it possible to redefine a MySQL column without destroying the data?

If you change the data type of a column and there is existing data, MySQL will attempt to convert the data to the new data type.  There are various rules for converting data between types.  This is also called casting.  You can also explicitly convert data during a SQL query, there are functions to do that.  Those are the same functions that MySQL would use if you change the data type of a column.

[justsomeguy]

I misread what you wrote, I thought you wrote "there is no such thing as scope".  Yes, SQL queries have a scope.

[iwato]

ALMOST THERE?

There is an improvement, but i am still not there as the procedure returns NULL for the field userbio.

(SELECT p.id, p.usertype, p.username, c.userbio
	FROM parent_table AS p
	LEFT OUTER JOIN child_table AS c 
		ON c.id = p.id
		WHERE p.id = 10)

UNION 

(SELECT pu.id, pu.usertype, pu.username, cu.userbio
		FROM parent_table AS pu 
		LEFT OUTER JOIN child_table AS cu
			ON cu.id = pu.id
			WHERE pu.id IN (SELECT ref
							FROM ref_table
							WHERE ref_table.id = 10)
)

Output

id	usertype	username	userbio
10	3			david		NULL
4	3			tim			NULL
6	1			liz			NULL

Never mind.  It works.  For some reason the listed data was never entered into the child_table.  Alas,  I am a happy dummy!

Roddy 

Edited November 17, 2017 by iwato

[iwato]

Quote

In that specific example, I don't think so, I think those aliases only apply to the select query where they are used.  I could be wrong about that though, you might be able to use one in the other select query, but I don't think that would work.

It turns out that the aliases must be repeated for each SELECT and JOIN statement, but it makes no difference whether they are the same or different for each SELECT and JOIN statement.

Roddy

[justsomeguy]

Yeah, I wouldn't think that the aliases would expand over the union because each of those select queries stands alone.