asked on

MySQL Join vs. Left Join, etc

I have two tables that are related.

In one table, there's a list of tags and products they're associated with. (Row 1 = Tag, Row 2 = Product ID)
In the second table, there's the list of products the actual products. (Row 1 = Product ID, Row(s) 2-X other product info)

I know that getting the two together requires a join of some sort.

How would you go phrasing the SQL query to

(From Table 1)

"Select * from TAG where TAG = "x"

(and then display (from table 2))

All product IDs identified from Table 1

?

Thanks!

ASKER CERTIFIED SOLUTION

bitref

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

pivar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bitref

Select *
From Table1 t1
          Left Outr Join  Table2 t2
                   On t1.ProductID = t2.ProductID
Where t1.TAG = "x"

Open in new window

erzoolander

ASKER

Awesome!

One last question.

I'm trying to do a left join on one table... The column is formatted as

product/x

(where x has been defined earlier..)

What would the syntax be to say:

LEFT JOIN product_alias t5 on t5.src = 'product/'.t1.productid

Can I do something like that?

pivar

Try

SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t2.ProductId = t1.ProductId
LEFT JOIN product_alias t5 on t5.src = 'product/'+convert(varchar(11), t1.productid)
WHERE t1.Tag = 'X'

erzoolander

ASKER

Nope, unfortunately!

pivar

I assume you mean you don't get any matches?

Can you give an example how product_alias.src actually looks like?

bitref

Excuse me. U should close this question and open a new question for your second query.

erzoolander

ASKER

True - I thought about that but got all excited and asked right away :)