[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL Join vs. Left Join, etc

Posted on 2011-10-06
9
Medium Priority
?
272 Views
Last Modified: 2012-05-12
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!
0
Comment
Question by:erzoolander
  • 3
  • 3
  • 3
9 Comments
 
LVL 5

Accepted Solution

by:
bitref earned 1000 total points
ID: 36924091
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
 
LVL 22

Assisted Solution

by:pivar
pivar earned 1000 total points
ID: 36924099
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
 
LVL 5

Expert Comment

by:bitref
ID: 36924101
Select *
From Table1 t1
          Left Outr Join  Table2 t2
                   On t1.ProductID = t2.ProductID
Where t1.TAG = "x"

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 2

Author Comment

by:erzoolander
ID: 36927287
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
 
LVL 22

Expert Comment

by:pivar
ID: 36927362
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
 
LVL 2

Author Comment

by:erzoolander
ID: 36927377
Nope, unfortunately!
0
 
LVL 22

Expert Comment

by:pivar
ID: 36927402
I assume you mean you don't get any matches?

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

Expert Comment

by:bitref
ID: 36927852
Excuse me. U should close this question and open a new question for your second query.
0
 
LVL 2

Author Closing Comment

by:erzoolander
ID: 36927888
True - I thought about that but got all excited and asked right away :)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month20 days, 10 hours left to enroll

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question