1. Home
  2. Forums Index
  3. Server Side / Databases 2:02 am May 21, 2026

Forum Moderators: open

Message Too Old, No Replies

MySQL LEFT JOIN, RIGHT JOIN AND Union ALL issues.!

Some Items are NOT Displayed At All

         

Sherif

9:42 am on Aug 2, 2010 (gmt 0)

10+ Year Member



Hey Guys....


First of all i would like to thank each and everyone here in this forum for making it a fruitful place where one can share his knowledge, and help others in their coding helping them achieve their goals.

I am Currently facing a problem in one of My MySQL queries.

I am working on a database that will display user transactions based on the product id.

The products are apartments in a building. So the unique id will be a combination of the Building Number Apartment Number, and the invoice id itself.

The query that i am using is the following:

SELECT invoices.* , payments.*
FROM
invoices LEFT JOIN payments
ON
invoices.building_no = payments.building_no AND invoices.apartment_no = payments.apartment_no AND invoices.invoice_id = payments.invoice_id

WHERE invoices.building_no = 1 AND invoices.apartment_no = 1

UNION ALL

SELECT invoices.* , payments.*
FROM invoices RIGHT JOIN payments

ON payments.building_no = invoices.building_no AND payments.apartment_no = invoices.apartment_no AND payments.invoice_id = invoices.invoice_id

WHERE payments.building_no = 1 AND payments.apartment_no = colname1 AND payments.invoice_id is null

ORDER BY invoice_date, payment_date ASC


in the second where statement i used the is null command to avoid the duplicates that will occur due to the union all command.


The output of the query is correct, but the problem is with a segment of the output itself.
for the invoice_id column, the value appears only when there is a match in both tables, and if it is missing in the payments table, then we do not see it anywhere.

I need the invoice id to appear correctly since it will be used for later reference when we want to attach a payment to the invoice itself so that we can compare the values, and dates... etc...

Could you please help me on solving this problem. Your help will be much appreciated.


Sincerely,
Sherif

Sherif

9:38 am on Aug 5, 2010 (gmt 0)

10+ Year Member



Hey Guys....

Until now i didn't get any response related to my issue, and until now i couldn't find any answer to my problem.

I checked the syntax multiple times, and was able to optimize it. The output as far as i am concerned is correct in terms of correctly joining the results between the 2 tables.

The main problem that i am facing, if i have data in the invoices table, and no values in the payment table related to that data, then the values of the columns that are being joined (building_no, apartment_no, and invoice_id) do not appear, while the other data in the other columns of the same row appear.

Does anyone have any solution to this problem. (I need the data to appear since these are the unique numbers of the row itself, so i need it to pass the information to another page so that we can later associate the payments with these values).

I tried the the
LEFT JOIN
& the
LEFT OUTER JOIN
and i get the same results (output)

Your Help will be much appreciated.

Thanks a lot for your support in advance.

Sincerely,
Sherif

LifeinAsia

4:10 pm on Aug 5, 2010 (gmt 0)

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month



I'm a little confused about what you are trying to show...

Also, why are you using a LEFT JOIN in the first part and a RIGHT JOIN in the second?

Sherif

12:48 pm on Aug 8, 2010 (gmt 0)

10+ Year Member



Hey LifeinAsia,

In the SQL syntax, there is a FULL JOIN command that joins the tables together similar to the left join + right join, but this FULL JOIN syntax doesn't exist in MySQL So i have to use both to get the same effect, but in order to avoid the duplicates that will arrise because of the join using the UNION ALL command, we use the is null in the seccond query.

The script works really well in obtaining the results that i want.

The problem that i am currently facing is that, if there is no match in the left join, the data is retrieved from the table in the left, but the values of the Joined Columns are not displayed.

Is there a command that i should use to output the values of the JOINED COLUMNS even if there is no actual join / match?

This is a serious issue, since i need to use these values as they are the unique key that i will use later on to associated data to it in the other table using forms.

Thanks in advance for your support.

Sincerely,
Sherif

syber

5:40 pm on Aug 8, 2010 (gmt 0)

10+ Year Member



You are not going to get what you want using a JOIN. By design, the columns from the non matching table in an OUTER JOIN will be blank.

Instead, try a subquery. 


SELECT invoices.*,(SELECT payment_column 
  FROM payments 
  WHERE invoices.building_no = payments.building_no  
  AND invoices.apartment_no = payments.apartment_no  
  AND invoices.invoice_id = payments.invoice_id) AS payment_column 
 
FROM invoices 
 
WHERE building_no = 1 AND apartment_no = 1


If there is a matching payment, the payment information will show up in the payment column(s).

Sherif

11:09 am on Aug 10, 2010 (gmt 0)

10+ Year Member



Hey Syber,

Thanks a lot for your support up until now, but in the last couple of days, I tried to search for more information about the MySQL subquery, but i couldn't find any information related to it (explaining the concept that you suggested).

Could you please explain the concept on how the subquery could be used to act as a LEFT JOIN Syntax?

Thanks a lot for your support.

Sincerely,
Sherif

syber

4:06 pm on Aug 10, 2010 (gmt 0)

10+ Year Member



The subquery brings in the matching columns from the payments table (if they exist) without having to do a JOIN.

By using the JOIN criteria in the WHERE clause of the subquery, you are able to display matching data from other tables.

MySQL reference for correlated subqueries: [dev.mysql.com ]

Try the sample code I displayed above.

Sherif

9:05 pm on Aug 10, 2010 (gmt 0)

10+ Year Member



Hey syber,

Thanks a lot for your cooperation, but i have a problem using this method.

When i tried using this method, in the payments table, there are more than one column that i need to retrieve since it contains relevant data to the payment itself, example payment_method, payment_location, payment_description etc...

SELECT invoices.*,(SELECT payment_value, payment_method, payment_location, payment_description
FROM payments
WHERE invoices.building_no = payments.building_no
AND invoices.apartment_no = payments.apartment_no
AND invoices.invoice_id = payments.invoice_id) AS payment_column

FROM invoices

WHERE building_no = 1 AND apartment_no = 1


When i modified the query to obtain these columns, I got the MySQL Error #1241 - Operand Should Contain 1 Column(s) What should i do if i need to obtain more than one column from the other table in the subquery?

Again, Thanks for your support.

Sincerely,
Sherif

syber

11:08 pm on Aug 10, 2010 (gmt 0)

10+ Year Member



You repeat the column definitions: 


SELECT invoices.*, 
 (SELECT payment_value 
 FROM payments 
 WHERE invoices.building_no = payments.building_no 
 AND invoices.apartment_no = payments.apartment_no 
 AND invoices.invoice_id = payments.invoice_id) AS payment_value, 
(SELECT payment_method 
 FROM payments 
 WHERE invoices.building_no = payments.building_no 
 AND invoices.apartment_no = payments.apartment_no 
 AND invoices.invoice_id = payments.invoice_id) AS payment_method, 
(SELECT payment_location 
 FROM payments 
 WHERE invoices.building_no = payments.building_no 
 AND invoices.apartment_no = payments.apartment_no 
 AND invoices.invoice_id = payments.invoice_id) AS payment_location, 
(SELECT payment_description 
 FROM payments 
 WHERE invoices.building_no = payments.building_no 
 AND invoices.apartment_no = payments.apartment_no 
 AND invoices.invoice_id = payments.invoice_id) AS payment_description 
 
 
FROM invoices 
 
WHERE building_no = 1 AND apartment_no = 1



This ends up being the same as the following LEFT JOIN: 

SELECT invoices.*, payment_value, payment_method, payment_location, payment_location 
FROM invoices LEFT OUTER JOIN payments 
 ON invoices.building_no = payments.building_no AND  
 invoices.apartment_no = payments.apartment_no AND 
 invoices.invoice_id = payments.invoice_id 
 
WHERE invoice.building_no = 1 AND invoice.apartment_no = 1

Sherif

11:35 pm on Aug 10, 2010 (gmt 0)

10+ Year Member



Hey Syber,

That's what I expected, but i thought that there would be a better method which requires less coding in the query.

The remaining part that makes me confused, is that in many cases, there could be more than one payment that is associated with the same invoice number. How would this syntax/query treat this one to many relation in the output of this query? Would this query output the same row multiple times from the invoice table to satisfy the values from the payments table?

Again, thanks for your support and patience.


Sincerely,
Sherif

syber

3:10 pm on Aug 11, 2010 (gmt 0)

10+ Year Member



Ok, I assumed a one-to-one relationship.

The LEFT JOIN will give you multiple rows when there are multiple payments for the same invoice. I guess where I got confused was on why you were trying to do a RIGHT JOIN initially. Do you have payments that don't have an invoice number attached?

Sherif

8:03 pm on Aug 11, 2010 (gmt 0)

10+ Year Member



Hey Syber,

Yes... In most cases, there could be a payment or 2 for a product without an invoice number but it should be associated to the product, this situation generally happens when a customer pays a small down payment before we agree with him/her on the number of installments and their values. That was why I used a Right Join to get these payments.

The syntax that I used in the first post works fine, & outputs the desired results with the exception that when there is no match on the columns in the ON section of the query, the invoice_id, building_no, and apartment_no, are not printed (retrieved).

These values are crucial for later use as they are the unique number for the invoice related to each individual apartment, and therefore we will need it to associate the payment with the appropriate invoice when the customer comes to pay his/her installment.

Thanks for your patience and support up until this moment :) I really appreciate all of the support.

Sincerely,
Sherif Malek

syber

9:31 pm on Aug 11, 2010 (gmt 0)

10+ Year Member



In that situation you might want to take the invoice_id match out of the ON clause in each of the queries. This way the payment information should return even if there isn't a match on invoice_id.

Sherif

9:42 pm on Aug 11, 2010 (gmt 0)

10+ Year Member



Hey Syber,

If we do this, then how will the left join work if the invoice_id is removed from the ON clause when the relation between both tables use the unique id for each row depending on 3 columns (building_no, apartment_no, invoice_id) if we only use the building_no, and apartment_no then there will be hundreds of matches, but the payments will not know which invoices it should be depending on.

How will each payment be associated to the invoice that it depends on? Especially that we can have more than one payment for the same invoice, or even have a payment without an invoice.

Thanks for your support.


Sincerely,
Sherif Malek

syber

10:12 pm on Aug 11, 2010 (gmt 0)

10+ Year Member



You can't have it both ways, either every payment needs to have an invoice_id - or you match on just building_no and apartment_no and display the invoice_id if it is present.

Sherif

10:19 pm on Aug 11, 2010 (gmt 0)

10+ Year Member



Hey Syber,

I thought that I can have 2 columns for the invoice_id having the same value so that one used for the relations between the table, and the other for the output... What do you think of this option?

Sincerely,
Sherif Malek

syber

1:00 am on Aug 12, 2010 (gmt 0)

10+ Year Member



Doesn't help, if the payment row is missing the invoice_id there is no relation.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Databases 2:02 am May 21, 2026