CODEWITHSUNDEEP
phpselectmysqli
Photographer Britt
Photographer Britt

Reputation: 115

Mysqli Getting Multiple Values from Multiple Tables

I'm struggling to figure this out, and it's probably pretty simple but I just can't get it to work. Using PHP/MYSQLI I'm trying to get data from two tables, while the first table will have the basis to get everything, the second table may or may not have data that matches to the first table's data, so I would need the second table to return empty values. For example...

First Table 'Cust':

CustomerID    Name             School
------------------------------------------
1623          Bob Smith        BON
1785          Betty Davis      FOOT
1854          John Miller      BECK
1547          Kate Lake        BON

Second Table 'Ybk':

CustomerID      Frame     Type
------------------------------------------
1623            001       CC
1854            012       CC

What I would like to get from these two tables is a bit variable between two things...

1) If I want to select the School from the first table (For example WHERE Cust.School='BON') I would like to get this result back:

CustomerID    Name             School      Frame     Type
---------------------------------------------------------
1623          Bob Smith        BON         001       CC
1547          Kate Lake        BON

2) Or, if I select everything, I get this result back:

CustomerID    Name             School      Frame     Type
---------------------------------------------------------
1623          Bob Smith        BON         001       CC 
1785          Betty Davis      FOOT
1854          John Miller      BECK        012       CC
1547          Kate Lake        BON

Right now, when I try some different versions of the SELECT statement I only get back results that are in both tables instead all of the Cust fields returning with the Ybk ones if they exist as well. Help! Thank you!

Upvotes: 1

Views: 709

Answers (1)

user1864610
user1864610

Reputation:

This is a fairly simple LEFT JOIN:

SELECT * FROM Cust LEFT JOIN YbK using (CustID) where School='BON';

# CustID, Name,        School, Frame, Type
'1547',   'Kate Lake', 'BON',  NULL,  NULL
'1623',   'Bob Smith', 'BON',  '001', 'CC'

and 

SELECT * FROM Cust LEFT JOIN YbK using (CustID);

# CustID, Name,           School, Frame,  Type
'1547',   'Kate Lake',    'BON',  NULL,   NULL
'1623',   'Bob Smith',    'BON',  '001',  'CC'
'1785',   'Betty Davis',  'FOOT', NULL,   NULL
'1854',   'John Miller',  'BECK', '012',  'CC'

LEFT JOIN will return all rows from the first table in the JOIN, and fill with null any absent columns where no matching row is available in the second table. The rows are matched on the columns in the USING clause, which helpfully coalesces the matched columns in the resulting output.

The full syntx is given in the MySQL manual here

Upvotes: 2

Related Questions