February 25, 2002 at 11:53 am
I am trying to figure out a way, using a sql statement, to join two tables where the join
field from one table (table2) is only the first few characters of the field
from the second table (table1).
Also, Table2 may not have a record associated with Table1 (for example, "Tricycle wheel"
does not have a corresponding record in Table2, so "Tricycle wheel" is returned in
the dataset).
Table1:
-------
<product>
Bicycle wheel
Bicycle seat
Car wheel
Car seat
Plane wing
Plane engine
Tricycle wheel
Table2:
-------
<wildcard><description>
BicycleBike parts
CarCar parts
The dataset I want from the query:
---------------------------------
Bicycle
Bicycle
Car
Car
Plane wing
Plane engine
Tricycle wheel
February 25, 2002 at 12:10 pm
select *
from table1 t1
inner join table2 t2
on t2.wildcard + '%' like t1.product
Steve Jones
February 25, 2002 at 12:49 pm
SELECT t2.wildcard
FROM Table_a t1 CROSS JOIN
Table_b t2
WHERE (t1.product LIKE '%' + t2.wildcard + '%')
But I'm not sure how to get the records from table_a where the product and wildcard doesn't match!
February 25, 2002 at 12:56 pm
Be careful about using a cross join in this manner because if a cross join is specified with a where clause like this, it becomes an inner join. If it becomes an inner join, might as well call it that and save on the confusion.
If all records for table1 need to be returned regardless of a match on table2, then do the following (modifying Steve's code):
select *
from table1 t1
left join table2 t2
on t2.wildcard + '%' like t1.product
This is a left outer join. Which means all rows from the table on the left side (table1) of the join will be returned regardless of match. All fields for table2 where a match was not found will be NULL.
If records need to match for a row to be returned, Steve's code with the inner join is what you want.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 25, 2002 at 9:35 pm
select isnull(wildcard,product) from products p left outer join prod_lookup pl
on p.product like pl.wildcard+'%'
Table1 = products
Table2 = prod_lookup
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply