Making a SQL Query in two tables

Question

I'm wondering, is it possible to make an sql query that does the same function as 'select products where barcode in table1 = barcode in table2'. I am writing this function in a python program. Once that function is called will the table be joined permanently or just while that function is running? thanks. lincoln.

Answer 1

Something like:

SELECT * FROM table1 WHERE barcode IN (SELECT barcode FROM table2)

Is that what you're looking for?

Answer 2

I think you want to join two tables:

http://www.w3schools.com/Sql/sql_join.asp

Answer 3

SELECT t1.products
FROM [Table1] t1
INNER JOIN [Table2] t2 ON t2.barcode = t1.barcode

Answer 4

SELECT table1.*, table2.* FROM table1 left join table2 on table1.barcode = table2.barcode

Answer 5

For those who need to understand joins visually http://www.codinghorror.com/blog/archives/000976.html

Answer 6

Here is an example of inner joining two tables based on a common field in both tables.

SELECT table1.Products FROM table1 INNER JOIN table2 on table1.barcode = table2.barcode WHERE table1.Products is not null

Answer 7

Here's a way to talk yourself through table design in these cases, based on Object Role Modeling. (Yes, I realize this is only indirectly related to the question.)

You have products and barcodes. Products are uniquely identified by Product Code (e.g. 'A2111'; barcodes are uniquely identified by Value (e.g. 1002155061).

A Product has a Barcode. Questions: Can a product have no barcode? Can the same product have multiple barcodes? Can multiple products have the same barcode? (If you have any experience with UPC labels, you know the answer to all these is TRUE.)

So you can make some assertions:

A Product (code) has zero or more Barcode (value).
A Barcode (value) has one or more Product (code). -- assumption: we barcodes don't have independent existence if they aren't/haven't been/won't be related to products).

Which leads directly (via your ORM model) to a schema with two tables:

Product
ProductCode(PK) Description etc

ProductBarcode
ProductCode(FK) BarcodeValue
-- with a two-part natural primary key, ProductCode + BarcodeValue

and you tie them together as described in the other answers.

Similar assertions can be used to determine which fields go into various tables in your design.