iwato Posted November 16, 2017 Author Share Posted November 16, 2017 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 Link to comment Share on other sites More sharing options...
iwato Posted November 16, 2017 Author Share Posted November 16, 2017 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 16, 2017 Share Posted November 16, 2017 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. 1 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 16, 2017 Share Posted November 16, 2017 I misread what you wrote, I thought you wrote "there is no such thing as scope". Yes, SQL queries have a scope. 1 Link to comment Share on other sites More sharing options...
iwato Posted November 17, 2017 Author Share Posted November 17, 2017 (edited) 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 Link to comment Share on other sites More sharing options...
iwato Posted November 17, 2017 Author Share Posted November 17, 2017 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 Link to comment Share on other sites More sharing options...
justsomeguy Posted November 17, 2017 Share Posted November 17, 2017 Yeah, I wouldn't think that the aliases would expand over the union because each of those select queries stands alone. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now