Reputation: 51
I can't get this mysql join query to produce the desired result
The structure for the three tables.
Booking
CREATE TABLE `booking` (
`bookingID` int(11) NOT NULL AUTO_INCREMENT,
`receipt_no` int(11) NOT NULL,
`client` varchar(32) NOT NULL,
`operator` varchar(32) NOT NULL,
`discount` int(11) NOT NULL,
`total_amount` int(64) NOT NULL,
`amount_paid` int(32) NOT NULL,
`balance` int(32) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`bookingID`)
ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=latin1
Jobtitle
CREATE TABLE `jobtitle` (
`jobtitleID` int(11) NOT NULL AUTO_INCREMENT,
`jobtitle` varchar(255) NOT NULL,
`quantity` int(11) NOT NULL,
`amount` varchar(255) NOT NULL,
`jobtypeID` int(11) NOT NULL,
`bookingID` int(11) NOT NULL,
PRIMARY KEY (`jobtitleID`)
ENGINE=InnoDB AUTO_INCREMENT=463 DEFAULT CHARSET=latin1
First_graphics_debtors
CREATE TABLE `first_graphics_debtors`
`id` int(11) NOT NULL AUTO_INCREMENT,
`receipt_no` int(11) NOT NULL,
`date_paid` date NOT NULL,
`old_balance` int(32) NOT NULL,
`debtor_amount_paid` int(32) NOT NULL,
`new_balance` int(32) NOT NULL,
PRIMARY KEY (`id`)
ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
After inserting few data
Booking
+-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+
| bookingID | receipt_no | client | operator | discount | total_amount | amount_paid | balance | date |
+-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+
| 205 | 156872940 | Osaro | Obi | 10 | 156380 | 135000 | 5742 | 2012-05-15 |
| 206 | 227349168 | Amaka | Stephen | 4 | 73250 | 70320 | 0 | 2012-05-15 |
| 207 | 155732278 | Aghahowa | Ibori | 0 | 116836 | 15000 | 101836 | 2012-05-15 |
| 208 | 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | 2012-05-15 |
+-----------+------------+----------+----------+----------+--------------+-------------+---------+------------+
Jobtitle
+------------+---------------------------+----------+--------+-----------+-----------+
| jobtitleID | jobtitle | quantity | amount | jobtypeID | bookingID |
+------------+---------------------------+----------+--------+-----------+-----------+
| 454 | A1 Full Colour | 10 | 4334 | 1 | 205 |
| 455 | Complementry Card | 20 | 5652 | 2 | 205 |
| 456 | A4 Printout (graphics)B/W | 25 | 2930 | 4 | 206 |
| 457 | Scan | 2 | 4334 | 5 | 207 |
| 458 | A4 Full Colour | 199 | 500 | 3 | 207 |
| 459 | ID Card | 2 | 4334 | 2 | 207 |
| 460 | A3 Full Colour | 10 | 4334 | 3 | 208 |
| 461 | Flex Banner | 20 | 2930 | 2 | 208 |
| 462 | A2 Single Colour | 199 | 650 | 1 | 208 |
+------------+---------------------------+----------+--------+-----------+-----------+
First_graphics_debtors
+----+------------+------------+-------------+--------------------+-------------+
| id | receipt_no | date_paid | old_balance | debtor_amount_paid | new_balance |
+----+------------+------------+-------------+--------------------+-------------+
| 7 | 156872940 | 2012-05-15 | 5742 | 5000 | 742 |
| 8 | 156872940 | 2012-05-15 | 5742 | 5742 | 0 |
| 9 | 753263343 | 2012-05-15 | 152161 | 152161 | 0 |
| 13 | 753263343 | 2012-05-15 | 152161 | 14524 | 137637 |
| 14 | 753263343 | 2012-05-15 | 152161 | 2000 | 150161 |
| 15 | 753263343 | 2012-05-15 | 152161 | 1000 | 151161 |
+----+------------+------------+-------------+--------------------+-------------+
When I run this query:
SELECT `booking`.`receipt_no`, `client`, `operator`, `discount`, `total_amount`,
`amount_paid`, `balance`, `date`, `jobtitle`, `quantity`, `amount`,
`date_paid`, `old_balance`, `debtor_amount_paid`, `new_balance`
FROM (`booking`)
JOIN `jobtitle` ON `jobtitle`.`bookingID` = `booking`.`bookingID`
JOIN `first_graphics_debtors`
ON `first_graphics_debtors`.`receipt_no` = `booking`.`receipt_no`
WHERE `booking`.`receipt_no` = '753263343'
AND `first_graphics_debtors`.`receipt_no` = '753263343'
GROUP BY `jobtitle`.`quantity`
I get this output:
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
| receipt_no | client | operator | discount | total_amount | amount_paid | balance | date | jobtitle | quantity | amount | date_paid | old_balance | debtor_amount_paid | new_balance |
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
| 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | 2012-05-15 | A3 Full Colour | 10 | 4334 | 2012-05-15 | 152161 | 152161 | 0 |
| 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | 2012-05-15 | Flex Banner | 20 | 2930 | 2012-05-15 | 152161 | 152161 | 0 |
| 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | 2012-05-15 | A2 Single Colour | 199 | 650 | 2012-05-15 | 152161 | 152161 | 0 |
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
Data on the columns below are repeated three times, instead of getting data from the four rows relating to the receipt_no
date_paid, old_balance, debtor_amount_paid, new_balance
Expected result
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
| receipt_no | client | operator | discount | total_amount | amount_paid | balance | date | jobtitle | quantity | amount | date_paid | old_balance | debtor_amount_paid | new_balance |
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
| 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | 2012-05-15 | A3 Full Colour | 10 | 4334 | 2012-05-15 | 152161 | 152161 | 0 |
| 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | 2012-05-15 | Flex Banner | 20 | 2930 | 2012-05-15 | 152161 | 14524 | 137637 |
| 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | 2012-05-15 | A2 Single Colour | 199 | 650 | 2012-05-15 | 152161 | 2000 | 150161 |
+------------+--------+----------+----------+--------------+-------------+---------+------------+------------------+----------+--------+------------+-------------+--------------------+-------------+
Though,the expected output not complete, but maybe you can get the picture from the data on debtor_amount_paid and new_balance
Upvotes: 3
Views: 178
Answers (3)
Reputation: 39393
You might want to use FULL JOIN to align those joined rows, but since there's no FULL JOIN on MySQL, you have to contend with UNION:
Code compacted to avoid scrollbars:
select bx.*, null as sepy, booking_particulars.*
from booking bx
join (
select same_n,
max(Receipt_No) as Receipt_No, max(Quantity) as Quantity,
max(Amount) as Amount,
null as sepx, -- separator
max(Date_Paid) as Date_Paid, max(Old_Balance) as Old_Balance,
max(Debtor_Amount_Paid) as Debtor_Amount_Paid,
max(New_Balance) as New_Balance
from (
select *
from (
select b.Receipt_No, j.Quantity, j.Amount,
null as Date_Paid, null as Old_Balance, null as Debtor_Amount_Paid,
null as New_Balance, @j_rn := @j_rn + 1 as same_n
from (booking b, (select @j_rn := 0) as vars)
join jobtitle j ON j.bookingID = b.bookingID
where b.receipt_no = '753263343'
order by j.JobTitleId
) as jx
union all
select * from (
select f.Receipt_No, null as Quantity, null as Amount,
f.Date_Paid, f.Old_Balance, f.Debtor_Amount_Paid,
f.New_Balance, @f_rn := @f_rn + 1 as same_n
from (first_graphics_debtors f, (select @f_rn := 0) as vars)
where f.receipt_no = '753263343'
order by f.id
) as fx
) as z
group by same_n order by same_n
) as booking_particulars on booking_particulars.receipt_no = bx.receipt_no
Output:
| BOOKINGID | RECEIPT_NO | CLIENT | OPERATOR | DISCOUNT | TOTAL_AMOUNT | AMOUNT_PAID | BALANCE | DATE | SEPY | SAME_N | QUANTITY | AMOUNT | SEPX | DATE_PAID | OLD_BALANCE | DEBTOR_AMOUNT_PAID | NEW_BALANCE |
|-----------|------------|--------|----------|----------|--------------|-------------|---------|----------------------------|--------|--------|----------|--------|--------|----------------------------|-------------|--------------------|-------------|
| 208 | 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | May, 15 2012 08:00:00-0700 | (null) | 1 | 10 | 4334 | (null) | May, 15 2012 08:00:00-0700 | 152161 | 152161 | 0 |
| 208 | 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | May, 15 2012 08:00:00-0700 | (null) | 2 | 20 | 2930 | (null) | May, 15 2012 08:00:00-0700 | 152161 | 14524 | 137637 |
| 208 | 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | May, 15 2012 08:00:00-0700 | (null) | 3 | 199 | 650 | (null) | May, 15 2012 08:00:00-0700 | 152161 | 2000 | 150161 |
| 208 | 753263343 | Chaka | Stephen | 10 | 231290 | 56000 | 152161 | May, 15 2012 08:00:00-0700 | (null) | 4 | (null) | (null) | (null) | May, 15 2012 08:00:00-0700 | 152161 | 1000 | 151161 |
Live test and query progression here: http://www.sqlfiddle.com/#!2/d8d3b/45
Upvotes: 1
Reputation: 753615
I fully agree with what Travesty3 said about table aliases and backticks.
Your problem is, I think, that you are trying to combine what 'should' be two separate queries into one. For any given Booking, you can have an arbitrary number of Job Title entries, and completely independently of the Job Title information, you can have an arbitrary number of First Graphics Debtors entries. In the sample data for the sample Receipt 753263343, you have one Booking, three Job Title entries, and 4 First Graphic Debtors entries.
By default (without a GROUP BY clause), you would get 12 entries corresponding to the cross-product of the three sets of rows (1 x 3 x 4).
The GROUP BY in MySQL is different from most other DBMS; Standard SQL would not allow you to omit the non-aggregate columns from the GROUP BY clause (and all the columns are non-aggregate).
What I think you're after is an output result set similar to (using FGD as an abbreviation for First Graphics Debtors):
+-------------------+---------------------+---------------+
| Data from Booking | Data from Job Title | Data from FGD |
+-------------------+---------------------+---------------+
| Booking ID 208... | Job Title ID 460... | FGD ID 9 |
| Booking ID 208... | Job Title ID 461... | FGD ID 13 |
| Booking ID 208... | Job Title ID 462... | FGD ID 14 |
| Booking ID 208... | ...?????????????... | FGD ID 15 |
+-------------------+---------------------+---------------+
The most plausible set of values for the question marks is probably a set of NULLs.
You would probably be better off, though, using two separate queries, one to fetch the Job Title information, one to fetch the FGD information (and only one of the two needs to return the Booking information). However, you then have to do some presentation work in your application code (which is where presentation work should be done, but that's a different discussion).
So, for the faint of heart, I recommend using two queries. For the brave, read on...
For the Brave
Update: The material down to TDQD — including the Testing is mildly erroneous.
First off, let me state: if that is what you're after, then it is relatively hard work to do, though it can be done. Secondly, it is a heap simpler with OLAP functions in the DBMS. With MySQL (and some other DBMS), you can use temporary tables to hold the intermediate results.
You join Booking and Job Title on Booking ID. You join Booking and FGD on Receipt No. Suppose we create two intermediate result sets:
SequencedJobTitles(Booking ID, Job_Seq_No)
SequencedReceipts(Receipt No, Rec_Seq_No)
where the job sequence number in the first goes from 1..N for each Booking ID, and the receipt sequence number goes from 1..M for each Receipt No.
Now we can write:
SELECT B.*, J.*, R.*
FROM Booking AS B
LEFT JOIN (SELECT J1.*, J2.Job_Seq_No
FROM SequencedJobTitles AS J2
JOIN JobTitle AS J1 ON J2.BookingID = J1.BookingID
) AS J
ON B.BookingID = J.BookingID
LEFT JOIN (SELECT R1.*, R2.Rec_Seq_No
FROM SequencedReceipts AS R2
JOIN First_Graphics_Debtors AS R1 ON R1.Receipt_No = R2.Receipt_No
) AS R
WHERE J.Job_Seq_No = R.Rec_Seq_No
AND B.Receipt_No = 753263343
ORDER BY B.BookingID, COALESCE(J2.Job_Seq_No, R2.Rec_Seq_No);
Yes, the use of * is lazy; I'd refine it to list the exact list of columns that I wanted in a production query. For debugging, it ensures you see all the data, which is probably helpful.
How to create the two intermediate result sets?
In the absence of OLAP functionality, you could generate the SequencedJobTitles with:
SELECT J1.BookingID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID <= J2.JobTitleID
GROUP BY J1.BookingID;
Similarly for the SequencedReceipts with:
SELECT R1.Receipt_No, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID <= R2.ID
GROUP BY R1.Receipt_No;
These expressions use a self-join with a non-equijoin, which is moderately expensive. You'd probably want to get a restriction to just the relevant Booking ID or Receipt Numbers into the sub-queries if the tables are big. Note that you can test these sub-queries in isolation; this is a good idea for TDQD (Test Driven Query Design).
However, we can simply slot these subqueries into the main query developed before:
SELECT B.*, J.*, R.*
FROM Booking AS B
LEFT JOIN (SELECT J1.*, J2.Job_Seq_No
FROM (SELECT J1.BookingID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID <= J2.JobTitleID
GROUP BY J1.BookingID
) AS J2
JOIN JobTitle AS J1 ON J2.BookingID = J1.BookingID
) AS J
ON B.BookingID = J.BookingID
LEFT JOIN (SELECT R1.*, R2.Rec_Seq_No
FROM (SELECT R1.Receipt_No, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID <= R2.ID
GROUP BY R1.Receipt_No
) AS R2
JOIN First_Graphics_Debtors AS R1 ON R1.Receipt_No = R2.Receipt_No
) AS R
ON J.Job_Seq_No = R.Rec_Seq_No
WHERE B.Receipt_No = 753263343
ORDER BY B.BookingID, COALESCE(J2.Job_Seq_No, R2.Rec_Seq_No);
Caution: untested SQL!
Update: This warning was not put there idly. I don't trust myself to write complex queries accurately first time, no matter how hard I try. And I'd not tried to produce the outline result I showed before (I've always used the two separate queries and combine the data in the presentation tool approach instead), so mistakes were, I suppose, inevitable.
TDQD — including the Testing
Real TDQD shows that the sequence queries are close, but not correct. Although I changed the <= conditions into >= conditions, that is more cosmetic than substantive. The correct queries are as follows:
SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID;
SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID;
These two results can each be joined back with the corresponding table, but the join condition is different from what I originally wrote:
SELECT J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
ORDER BY J4.BookingID, J3.Job_Seq_No;
SELECT R4.*, R3.Rec_Seq_No
FROM (SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID
) AS R3
JOIN First_Graphics_Debtors AS R4
ON R3.Receipt_No = R4.Receipt_No
AND R3.ID = R4.ID
ORDER BY R4.Receipt_No, R3.Rec_Seq_No;
And then you can run this query:
SELECT B.*, J.*, R.*
FROM Booking AS B
LEFT JOIN (SELECT J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
) AS J
ON B.BookingID = J.BookingID
LEFT JOIN (SELECT R4.*, R3.Rec_Seq_No
FROM (SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID
) AS R3
JOIN First_Graphics_Debtors AS R4
ON R3.Receipt_No = R4.Receipt_No
AND R3.ID = R4.ID
) AS R
ON B.Receipt_No = R.Receipt_No
WHERE B.Receipt_No = 753263343
AND J.Job_Seq_No = R.Rec_Seq_No
ORDER BY B.BookingID, NVL(J.Job_Seq_No, R.Rec_Seq_No);
Unfortunately, it produces just three rows on the sample data; it omits the last of the 4 receipt numbers. This is why I do TDQD; when I take short cuts, I find there are problems.
The structure of the last query needs to be such that the J and R sub-queries are joined using a FULL OUTER JOIN (in general), and therefore they need either the Receipt Number or Booking ID in common. I chose to add the Receipt Number to the JobTitle information; it would work fine with the Booking ID added to the First Graphics Debtors information; symmetry suggests doing both, but that might also be regarded as overkill.
SELECT B.Receipt_No, J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
JOIN Booking AS B
ON B.BookingID = J4.BookingID
ORDER BY J4.BookingID, J3.Job_Seq_No;
Then, combining the two sequenced lists with a FULL OUTER JOIN:
SELECT *
FROM (SELECT B.Receipt_No, J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
JOIN Booking AS B
ON B.BookingID = J4.BookingID
) AS J
FULL OUTER JOIN
(SELECT R4.*, R3.Rec_Seq_No
FROM (SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID
) AS R3
JOIN First_Graphics_Debtors AS R4
ON R3.Receipt_No = R4.Receipt_No
AND R3.ID = R4.ID
) AS R
ON J.Receipt_No = R.Receipt_No AND J.Job_Seq_No = R.Rec_Seq_No
ORDER BY NVL(J.Receipt_No, R.Receipt_No), NVL(J.Job_Seq_No, R.Rec_Seq_No);
Final Sample Query
And finally embedding that into the main query gives (a very verbose version of) the desired result:
SELECT B.*, JR.*
FROM Booking AS B
JOIN (SELECT NVL(J.Receipt_No, R.Receipt_No) AS Rec_No,
NVL(J.Job_Seq_No, R.Rec_Seq_No) AS Seq_No,
J.*, R.*
FROM (SELECT B.Receipt_No, J4.*, J3.Job_Seq_No
FROM (SELECT J1.BookingID, J1.JobTitleID, COUNT(*) AS Job_Seq_No
FROM JobTitle AS J1
JOIN JobTitle AS J2
ON J1.BookingID = J2.BookingID
WHERE J1.JobTitleID >= J2.JobTitleID
GROUP BY J1.BookingID, J1.JobTitleID
) AS J3
JOIN JobTitle AS J4
ON J3.BookingID = J4.BookingID
AND J3.JobTitleID = J4.JobTitleID
JOIN Booking AS B
ON B.BookingID = J4.BookingID
) AS J
FULL OUTER JOIN
(SELECT R4.*, R3.Rec_Seq_No
FROM (SELECT R1.Receipt_No, R1.ID, COUNT(*) AS Rec_Seq_No
FROM First_Graphics_Debtors AS R1
JOIN First_Graphics_Debtors AS R2
ON R1.Receipt_No = R2.Receipt_No
WHERE R1.ID >= R2.ID
GROUP BY R1.Receipt_No, R1.ID
) AS R3
JOIN First_Graphics_Debtors AS R4
ON R3.Receipt_No = R4.Receipt_No
AND R3.ID = R4.ID
) AS R
ON J.Receipt_No = R.Receipt_No AND J.Job_Seq_No = R.Rec_Seq_No
) AS JR
ON B.Receipt_No = JR.Rec_No
WHERE B.Receipt_No = 753263343
ORDER BY B.BookingID, Seq_No;
With a very much more selective column list like this:
SELECT B.BookingID AS Booking,
B.Receipt_No AS Receipt,
B.Client AS Client,
JR.JobTitleID AS Title
JR.JobTitle AS JobTitle,
JR.Old_Balance AS Old_Balance,
JR.New_Balance AS New_Balance
FROM Booking AS B
...
Final Sample Output
The result data I obtained from the sample data was:
booking receipt client title jobtitle old_balance new_balance
208 753263343 Chaka 460 A3 Full Colour 152161 0
208 753263343 Chaka 461 Flex Banner 152161 137637
208 753263343 Chaka 462 A2 Single Colour 152161 150161
208 753263343 Chaka 152161 151161
The only residual issue is whether MySQL support the FULL OUTER JOIN notation. If not, you can use a RIGHT OUTER JOIN on this sample data (where there are fewer rows in the Job Title information than in the FGD information).
Using OLAP functions
Update: This is based on the now known to be erroneous queries. Analagous changes must be made here to get it working as required.
With OLAP functions, I think you'd be able to create the sequenced result sets like this:
SELECT BookingID,
ROW_NUMBER() OVER (PARTITION BY BookingID ORDER BY JobTitleID) AS Job_Seq_No
FROM JobTitle;
SELECT Receipt_No,
ROW_NUMBER() OVER (PARTITION BY Receipt_No ORDER BY ID) AS Rec_Seq_No
FROM First_Graphics_Debtors;
However, I'm much less confident of this part of the SQL. However, the resulting big query is simpler:
SELECT B.*, J.*, R.*
FROM Booking AS B
LEFT JOIN (SELECT J1.*, J2.Job_Seq_No
FROM (SELECT BookingID,
ROW_NUMBER()
OVER (PARTITION BY BookingID ORDER BY JobTitleID)
AS Job_Seq_No
FROM JobTitle
) AS J2
JOIN JobTitle AS J1 ON J2.BookingID = J1.BookingID
) AS J
ON B.BookingID = J.BookingID
LEFT JOIN (SELECT R1.*, R2.Rec_Seq_No
FROM (SELECT Receipt_No,
ROW_NUMBER() OVER (PARTITION BY Receipt_No ORDER BY ID)
AS Rec_Seq_No
FROM First_Graphics_Debtors
) AS R2
JOIN First_Graphics_Debtors AS R1 ON R1.Receipt_No = R2.Receipt_No
) AS R
WHERE J.Job_Seq_No = R.Rec_Seq_No
AND B.Receipt_No = 753263343
ORDER BY B.BookingID, COALESCE(J2.Job_Seq_No, R2.Rec_Seq_No);
Caution: more untested SQL!
Upvotes: 0
Reputation: 14469
I'm fairly certain that your problem is because of your GROUP BY clause. Not sure what your aim was in using that, but try without it. If you get duplicate rows, try using SELECT DISTINCT. If your aim was to order the results based on that column, then use ORDER BY.
Also, specifying the receipt_no twice in the WHERE clause is redundant. Those two tables are already joined by that column, so filtering it in one table is all you need to do. And the backticks aren't really necessary except for some special exceptions, like spaces or commas in the column name, or columns named the same as reserved words (may be a few others as well). The only column of yours that looks like it needs backticks is the date column, but even excluding them on that column, you should still be fine. I just find that backticks everywhere make the query longer and harder to read. They don't hurt anything by being there, so you can leave them if that's your preference, but personally, I'm not a fan.
I rewrote your query with my modifications mentioned above, plus I gave the tables aliases to further shorten it. This doesn't improve performance or anything, just makes it easier to read IMO:
SELECT DISTINCT
b.receipt_no, client, operator, discount, total_amount,
amount_paid, balance, `date`, jobtitle, quantity,
amount, date_paid, old_balance, debtor_amount_paid, new_balance
FROM
booking b
INNER JOIN jobtitle jt ON jt.bookingID = b.bookingID
INNER JOIN first_graphics_debtors fgd ON fgd.receipt_no = b.receipt_no
WHERE
b.receipt_no = '753263343'
ORDER BY
jt.quantity
Upvotes: 1
Related Questions
- Failing to get the desired results for a RIGHT JOIN
- MYSQL join query not working as expected
- mysql query not working as expected complex
- What join do i need? Why doesn't my query work?
- Unable to figure this join giving wrong results
- Join in Mysql doesn't produce the correct output
- MySQL query with 2 JOINs not returning expected result
- SQL query doesn't seem to join correctly
- JOIN query not behaving as expected
- SQL query to get expected result