How to create table from left join result

Databases
1

I have two tables, table a has two columns , email and course. table b has one column email. I am trying to get a result from table a that will exclude all the similar emails in table b.I used this

SELECT * FROM EnqAll
LEFT OUTER JOIN Unsubscribe
ON EnqAll.EMAIL = Unsubscribe.Email
WHERE Unsubscribe.EMAIL IS null

it returned the result with three columns email, course and email(this last column is null). I tried to create a table with this,but I couldn’t, on further reading I was advised to first create the table in the database and insert, but I cant create a table with similar column name, please how can I go about this, I am using sql-server

2

Don’t use the * in the SELECT clause, instead specify the columns you want to select.

SELECT
     EnqAll.EMAIL
   , EnqAll.COURSE
3

I don’t get what you say here. Of course you can create a third table and use the names for the fields that equal to the names of some other table.

You can’t have two fields with the same name in the same table.

Your query shouldn’t return three columns, since EnqAll only has two.

4

Thanks, It worked, without the last column with null values. is that the standard way

5

because i was using * oit was creating a third column with value for email. so i could not create a table to insert the result(because the result had three column , email, course and email)

6

Sorry, I still don’t see it.

If EnqAll has only two fields, and in your query only view those two fields, then it shouldn’t be a problem. Even if you use *

The way I understand it, the table EnqAll only has two fields: email and course.

SELECT * FROM EnqAll will only show two fields because EnqAll has only two fields.

The third field (unsubscribe.email) will not be in the result because it is only a criteria.

Now, if you wanted the three fields visible, you only need to specificy the table

SELECT EnqAll.* , Unsubscribe.* FROM EnqAll LEFT JOIN Unsuscribe ON  EnqAll.EMAIL = Unsubscribe.Email
WHERE Unsubscribe.EMAIL IS null

That should work too.

Although, the better way to do it would be naming the fields

   SELECT EnqAll. email, EnqAll.course , Unsubscribe.email FROM EnqAll LEFT JOIN Unsuscribe ON  EnqAll.EMAIL = Unsubscribe.Email
    WHERE Unsubscribe.EMAIL IS null

Again, this should work too. To make it prettier to look at, you can use aliases for the fields

   SELECT EnqAll. email as E-mail, EnqAll.course as Course , Unsubscribe.email as Unsubscribed FROM EnqAll LEFT JOIN Unsuscribe ON  EnqAll.EMAIL = Unsubscribe.Email
    WHERE Unsubscribe.EMAIL IS null
7

Thanks. molona.

I also saw another solution, which also works

SELECT EnqAll.* INTO NewTable FROM EnqAll
LEFT OUTER JOIN Unsubscribe
ON EnqAll.EMAIL = Unsubscribe.Email
WHERE Unsubscribe.EMAIL IS null

1 Like
8

[quote=“molona, post:6, topic:101513, full:true”]Although, the better way to do it would be naming the fields

   SELECT EnqAll. email, EnqAll.course , Unsubscribe.email FROM EnqAll LEFT JOIN Unsuscribe ON  EnqAll.EMAIL = Unsubscribe.Email
    WHERE Unsubscribe.EMAIL IS null[/quote]how would this be the "better" way?

the third column will still have a duplicated column name and it will always be NULL

9

But he’s joining two tables so the SELECT * returns the columns from both tables, not only the two from the first table. I gave the solution in my first reply :wink:

10

Better way if he wanted to see the three fields on screen (even if it was just to check that third one was, as desired, null) :wink:

I only added it because in his first post he said that the result showed 3 fields, the last one being null. I don’t understand how that can happen when he was selecting the fields of EnqAll which was supposed to have only two fields

11

Yes, you did. But even if he’s joining two tables, this field was used for criteria only. It should not be in the result screen at all

Especially in SQL Server which is, after all, a sophisticated Access (to put it in a simplistic way)

12

Ahhh that explains a lot :slight_smile:

13

What does it explain?

14

That I expect an SQL query to behave as it should (* means all columns, not just a few because of some obscure logic) but I know that Access does things it’s own way. So if SQL server has that same characteristic it’s no wonder I didn’t understand what you were trying to say in your posts :wink:

15

I obviously didn’t explain myself very well… * does select all the fields of the table used in the FROM. But the second table is only used to add the criteria. And that’s why he needs the join. The second table is not part of the FROM

16

I understand now. But that’s not normal SQL behaviour. Normally all joined tables are part of the FROM

17

Actually, I’m going to agree that they are but I think that the outcome in most databases would be the two fields from EqnAll

18

i go AFK for a while and all hell breaks loose…

the original query had the unadorned “select star” –SELECT * FROM EnqAll LEFT OUTER JOIN Unsubscribe ON EnqAll.EMAIL = Unsubscribe.Email WHERE Unsubscribe.EMAIL IS nullthis means return all columns from all tables in the FROM clause and it works the same way in all databases

come on, folks, the second table ~is~ part of the FROM clause

post #7 is the way to go – a qualified star

19

molona, please… you need to understand the FROM clause a little better

20

Just tired Rudy. I haven’t had any sleep. I guess it is time to go to bed. :slight_smile:
I do apologise for any confusion that I may have caused.

1 Like