Use empty table in SQL query

Question

I have created a new SQL table of two column [Lot_no, Comment] which is empty.

|Lot_No|comment|

I have another table of two column [Lot_no, Product_Code] which has values.

|Lot_No|Product_Code|
|1     |2304|
|2     |2307|
|3     |2317|
|4     |2308|

Problem:

I want to create a SQL query where I retrieve [Lot_No|Product_Code|Comment] for some [Lot_No] but since no data is available for comment in first table it should return null in comment retrieved column.
Later I want to insert new row of Lot_No and comment in that empty table. How can I do this?

John Cappelletti · Accepted Answer · 2020-04-23 02:32:04Z

2

A simple LEFT JOIN should do the trick. Perhaps something like this:

Select A.Lot_No
      ,A.Product_Code
      ,B.Comment
 From  YourTable A
 Left Join YourCommentTable B
  on  A.Lot_No = B.Lot_No
 Where A.Lot_No = 3 -- for example

For the Insert

Insert Into YourCommentTable (Lot_No,Comment) values (4,'Some new comment')

answered Apr 23, 2020 at 2:32

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Saurav Singh Over a year ago

Thanks a lot as it is working. Can you please help me with older syntax of left join in the above query as my most query has older syntax of join and it will be confusing if i use ANSI syntax.

Saurav Singh Over a year ago

Can you please help me with older syntax of left join in the above query as my most query has older syntax of join and it will be confusing if i use ANSI syntax

Dale K Over a year ago

@SauravSingh What do you mean by "older syntax"? There is nothing old about a "left join" and as we can't see your queries which you refer to, we cannot advise you on the,.

user631589 · Accepted Answer · 2020-04-23 04:45:22Z

-1

I assume by 'older syntax' you mean the old pre-ansi syntax. Try this:

Select A.Lot_No
      ,A.Product_Code
      ,B.Comment
From YourTable A, YourCommentTable B
Where A.Lot_No = 3 -- for example 
And A.Lot_No *= B.Lot_No

answered Apr 23, 2020 at 4:45

user631589

411 gold badge2 silver badges6 bronze badges

Collectives™ on Stack Overflow

Use empty table in SQL query

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related