CODEWITHSUNDEEP
sqlsql-server
Saurav Singh
Saurav Singh

Reputation: 17

Use empty table in SQL query

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:

  1. 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.

  2. Later I want to insert new row of Lot_No and comment in that empty table. How can I do this?

Upvotes: 0

Views: 1164

Answers (2)

user631589
user631589

Reputation: 43

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

Upvotes: -1

John Cappelletti
John Cappelletti

Reputation: 81970

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')

Upvotes: 2

Related Questions