MySQL FAQs
FAQs Categories
Client Server Commands
Database Structure
Table Types or Storage Engines
Indexes
SQL Statements
Table Joins
Funtions and Operators
Tricky Select Queries
Speed Up Queries
Data Back Up
General Questions
Errors
N.M.P.L. Company www.NearMePayday.Loan || 3-month (90 days)
Powered by MySQL
 
Home / Table Joins / Question No: 18

How to get record from first table joining with second table without any active record between them?

I am trying to select a particular row joining two tables but I cannot get the result I am after. I have "table1" with 1000 rows and "table2" with 12 rows. The relationship between the tables is a column "linkedfield". Table1 has a unique key called "lookup". If I use the code:
SELECT t1.*,  t2.thedescription AS t2desc FROM table1 AS t1, 
table2 AS t2 WHERE (t1.lookup='ZZZZZ');
I get a result of 12 rows (as expected)
SELECT t1.*,  t2.thedescription AS t2desc FROM table1 AS t1, 
table2 AS t2 WHERE (t1.linkfield=t2.linkfield)

I get 1000 rows as expected

SELECT t1.*, t2.thedescription AS t2desc FROM table1 AS t1, 
table2 AS t2 WHERE (t1.lookup='ZZZZZ') AND (t1.linkfield=t2.linkfield);

The above query returns one row as desired if there is an active record between the two tables otherwise returns 0 row if there is not. This is where I am stuck. In the above example query, I would like the 1 row whether there is an "active" link or not. Can anyone help me with the logic?

Answer No: 18

Try this:
SELECT t1.*, t2.thedescription AS t2desc FROM table1 AS t1 
LEFT JOIN table2 AS t2
ON t1.linkfield=t2.linkfield WHERE t1.lookup='ZZZZZ';
Without the WHERE condition this would return all 1000 rows of table1 since "a LEFT JOIN returns all rows of the left of the condition even if there is no right column to match." but t1.lookup='ZZZZZ' constrains this to only the one row of table one with lookup equal to 'ZZZZZ'.

Related MySQL FAQs to the Above FAQ

What-is-difference-between-ON-Clause-and-WHERE-Clause What is difference between ON Clause and WHERE Clause?

How-to-delete-or-update-records-of-multiple-tables-in-one-go How to delete or update records of multiple tables in one go?

About FAQs: Recently Added FAQs

About MySQL FAQs: Site Map | Bookmark Us | Recommend this Site to Your Friend | Contact Us

Useful Links: Wikipedia.org | Oracle.com | w3schools.com | www.php.net | Github.com

© 2021  www.mysqlfaqs.net
All rights reserved.