homepage Welcome to WebmasterWorld Guest from 54.211.118.204
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL LEFT JOIN, RIGHT JOIN AND Union ALL issues.!
Some Items are NOT Displayed At All
Sherif




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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




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

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved