asked on

SQL Query stopped returning Data

Say,

Data stored in MySql 5 running on Win7
Since moving it from linux MySql DB the join query below doesn't return data: If I remove the join - tTop10NosByCallType returns data.
Can you explain it and how to solve please?

SELECT
*,
tDirectory.`CallerID` AS tDirectory_CallerID,
tDirectory.`Category` AS tDirectory_Category,
tDirectory.`Access` AS tDirectory_Access,
tDirectory.`Status` AS tDirectory_Status
FROM
`tDirectory` tDirectory LEFT OUTER JOIN `tTop10NosByCallType` tTop10NosByCallType ON tDirectory.`TelNo` = tTop10NosByCallType.`No`
WHERE
tTop10NosByCallType.Client = $P{Client}
and `Invoice number` = $P{InvNo}
ORDER BY
CallType DESC,
CountByCallType DESC,
DurationHHMM DESC

Shaun Wingrin

ASKER

Do you need to see the tables?

Saurabh Bhadauria

Move below where condition to join .... I mean with on

tTop10NosByCallType.Client = $P{Client}

SELECT
*,
     tDirectory.`CallerID` AS tDirectory_CallerID,
     tDirectory.`Category` AS tDirectory_Category,
     tDirectory.`Access` AS tDirectory_Access,
     tDirectory.`Status` AS tDirectory_Status
FROM
     `tDirectory` tDirectory LEFT OUTER JOIN `tTop10NosByCallType` tTop10NosByCallType ON tDirectory.`TelNo` = tTop10NosByCallType.`No`       and
   tTop10NosByCallType.Client = $P{Client}

WHERE
   `Invoice number` = $P{InvNo}
ORDER BY
     CallType DESC,
     CountByCallType DESC,
     DurationHHMM DESC

Open in new window

Jim Horn

Just a thought ... the above T-SQL uses * to return all columns, but you have two queries in your table. So is the intent here to return all columns from both tables, or one, or the other?

tDirectory.*
tTop10NosByCallType.*
* -- this is the same as saying both of the above

Shaun Wingrin

ASKER

Tx.
1. I replaced pasted your code above, but same issue - no data. Wasn't sure what you meant by the comment though.
2. The same issue with just a *
I need fields from both tables

ASKER CERTIFIED SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shaun Wingrin

ASKER

Moved back to Linux...