SQL using LEFT JOIN

[VernMan]

I have the following SQL

SELECT DISTINCT(billing.orderID),media.pub_name, billing.space_cost, billing.pub_box, billing.messenger, billing.rs_fax, billing.rs_box, billing.bold, billing.rs_email, billing.logo, billing.prod, billing.pdf, billing.printnpost, billing.shading, billing.fedex, billing.web_charge, billing.pre_pay, billing.mailed, billing.media_cost, billing.weekday_addon, billing.misc, billing.printnpostCost, billing.miscCost, billing.fedexCost, billing.net_space_cost, billing.pubsWebChargeFee, customer_ads.rs_inv_num, customer_ads.start_date, customer_ads.id, customers.company, customer_ads.job_title, customer_ads.estimatedCost

FROM billing

LEFT JOIN customer_ads ON customer_ads.id = billing.orderID

LEFT JOIN customers ON customer_ads.client_id = customers.customers_id

LEFT JOIN receivables ON customer_ads.client_id = receivables.client_id

LEFT JOIN media ON media.id = customer_ads.media_id

WHERE (start_date >= "2013-01-23 00:00:00") AND (start_date <= "2013-01-25 23:59:59")

and I want to add the column "receivables.cust_check_num" which if I add to the end above only returns those records that are not null on receivables.cust_check_num, which there will be empty values, however the values will only be empty if no record exist in the receivables table. Anyone have any ideas how to resolve this?

Thanks

[bregent]

Should work. try:

SELECT DISTINCT(billing.orderID),media.pub_name, billing.space_cost, billing.pub_box, billing.messenger, billing.rs_fax, billing.rs_box, billing.bold, billing.rs_email, billing.logo, billing.prod, billing.pdf, billing.printnpost, billing.shading, billing.fedex, billing.web_charge, billing.pre_pay, billing.mailed, billing.media_cost, billing.weekday_addon, billing.misc, billing.printnpostCost, billing.miscCost, billing.fedexCost, billing.net_space_cost, billing.pubsWebChargeFee, customer_ads.rs_inv_num, customer_ads.start_date, customer_ads.id, customers.company, customer_ads.job_title, customer_ads.estimatedCost

FROM (((billing

LEFT JOIN customer_ads ON customer_ads.id = billing.orderID)

LEFT JOIN customers ON customer_ads.client_id = customers.customers_id )

LEFT JOIN receivables ON customer_ads.client_id = receivables.client_id )

LEFT JOIN media ON media.id = customer_ads.media_id

WHERE (start_date >= "2013-01-23 00:00:00") AND (start_date <= "2013-01-25 23:59:59")

That's assuming your intent is to have 'billing' as the LEFT table in the first join, and 'customer_ads' as the LEFT table in the other joins. If 'customer_ads is supposed to be the LEFT table in all of the joins, then your first join statement is reversed.

[VernMan]

I want to join tables from customer_ads and customers when there is a match (and there always will be).
However on the receivables table there may or may not match and if not I want to return that value as blank, but still need it returned.

This above now returns the same OrderID over and over agian, but they with different cust_check_num when I add the ", receivables.cust_check_num" before the FROM statement as I am trying to retun that value. Does that make sense?

Example:

SELECT DISTINCT(billing.orderID),media.pub_name, billing.space_cost, billing.pub_box, billing.messenger, billing.rs_fax, billing.rs_box, billing.bold, billing.rs_email, billing.logo, billing.prod, billing.pdf, billing.printnpost, billing.shading, billing.fedex, billing.web_charge, billing.pre_pay, billing.mailed, billing.media_cost, billing.weekday_addon, billing.misc, billing.printnpostCost, billing.miscCost, billing.fedexCost, billing.net_space_cost, billing.pubsWebChargeFee, customer_ads.rs_inv_num, customer_ads.start_date, customer_ads.id, customers.company, customer_ads.job_title, customer_ads.estimatedCost, receivables.cust_check_num

FROM (((billing

LEFT JOIN customer_ads ON customer_ads.id = billing.orderID)

LEFT JOIN customers ON customer_ads.client_id = customers.customers_id )

LEFT JOIN receivables ON customer_ads.client_id = receivables.client_id )

LEFT JOIN media ON media.id = customer_ads.media_id

WHERE (start_date >= "2013-01-26 00:00:00") AND (start_date <= "2013-01-28 23:59:59")

[bregent]

>However on the receivables table there may or may not match and if not

>I want to return that value as blank, but still need it returned.

I understand that, but that's not what I was asking. I trying to determine which tables you want on the LEFT and which are on the RIGHT.  It would be best for you to include a diagram of your schema because as you have it currently written, it does not look like a valid SQL statement.

>This above now returns the same OrderID over and over agian,

>but they with different cust_check_num when I add the ",

>receivables.cust_check_num" before the FROM statement as

>I am trying to retun that value. Does that make sense?

Are there multiple receivables for each OrderID? If so, then you should expect to see the OrderID repeated, correct?

[VernMan]

Ok well the above all works except for when I want to pull a value from the receiables table. But let me make it a little clearer so you understand whats going on:

There are 5 tables being joined:

• billing - contains the billing details to the ad placed by the customer
• customer_ads  - the table with the OrderID which is how we tie into the receivables table
• customers - joined on the client_id to get customer.company name

• media - used to retrieve pulication name
• receivables - the new table being added to see is if cust_check_num is null, it will either return the value of a check number or be blank, if blank I still want to return above data, only this being null

All of these tables willl have data connected to them except receivables which may or not have data. Does that make sense now?

[bregent]

>All of these tables willl have data connected to

>them except receivables which may or not have data.

If that's the case, then you should not be using outer joins except for the receivables table. And for that you need to left outer join the customer_ads table ON the receivables table, not the other way around. As I stated earlier, your current joins are ambiguous; in most other DBMS you would get an error. Unfortunately the error checking in MySQL (I assume this is what you are using) is very poor.

[VernMan]

OK I get it, I think, the following is how it should be:

SELECT DISTINCT(billing.orderID),media.pub_name, billing.space_cost, billing.pub_box, billing.messenger, billing.rs_fax, billing.rs_box, billing.bold, billing.rs_email, billing.logo, billing.prod, billing.pdf, billing.printnpost, billing.shading, billing.fedex, billing.web_charge, billing.pre_pay, billing.mailed, billing.media_cost, billing.weekday_addon, billing.misc, billing.printnpostCost, billing.miscCost, billing.fedexCost, billing.net_space_cost, billing.pubsWebChargeFee, customer_ads.rs_inv_num, customer_ads.start_date, customer_ads.id, customers.company, customer_ads.job_title, customer_ads.estimatedCost, receivables.cust_check_num

FROM billing

JOIN customer_ads ON customer_ads.id = billing.orderID
JOIN customers ON customer_ads.client_id = customers.customers_id
JOIN media ON media.id = customer_ads.media_id

LEFT OUTER JOIN customer_ads ON receivables.client_id = customer_ads.client_id

WHERE (start_date >= "2013-01-23 00:00:00") AND (start_date <= "2013-01-25 23:59:59")

However I get the following error:

MySQL Error#; 1066

Not unique table/alias: 'customer_ads'

[bregent]

To be honest, I never use ANSI 92 join syntax - I find it hard to read! I do all of my joins in the WHERE clause. But I don't use MySQL and it does not allow that so you must put outer joins in the FROM clause. Try this:

FROM billing

JOIN customer_ads ON customer_ads.id = billing.orderID
JOIN customers ON customer_ads.client_id = customers.customers_id
JOIN media ON media.id = customer_ads.media_id

LEFT JOIN receivables ON customer_ads.client_id =receivables.client_id

WHERE (start_date >= "2013-01-23 00:00:00") AND (start_date <= "2013-01-25 23:59:59")

[VernMan]

works if remove ", receivables.cust_check_num" t that defeats the ole purpoe, s returning the sam orderover andver again

[bregent]

Sorry, I dont follow you.

Do you have multiple receiables for each customer? If so, then you are going to get multiple rows. If that's what you are asking, then you may be misunderstanding how the DISTINCT keyword works.

Can you show me an example of the output?

[VernMan]

That's a little hard to come up with but does this help? I can get you a spreadhseet if you really need it.

Before

After

[bregent]

I don't see the problem. Are all of those cust_check_numbers not associated with that orderID?

[VernMan]

they are not, it's only one orderID and should only be 1 cust_check_num and multiple orderID & check_num

[bregent]

OK, sounds like you have a cartesan product. Can you post a snapshot of your ERD with the cardinality details?

[VernMan]

I have no idea what you just said "cartesan product"? "Can you post a snapshot of your ERD with the cardinality details?"

I'm running a CentOS platform with php & MySQL using Dreamweaver

[bregent]

I want to see your entity relationship diagram so I understand the relationships between all of the tables. If you don't have one then just create a quick one in Word or any graphics app. Also, what is the definition of "client_id" ?  Is that a unique column in the receivables table?