MySQL left join problem

[Zaggs]

Hi Guys!

I am having a problem when doing a left join with the “parked_stats” table below. I think it is because there is no data in that table. Would that cause no rows to be returned? If so, how can I get around that?

select UNIX_TIMESTAMP(dt_auctions.end_date) - UNIX_TIMESTAMP() as time_left, sum(dt_parked_stats.uniques) as website_visitors, dt_currencies.symbol, dt_auctions.end_date, dt_auctions.paid_auction, dt_auctions.high_bid, dt_domains.id, dt_domains.domain, dt_domains.bin_price, dt_domains.minimum_offer, dt_domains.views, dt_domains.previous_offers, dt_domains.currency from dt_domains left join dt_currencies on dt_currencies.code = dt_domains.currency left join dt_auctions on dt_auctions.domain_id = dt_domains.id left join dt_parked_stats on dt_parked_stats.domain = dt_domains.domain where active='1' order by dt_domains.domain

[r937]

change this –

if(sum(dt_parked_stats.uniques) is null
  , '--'
  , dt_parked_stats.id) as website_visitors

to this –

COALESCE(SUM(dt_parked_stats.uniques),0) AS website_visitors

[Zaggs]

@r937

no, i didn’t say that

i said it was okay for the right table to have no data in a LEFT OUTER JOIN

i mean, that’s why you need a LEFT OUTER JOIN – to return a given row from the left table even if there is no matching row for it in the right table

that said, your first join should be INNER, since i am going to bet that there is no value in dt_domains.currency that isn’t in dt_currencies.code

on the other hand, the second and third joins should be LEFT OUTER, since i imagine it might be possible for there to be domains that have no auction, as well as domains that have no parked stats

Okay great, seems to work now. One more quick question: how can I order by website_visitors column that I create within this query? I tried and it just gives an error, here’s my query now:

select UNIX_TIMESTAMP(dt_auctions.end_date) - UNIX_TIMESTAMP() as time_left, 
if(sum(dt_parked_stats.uniques) is null, '--', dt_parked_stats.id) as website_visitors, 
    dt_currencies.symbol, 
    dt_auctions.end_date, 
    dt_auctions.paid_auction, 
    dt_auctions.high_bid, 
    dt_domains.id, 
    dt_domains.domain, 
    dt_domains.bin_price, 
    dt_domains.minimum_offer, 
    dt_domains.views, 
    dt_domains.previous_offers, 
    dt_domains.currency from dt_domains 
inner join dt_currencies on dt_currencies.code = dt_domains.currency 
left outer join dt_auctions on dt_auctions.domain_id = dt_domains.id 
left outer join dt_parked_stats on dt_parked_stats.domain = dt_domains.domain 
	where dt_domains.active='1' 
    group by dt_domains.id order by website_visitors asc

[r937]

no, i didn’t say that

i said it was okay for the right table to have no data in a LEFT OUTER JOIN

i mean, that’s why you need a LEFT OUTER JOIN – to return a given row from the left table even if there is no matching row for it in the right table

that said, your first join should be INNER, since i am going to bet that there is no value in dt_domains.currency that isn’t in dt_currencies.code

on the other hand, the second and third joins should be LEFT OUTER, since i imagine it might be possible for there to be domains that have no auction, as well as domains that have no parked stats

[Zaggs]

@r937

why did you choose to use two LEFT joins followed by a RIGHT???

I thought you said in one of your above posts that it’s fine to use to left outer joins and a right join on the other table even if it has no data…

[r937]

why did you choose to use two LEFT joins followed by a RIGHT???

[Zaggs]

@r937

okay, then your WHERE clause is fine, although you should probably qualify the active column with its table name as general good practice in query writing

what GROUP BY did you use?

Hi r937,

Im now using the following query but still no results. Yet if I drop the right join, it works fine.

select UNIX_TIMESTAMP(dt_auctions.end_date) - UNIX_TIMESTAMP() as time_left, 
	sum(dt_parked_stats.uniques) as website_visitors, 
    dt_currencies.symbol, 
    dt_auctions.end_date, 
    dt_auctions.paid_auction, 
    dt_auctions.high_bid, 
    dt_domains.id, 
    dt_domains.domain, 
    dt_domains.bin_price, 
    dt_domains.minimum_offer, 
    dt_domains.views, 
    dt_domains.previous_offers, 
    dt_domains.currency from dt_domains 
left outer join dt_currencies on dt_currencies.code = dt_domains.currency 
left outer join dt_auctions on dt_auctions.domain_id = dt_domains.id 
right join dt_parked_stats on dt_parked_stats.domain = dt_domains.domain 
	where dt_domains.active='1' group by dt_domains.id order by dt_domains.domain

[r937]

@Zaggs

Also, the “active” column is in the domains table.

okay, then your WHERE clause is fine, although you should probably qualify the active column with its table name as general good practice in query writing

what GROUP BY did you use?

[Zaggs]

@r937

it’s perfectly okay to have no data in the right table of a LEFT OUTER JOIN – that would not explain why there are no results returned by the query

zaggs, two questions –

you have SUM() in the SELECT clause but the GROUP BY clause is missing… why?

which table is the active column in?

Hmm, it could actually be due to the GROUP BY clause missing. Also, the “active” column is in the domains table.

Thanks.

[r937]

p.s. if you want people to look at your query, please apply some formatting

SELECT UNIX_TIMESTAMP(dt_auctions.end_date) - 
       UNIX_TIMESTAMP() AS time_left
     , sum(dt_parked_stats.uniques) AS website_visitors
     , dt_currencies.symbol
     , dt_auctions.end_date
     , dt_auctions.paid_auction
     , dt_auctions.high_bid
     , dt_domains.id
     , dt_domains.domain
     , dt_domains.bin_price
     , dt_domains.minimum_offer
     , dt_domains.views
     , dt_domains.previous_offers
     , dt_domains.currency 
  FROM dt_domains 
LEFT 
  JOIN dt_currencies 
    ON dt_currencies.code = dt_domains.currency 
LEFT 
  JOIN dt_auctions 
    ON dt_auctions.domain_id = dt_domains.id 
LEFT 
  JOIN dt_parked_stats 
    ON dt_parked_stats.domain = dt_domains.domain 
 WHERE active='1' 
ORDER 
    BY dt_domains.domain

coding everything on one humoungously long line is user-hostile

[r937]

it’s perfectly okay to have no data in the right table of a LEFT OUTER JOIN – that would not explain why there are no results returned by the query

zaggs, two questions –

you have SUM() in the SELECT clause but the GROUP BY clause is missing… why?

which table is the active column in?

[IBazz]

erm, erm.

If there is no data in your table, you can’t really get any out of it.

put some data in the tables. Then, if you still get no results returned, post again and we can check out the query properly.

bazz

[Zaggs]

@r937

that’s twice now you’ve mentioned you received an error, and twice you have neglected to tell us what the error message said …

Here’s the error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/user/public_html/smarty/libs/plugins/function.return_paged_results.php  on line 81

[r937]

unfortunately, that’s a php error, and all it says is that you had a mysql error

try running your query outside of php

[r937]

that’s twice now you’ve mentioned you received an error, and twice you have neglected to tell us what the error message said …

[Zaggs]

@r937

change this –

if(sum(dt_parked_stats.uniques) is null
  , '--'
  , dt_parked_stats.id) as website_visitors

to this –

COALESCE(SUM(dt_parked_stats.uniques),0) AS website_visitors

Hmm okay changed it to that but still getting an error when doing an order by at the end. Without the order by it works fine. Any idea why?

order by website_visitors asc

[r937]

@Zaggs

It’s kind of imperative that I do use it in the WHERE clause

no, that’s not true

what you want is to use it in your HAVING clause

[Zaggs]

@r937

of course there is

ORDER BY website_visitors

you just can’t use it in the WHERE clause

Ahhh I see…but is there no way for me to use it in the WHERE clause? It’s kind of imperative that I do use it in the WHERE clause otherwise it deems my sql query useless.

Thanks in advance.

[r937]

@Zaggs

r937: so there is no way for me to order this query by “website_visitors”?

of course there is

ORDER BY website_visitors

you just can’t use it in the WHERE clause

[r937]

guelphdad, it’s an aggregate function expression, so referring to the calculation is not even allowed in the WHERE clause