Created
February 17, 2014 17:44
-
-
Save hodadgists/9055431 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT t.id, e.id, e.name, e.date, e.time, t.purchase_order_id, t.section, t.row, t.seat, t.updated_at, u1.username, t.created_at, u2.username | |
FROM j_ticket t | |
INNER JOIN j_event e on e.id = t.event_id | |
INNER JOIN users u1 on u1.id = t.mod_user_id | |
INNER JOIN users u2 on u2.id = t.created_user_id | |
WHERE e.date > '2009-01-01' and t.id in | |
( | |
SELECT id FROM j_ticket WHERE sold=true AND id NOT IN | |
( | |
SELECT ticket_id FROM j_invoice_ticket WHERE ticket_id=j_ticket.id | |
) | |
) | |
OERDER BY t.created"; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I have tried this (does not seem to work and wants to pull back far too much):
$missingtickets = DB::table('j_ticket AS t')
->join('j_event AS e', 'e.id', '=', 't.event_id')
->join('users AS mu', 'mu.id', '=', 't.mod_user_id')
->join('users AS mc', 'mc.id', '=', 't.created_user_id')
->select(
't.id AS t_id', 't.eventDate', 't.purchase_order_id', 't.section', 't.row', 't.seat', 't.updated_at', 't.created_at',
'e.id AS e_id', 'e.name AS eventname','mu.username AS mod_username', 'mc.username AS created_username'
)
->where('t.eventDate', '>=', '2010-01-01')
->whereIn('t.id', function($query)
{
$query->select('j_ticket.id')
->from('j_ticket')
->where('j_ticket.sold', '=', '1')
->whereNotIn('j_ticket.id', function($query)
{
$query->select('j_invoice_ticket.ticket_id')
->from('j_invoice_ticket')
->where('j_invoice_ticket.ticket_id', '=', 'j_ticket.id');
});
})
->get();