MySQL foreign key request returning empty

Question

I have three tables

Let a demo_organization ;

 +--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | org_name | varchar(100) | NO | | NULL | | | org_type | varchar(100) | NO | | NULL | | | abn_acn_no | varchar(100) | NO | | NULL | | | org_url | varchar(120) | NO | | NULL | | | notes | longtext | NO | | NULL | | | city | varchar(100) | YES | | NULL | | 

The second is demo_user

 mysql> desc demo_user ; +--------------------+----------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+----------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | user_name | varchar(100) | NO | | NULL | | | first_name | varchar(100) | NO | | NULL | | | middle_name | varchar(100) | NO | | NULL | | | last_name | varchar(100) | NO | | NULL | | | image | varchar(10000) | YES | | NULL | | | password | varchar(80) | NO | | NULL | | | role | varchar(20) | NO | | NULL | | | org_name_id | int(11) | NO | MUL | NULL | | | timezone_id | int(11) | NO | MUL | NULL | | 

And the third is demo_meeting; looks like

 mysql> desc demo_meeting ; +--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(200) | NO | | NULL | | | meetingID | varchar(50) | NO | | NULL | | | venue_id | int(11) | YES | MUL | NULL | | | status | int(11) | YES | | NULL | | | recurring_time | varchar(50) | NO | | NULL | | | attendee_passwd | varchar(100) | NO | | NULL | | | moderator_passwd | varchar(100) | NO | | NULL | | | date_created | datetime | NO | | NULL | | | start_time | varchar(100) | NO | | NULL | | | end_time | varchar(100) | NO | | NULL | | | meeting_duration | varchar(100) | NO | | NULL | | | meeting_datetime | datetime | YES | | NULL | | | timezone | varchar(50) | NO | | NULL | | | reminder | tinyint(1) | NO | | NULL | | | duration | varchar(20) | NO | | NULL | | | created_by_id | int(11) | NO | MUL | NULL | | 

In the third table, created_by_id refers to the user of the demo_user table (foreign key)

And org_name_id (demo_user table) refers to demo_organization (foreign key to demo_organization table)

Update

 mysql> desc demo_meetingroom; +------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | NO | | NULL | | | type | varchar(200) | NO | | NULL | | | expired_on | varchar(100) | NO | | NULL | | | user_id | int(11) | YES | MUL | NULL | | +------------+--------------+------+-----+---------+----------------+ 

Now I'm trying to get all the meetings for a specific organization.

I am writing a request like

 select meetingID ,type from demo_meeting as dm ,demo_meetingroom as dmr Where venue_id IS NOT NULL and dm.name = dmr.name AND created_by_id IN ( SELECT id from demo_user WHERE org_name_id IN ( SELECT id from demo_organization where id = (SELECT org_name_id from demo_user WHERE user_name = 'God') ) ); 

Unfortunately, it returns an Empty set to me (But there is a value)

Please help me, what can I do wrong?

Answer 1

JOIN three tables instead of these IN . Sort of:

 SELECT dm.meetingID, dm.type FROM demo_meeting dm INNER JOIN demo_user u ON dm.created_by_id = u.id INNER JOIN demo_organization org ON u.org_name_id = org.id WHERE u.user_name = 'God' AND dm.venue_id IS NOT NULL 

Answer 2

Much better if you can use JOIN .

 SELECT a.meetingID, d.type FROM demo_meeting a LEFT JOIN demo_user b ON a.created_by_id = b.id LEFT JOIN demo_organization c ON b.org_name_id = c.id LEFT JOIN demo_meetingroom d ON a.name = d.name WHERE a.venue_id IS NOT NULL AND b.username = 'GOD'