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.
-
What? What does "joined permanently" mean? Are you asking if a database query changes the database?S.Lott– S.Lott2008-12-31 21:45:48 +00:00Commented Dec 31, 2008 at 21:45
Add a comment
|
6 Answers
SELECT t1.products
FROM [Table1] t1
INNER JOIN [Table2] t2 ON t2.barcode = t1.barcode
Comments
I think you want to join two tables:
Comments
Something like:
SELECT * FROM table1 WHERE barcode IN (SELECT barcode FROM table2)
Is that what you're looking for?
2 Comments
Joel Coehoorn
This will work, but all else being equal (no optimizer involvement) the INNER JOIN method will be much faster.
HLGEM
Agree with Joel, this is not the solution even though it will work. The questioner needs to understand basic joining before going any farther in SQl and this doesn't help towards that end either.
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
Comments
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.
