CODEWITHSUNDEEP
pythonsql
marc lincoln
marc lincoln

Reputation: 1571

Making a SQL Query in two tables

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.

Upvotes: 0

Views: 729

Answers (6)

Joel Coehoorn
Joel Coehoorn

Reputation: 415620

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

Upvotes: 10

dkretz
dkretz

Reputation: 37645

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.

Upvotes: 0

Gary Kindel
Gary Kindel

Reputation: 17699

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

Upvotes: 0

Mohammed
Mohammed

Reputation: 

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

Upvotes: 0

cbrulak
cbrulak

Reputation: 15629

I think you want to join two tables:

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

Upvotes: 7

Ryan Smith
Ryan Smith

Reputation: 8324

Something like:

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

Is that what you're looking for?

Upvotes: 1

Related Questions