CODEWITHSUNDEEP
phpsqlsql-serverpdo
Mike
Mike

Reputation: 521

PHP PDO - dblib MSSQL - SQL join issue

I have been struggling with this issue for a few days now and i cant seem to come up with a solution, nor can i see where im going wrong.

Im trying to write a query in sql that has a couple joins because i need to display information found in a few different tables.

My issue lies in the joins, whenever i do even the simplest of joins my query returns nothing. Below is 2 examples of things ive tried. Hopefully im just missing something in the syntax.

dbo.VehicleJobHistory:

VehicleID           Number
BookingID           Text
DriverID            Number
PickupSuburb        Text
DestinationSuburb   Text
ReasonDispatched    Text
TimeJobRequired     Date/Time
TimeCarAccepted     Date/Time
TimeCarPickup       Date/Time
TimeCarComplete     Date/Time
KmToPickup          Number
KmOfJob             Number
_timestamp          DateTime

Sample date from dbo.VehicleJobHistory:

$STH = $DBH->query('SELECT * FROM dbo.VehicleJobHistory WHERE TimeJobRequired > "02/03/2013" AND VehicleID = "451"');

451/30162090/8387/Springwood/Springwood//Feb 3 2013 12:58:23:800AM///0/0/ 451/30163071/8387/Loganholme/Loganholme//Feb 3 2013 01:23:45:263AM///1200/4250/ 451/30163856/8387/Shailer Park/Shailer Park//Feb 3 2013 01:45:50:450AM///0/0/ 451/30163965/8387/Loganholme/Runcorn//Feb 3 2013 01:48:31:200AM///2950/16050/ 451/30164712/8387/Eight Mile Plains/Eight Mile Plains//Feb 3 2013 02:09:33:017AM///2450/5750/ 451/30165778/8387/Springwood/Springwood//Feb 3 2013 02:44:51:363AM///800/2100/ 451/30166370/8387/Loganholme/Loganholme//Feb 3 2013 03:06:28:103AM///0/0/ 451/30166916/8387/Shailer Park/ //Feb 3 2013 03:28:46:553AM///0/13950/ 451/30169626/11979/Logan Central/ //Feb 3 2013 07:18:05:633AM///0/0/ 451/30169632/11979/Logan Central/Logan Central//Feb 3 2013 07:18:33:120AM///0/0/ 451/30169783/11979/Woodridge/Springwood//Feb 3 2013 07:38:32:377AM///1250/6400/ 451/30170203/11979/Logan Central/ //Feb 3 2013 08:31:52:960AM///0/1050/

dbo.Vehicle:

VehicleID          Number 
CarNumber          Text 
PrimaryFleetID     Number 
Conditions         Text 
RegoNum            Text 
RegoExpiry         Date/Time 
TaxiLicNum         Number 
TaxiLicExpiry      Date/Time
VehicleMake        Text 
VehicleModel       Text 
VehicleYear        Text 
OwnerID            Number 
OperatorID         Number

Sample Data from dbo.vehicle

$STH = $DBH->query('SELECT * FROM dbo.Vehicle WHERE  VehicleID = "451"');

echo $row->VehicleID  . "/"; 
echo $row->CarNumber  . "/"; 
echo $row->PrimaryFleetID . "/"; 
echo $row->Conditions . "/"; 
echo $row->OwnerID . "/"; 
echo $row->OperatorID . "/";

451/4/5/120395399168/-1/3/

When i try and run any query that includes a join i get no result...

this returns nothing:

$STH = $DBH->query("SELECT dbo.VehicleJobHistory.BookingID, dbo.VehicleJobHistory.DriverID FROM dbo.Vehicle INNER JOIN dbo.VehicleJobHistory ON dbo.Vehicle.VehicleID = dbo.VehicleJobHistory.VehicleID WHERE TimeJobRequired > '09/03/2013' AND VehicleID = $VehicleID");

this returns an array of bookingID's and DriverID's from the VehicleJobHistory table:

$STH = $DBH->query("SELECT BookingID, DriverID FROM dbo.VehicleJobHistory WHERE TimeJobRequired > "02/03/2013" AND VehicleID = $VehicleID");

Ive tried virtually every other combination i could come up with, but as soon as i add a join into my query i dont seem to get anything to work.

If anyone has experience with this issue or can point me in the right direction I would be most appreciative.

Thanks in advance!

Upvotes: 0

Views: 369

Answers (1)

Mike
Mike

Reputation: 521

The SQL needed to be a left join.

SELECT dbo.VehicleJobHistory.BookingID
    , dbo.VehicleJobHistory.DriverID
FROM dbo.Vehicle
LEFT JOIN dbo.VehicleJobHistory
    ON dbo.Vehicle.VehicleID = dbo.VehicleJobHistory.VehicleID
        AND dbo.VehicleJobHistory.TimeJobRequired > '09/03/2013'
WHERE dbo.Vehicle.VehicleID = $VEHICLEID

Upvotes: 1

Related Questions