mysql full join query

Question

What's wrong with the following query. It's not working and I basically need it to do a full join and return all fields whether there is a match or not.

SELECT tbl_tickets.ID AS Ticket_ID, tbl_tickets.Ticket_Number, tbl_tickets.Ticket_Category, tbl_orders.PO_Number, tbl_invoice_to.Name AS Dealer_Name 
FROM tbl_tickets 
FULL INNER JOIN tbl_orders ON tbl_tickets.Order_ID=tbl_orders.ID 
FULL INNER JOIN tbl_invoice_to ON tbl_tickets.Invoice_To=tbl_invoice_to.ID
WHERE Ticket_Type='PARENT' AND {$where} 
ORDER BY tbl_tickets.Ticket_Category

1. It's not a sql query, it's part of php code(error could be here`{$where}`). 2. Please, define *not working* — sectus, Dec 03 '14 at 02:12
I can't say I've ever heard of a full *inner* join. Do you mean to use a full *OUTER* join? I also have five bucks on betting you probably mean to use a `LEFT JOIN`. — Cᴏʀʏ, Dec 03 '14 at 02:14
Just about to say the same thing. Just make up syntax on the fly and wonder why it doesn't work? :) — Chris Caviness, Dec 03 '14 at 02:17
DOESENT WORK EITHER: SELECT tbl_tickets.ID AS Ticket_ID, tbl_tickets.Ticket_Number, tbl_tickets.Ticket_Category, tbl_orders.PO_Number, tbl_invoice_to.Name AS Dealer_Name FROM tbl_tickets FULL OUTER JOIN tbl_orders ON tbl_tickets.Order_ID=tbl_orders.ID FULL OUTER JOIN tbl_invoice_to ON tbl_tickets.Invoice_To=tbl_invoice_to.ID WHERE Ticket_Type='PARENT' AND {$where} ORDER BY tbl_tickets.Ticket_Category — kharkuSINGH, Dec 03 '14 at 02:22
If you need further help, you'll need to provide your db structure. How are we supposed to know if your joins/identifiers/table references are correct? — EternalHour, Dec 03 '14 at 02:23

score 0 · Answer 1 · edited May 23 '17 at 11:50

0

AFAIK, there's no such thing as a FULL INNER JOIN. It's probably falling back to a regular INNER JOIN or doing something else entirely.

I think you're looking for an OUTER-type join. I would start with changing these to LEFT JOIN to see if that returns the results you're looking for.

If it does not, and you truly do need a FULL OUTER JOIN, then see Full Outer Join in MySQL for an example on how to achieve one in MySQL.

edited May 23 '17 at 11:50

Community

1
1

answered Dec 03 '14 at 02:19

Cᴏʀʏ

105,112
20
162
194

even full outer join returns nothing – kharkuSINGH Dec 03 '14 at 02:21
How are you executing the query? What is `$where`? You may need to provide more context if you're getting "nothing." Please edit your question to include more code. Inspecting the final query (after the string evaluation) or enabling error handling in your PHP might help as well. – Cᴏʀʏ Dec 03 '14 at 02:23

score 0 · Answer 2 · answered Dec 03 '14 at 02:37

Given the names of your tables, I would expect the foreign key relationships to line up. Hence, an inner join should be sufficient:

SELECT t.ID AS Ticket_ID, t.Ticket_Number, t.Ticket_Category, o.PO_Number, i.Name AS Dealer_Name 
FROM tbl_tickets t INNER JOIN
     tbl_orders o
     ON t.Order_ID = o.ID INNER JOIN
     tbl_invoice_to i
     ON t.Invoice_To = i.ID
WHERE Ticket_Type = 'PARENT' AND {$where} 
ORDER BY t.Ticket_Category

(Note I introduced table aliases, which make the query easier to write and to read.)

If this is returning nothing, then start by removing conditions in the WHERE clause. If still nothing is returned, then check the join condition.

MySQL does not support full outer join. So, when you write:

FROM tbl_tickets FULL INNTER JOIN
     tbl_orders . . .

You are assigning the alias FULL to tbl_tickets -- definitely not your intention.

score 0 · Answer 3 · answered Dec 03 '14 at 02:45

0

You need to do a left join it returns all the records of the first table whether there match or no . and if you want to do a full outer join yo need to do a left join - union - right join because there's not full outer join in MySQL

answered Dec 03 '14 at 02:45

Jorge Tovar

1,374
12
17

mysql full join query

3 Answers3