CODEWITHSUNDEEP
mysqlsql
fuzionpro
fuzionpro

Reputation: 601

LEFT JOIN filter certain Null values based on some condition MYSQL

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

my sqlfiddle for reference

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

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)

Upvotes: 1

Views: 1726

Answers (1)

Laurence
Laurence

Reputation: 10976

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

Upvotes: 1

Related Questions