Multiple INNER JOIN from the same table
You should specify different aliases for your tables . you are calling all of them m.
SELECT m1.MetalCode as 'Metal1', m2.MetalCode as 'Metal2',m3.MetalCode as 'Metal3'
FROM Item as k
INNER JOIN Metals AS m1 ON m1.metalID=k.metal1
INNER JOIN Metals AS m2 ON m2.metalID=k.metal2
INNER JOIN Metals AS m3 ON m3.metalID=k.metal3
WHERE k.ItemID=?
MySQL: How do I join same table multiple times?
You need to use multiple LEFT JOINs
:
SELECT
ticket.ticket_id,
a1.attr_val AS attr_val1,
a2.attr_val AS attr_val2,
a3.attr_val AS attr_val3
FROM ticket
LEFT JOIN attr a1 ON ticket.ticket_id=a1.ticket_id AND a1.attr_type=1
LEFT JOIN attr a2 ON ticket.ticket_id=a2.ticket_id AND a2.attr_type=2
LEFT JOIN attr a3 ON ticket.ticket_id=a3.ticket_id AND a3.attr_type=3
Here is an example: SQL Fiddle.
INNER JOIN same table
I think the problem is in your JOIN
condition.
SELECT user.user_fname,
user.user_lname,
parent.user_fname,
parent.user_lname
FROM users AS user
JOIN users AS parent
ON parent.user_id = user.user_parent_id
WHERE user.user_id = $_GET[id]
Edit:
You should probably use LEFT JOIN
if there are users with no parents.
Multiple join on same table
Try this;)
select r.phone, a.name as admin, re.name reseller, s.name as superadmin
from reseller_did r
join account a on a.id = r.admin_id
join account s on s.id = r.superadmin_id
join account re on re.id = r.reseller_id
SqlFiddle Result
Or
SELECT r.phone,
MAX(CASE WHEN a.id = r.admin_id THEN a.name END) as admin,
MAX(CASE WHEN a.id = r.reseller_id THEN a.name END) as reseller,
MAX(CASE WHEN a.id = r.superadmin_id THEN a.name END) as superadmin
FROM reseller_did r
INNER JOIN account a
ON a.id IN (r.admin_id, r.superadmin_id, r.reseller_id)
GROUP BY r.phone
This sql should thank to @sagi in this question Get multiple values from another table by different relations.
SqlFiddle Result
Inner Joining the same table multiple times
You need to use table aliases. You have mentioned the same table more than once in the from
clause. The query is something like this:
SELECT b.BlankTypeCode, b.BlankCode, pa1.Amount, pa1.Type, p1.PurchaseDate, pa2.DatePaid
FROM Blank b
INNER JOIN Ticket t
ON b.BlankCode = t.Blank_BlankCode
INNER JOIN MCO_Blank mb
ON b.BlankCode = mb.Blank_BlankCode
INNER JOIN Purchase p1
ON t.PurchaseID = p1.PurchaseID
INNER JOIN Purchase p2
ON mb.PurchaseID = p2.PurchaseID
INNER JOIN Payment pa1
ON t.PurchaseID = pa1.PurchaseID
INNER JOIN Payment pa2
ON mc.PurchaseID = pa2.PurchaseID
WHERE pa1.Status = "Paid";
I had to make a guess at which payment and purchase is intended for the aliases. These may not be correct in the from
and where
clauses.
Inner join multiple times on same table on same column
You want decode two value for rank (one for newrank and one for promoterrank ) then you should join tha table rank two time eg:
SELECT A.*
, B.rank as newrank
, C.rank as promoterrank
DATE_FORMAT(promotiontime, '%Y-%m-%dT%H:%i:%s0Z')
FROM promotions A
INNER JOIN ranks B ON A.newrank = B.id
INNER JOIN ranks C ON A.promoterrank = C.id
WHERE agent='".$_POST['agent']."' ORDER BY id DESC
anyway you should avoid the use of php var in sql code .. (you are at risk for sqlinjection) so take a look at prepared statement and binding param
Related Topics
Query to Find All Fk Constraints and Their Delete Rules (Sql Server)
What Is The T-Sql Equivalent of MySQL Syntax Limit X, Y
Find a Database with a Particular Table or Find a Table in Every Database of SQL Server
How to Do Many to Many Table Outer Joins
Sql: Count() Based on Column Value
How to Fetch The Nth Highest Salary from a Table Without Using Top and Sub-Query
Oracle SQL to Change Column Type from Number to Varchar2 While It Contains Data
How to Pass Two SQL Tables as Input Parameter for R Codes in SQL Server
How to Use MySQL Index Columns
Oracle SQL Order by in Subquery Problems!
How to Set a Datetime Variable in SQL Server 2008
Count Distinct Records (All Columns) Not Working
Checking for Existence of Index in Postgresql