How to check if entry exists in another table

zzz

I have a list of items in table1. I also have table2 with similar items. How can I check to see if any of the items in table1 already exist in table2?

I am afraid that if I run a quiery from each line of table1 to check table that may tax my server too much. Is there a better way?

chyan

you can used this;

select table1.field1 from table1
left join table2 on table1.field1=table2.field2

zzz

OK, I think I'm half way though but stuck gain.
I was able to join like this:

SELECT * FROM t1, t2 WHERE t1.column1=t2.column1;

Now my results are displayd through foreach loop

Now, I'd like to display some sign if I detect an entry in table2

Currently I just get two lines of output, i.e. results from both tables. What I'd like to have is the output from table1 and some sort of indicator that similar record exists in table2.

What would it look like? I guess I'm overcomplicating my syntaxt here... just stuck.

czambran

What you need is an outer join insted of the inner join you are currently using. I am at work so the following code is untested( I am hoping I remember correctly which is unlikely 😉 )

select t1.field1, t1.field2, t2.field1 from t1 left outer join on t1.field1=t2.field1.

Now this will return all records of tha table t1(left) and all the records of t2(right) that had a matching record on t1(left).
If there are no matching values in from the right table, the join still retrieves all the records from the left table. Any columns from the right table that are unmatched are left NULL.

Hope it helps

chyan

OK, here some explanation about table join

Join
The join operation is used to retrieve data from two tables that are related to each other through a common field. Conceptually, the result of the join is a new table whose rows consist of some or all the fields from the first table followed by some or all the fields from the second table; the expression in the ON clause in a JOIN command determines which rows from the second table will match a given row from the first table. For example, the following query returns information about all titles, including the name of their publisher. I already showed that you can complete this task using a SELECT command with multiple tables, but an INNER JOIN command is often better:

SELECT Titles.Title, Titles.[Year Published], Publishers.Name FROM Titles
INNER JOIN Publishers ON Titles.PubID = Publishers.PubID

This is an important detail: the previous statement retrieves only those titles for which there is a publisher, that is, those whose PubID field isn't Null. While the INNER JOIN (also known as equi-join) is the most common form of join operation, SQL also supports two other types of joins, the LEFT JOIN and the RIGHT JOIN operations. The LEFT JOIN operation retrieves all the records in the first table, regardless of whether there's a corresponding record in the other table. For example, the following command retrieves all the titles, even if their publisher isn't known:

SELECT Titles.Title, Titles.[Year Published], Publishers.Name FROM Titles
LEFT JOIN Publishers ON Titles.PubID = Publishers.PubID

The RIGHT JOIN operation retrieves all the records in the second table, even if there isn't a related record in the first table. The following statement selects all the publishers, whether or not they have published any titles:

SELECT Titles.Title, Titles.[Year Published], Publishers.Name FROM Titles
RIGHT JOIN Publishers ON Titles.PubID = Publishers.PubID