LEFT JOIN filter certain Null values based on some condition MYSQL

Question

Is it possible to filter certain null values after left join using some conditions in mysql

my sqlfiddle for reference

i want to return the tables with their status for a particular booked datetime i have added the date condition but its returning rows of booking of other dates with the status

is my table structure is wrong or is their a solution for it....

expected output for date December, 09 2012 00:00:00+0000

TABLE_ID FLOOR_ID TABLE_STATUS  BOOKING_ID         D
1         1        seated            35      December, 09 2012 00:00:00+0000
2         1        free           (null)    (null)
3         1        free           (null)    (null)
4         1        free           (null)    (null)
5         1        free           (null)    (null)

but i am getting other nulls from booking table

 TABLE_ID FLOOR_ID TABLE_STATUS BOOKING_ID  D
   1     1           seated       35    December, 09 2012 00:00:00+0000
   2     1                        (null)    (null)
   2     1                        (null)    (null)
   3     1            free        (null)    (null)
   4     1            free        (null)    (null)
   5     1            free        (null)    (null)

can you give a correct result in this case?

TCFDS
– TCFDS

2012-12-09 18:02:18 +00:00
Commented Dec 9, 2012 at 18:02 — TCFDS
– TCFDS, Commented Dec 9, 2012 at 18:02

Laurence · Accepted Answer · 2012-12-09 18:27:19Z

1

You can use Group By to do this, but it isn't really clear what you want in the case of multiple matches for one table. You can use a combination of left outer join and inner join to ignore the unwanted booking_table rows:

Select
  t.table_id,
  t.floor_id,
  coalesce(Max(bt.table_status),'free') as table_status,
  max(bt.booking_id) as booking_id,
  max(bt.date) as d
From
  ttable as t
    Left Outer Join (
      Select
        bt.table_id,
        bt.table_status,
        b.booking_id,
        b.date
      From
        booking_table as bt 
          Inner Join
        booking As b
          On b.booking_id = bt.booking_id And b.date = '2012-12-09'
    ) bt On bt.table_id = t.table_id
Where
  t.floor_id = 1
Group By
  t.table_id,
  t.floor_id

You could use a right outer join to avoid the nesting, but it's not generally recommended:

Select
  t.table_id,
  t.floor_id,
  coalesce(Max(bt.table_status),'free') as table_status,
  max(b.booking_id) as booking_id,
  max(b.date) as d
From
  booking_table as bt
    Inner Join
  booking b
    On b.booking_id = bt.booking_id And b.date = '2012-12-09'
    Right Outer Join
  ttable as t
    On bt.table_id = t.table_id
Where
  t.floor_id = 1
Group By
  t.table_id,
  t.floor_id

http://sqlfiddle.com/#!2/cb03b/20

edited Dec 9, 2012 at 18:27

answered Dec 9, 2012 at 17:58

Laurence

11k1 gold badge28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fuzionpro Over a year ago

i want all table_ids with status for example if i have 5 restaurant tables i need the get status for the given date time

Laurence Over a year ago

@fuzionpro I think I get what you're after now, see new examples.

Collectives™ on Stack Overflow

LEFT JOIN filter certain Null values based on some condition MYSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related