Subscribe to this thread
Home - General / All posts - as alias in SQL
lionel

457 post(s)
#11-Mar-18 11:59

HI

I have a query with no alias AS

SQL9

SELECT * FROM Students 

LEFT OUTER JOIN Advisors 

   ON Students.[Advisor_ID] = Advisors.[Advisor_ID] 

   WHERE Advisors.[Advisor_ID]  IS NULL ;

i want to replace table name Students by S and Advisors by A !

I try this with X or [X] but don't work ( SQL server syntax)

SQL9

SELECT * FROM Students AS S 

LEFT OUTER JOIN Advisors AS A 

   ON S.[Advisor_ID] = A.[Advisor_ID] 

   WHERE A.[Advisor_ID]  IS NULL ;

any clue ?

All table has implicit row that contain null that appear if nothing match = is null !!!!

Attachments:
loop9_v3.png
loop_v9.mxb


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

lionel

457 post(s)
#11-Mar-18 12:28

update SQL join image with more explicit color ( null fill empty ) for those who think circle representation is not accurate

Attachments:
color_template.png
loop9_v3.png


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

lionel

457 post(s)
#11-Mar-18 12:54

.

Attachments:
loop9_v4.png


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

tjhb

8,215 post(s)
online
#11-Mar-18 19:48

Lionel,

Can you expand upon "don't work"?

What happens when you run the first query, without aliases?

What happens when you run the second query, with aliases?

lionel

457 post(s)
#11-Mar-18 20:14

Hi

For the first SQL result appear, but for the second SQL nothing appear ( no row ) .

For me when launch a SQL query the return value is table view ( Results tab) if all is ok and if error occur then don't use REsults tab but lob tab . Here it is a mix no error so don't use log tab but no result even use Results tab . test on 9.0.165.0 ( 31 jan 2018) 64 bits

regard's

Attachments:
manifold_SQL9_add_as.png


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

tjhb

8,215 post(s)
online
#11-Mar-18 20:41

Well, in one case you are filtering values in the left right table on IS NOT NULL, in the other case on IS NULL.

Is Advisor_ID NULL for any records in the right table?

lionel

457 post(s)
#11-Mar-18 20:51

thank's i don't care that i change "is null" by "is not null" and that i switch advisor by S.

with rest ideas become clearer


join image "Because my dad promised me" interstellar from Manifold: Time by Stephen Baxter. power Math destruction

adamw

8,061 post(s)
#13-Mar-18 07:45

In the second query, you wrote the join condition as:

ON [S].[Advisor_ID] = [S].[Advisor_ID]

while you wanted:

ON [S].[Advisor_ID] = [A].[Advisor_ID]

If I correct the join condition, the query returns two records (and is analogous to your second query in the first post).

Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.