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!
LVL 2
erzoolanderAsked:
Who is Participating?
 
bitrefCommented:
You should use Left Outer Join to ensure having records for all records from the first table as follows:

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

Open in new window

0
 
pivarCommented:
Hi,

Are you looking for?

SELECT *
FROM Table1 t1
LEFT JOIN Table2 t2 ON t2.ProductId = t1.ProductId
WHERE t1.Tag = 'X'


Use LEFT JOIN if you like to show all tags even if there is no products. Use INNER JOIN to only show tags with products.
/peter
0
 
bitrefCommented:
Select *
From Table1 t1
          Left Outr Join  Table2 t2
                   On t1.ProductID = t2.ProductID
Where t1.TAG = "x"

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
erzoolanderAuthor Commented:
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?
0
 
pivarCommented:
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'
0
 
erzoolanderAuthor Commented:
Nope, unfortunately!
0
 
pivarCommented:
I assume you mean you don't get any matches?

Can you give an example how  product_alias.src actually looks like?
0
 
bitrefCommented:
Excuse me. U should close this question and open a new question for your second query.
0
 
erzoolanderAuthor Commented:
True - I thought about that but got all excited and asked right away :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.