CODEWITHSUNDEEP
mysqlsqljoin
Jay Zus
Jay Zus

Reputation: 573

Multiple Join on same table with different columns

I have problems making a SQL request.

Here is my tables:

CREATE TABLE dates(  
  id INT PRIMARY KEY, 
  obj_id INT,
  dispo_date  text 
);  

CREATE TABLE option(
  id INT PRIMARY KEY, 
  obj_id INT,
  random_option INT
);

CREATE TABLE obj(  
  id INT PRIMARY KEY, 
);

and a random date that the user gives me and some options.

I'd like to select everything on both tables which correspond to an obj having his date equal to the user's date.

let's say that DATE = "22/01/2013" and OPTIONS = 3.

SELECT * FROM obj 
INNER JOIN dates
ON dates.obj_id=obj.id
INNER JOIN option
ON option.obj_id=obj.id
WHERE dates.dispo_date="22/01/2013"
AND option.random_option=3;

That just gives me everything from my obj table with, for each one, the same dates and options without filtering anything.

Can someone give me some pointers about what I'm doing wrong ?

SOLUTION:

Since everybody seemed to get what I was looking for I restarted my SQL server and since, everything works ...

Thanks for your help and sorry for the time-loss :-(

Upvotes: 0

Views: 149

Answers (3)

Guffa
Guffa

Reputation: 700152

As far as I can see, there is nothing wrong with the query.

When I try it, it returns only the obj rows where there is a corresponding date and a corresponding option.

insert into dates values
(1, 1, '22/01/2013'),
(2, 1, '23/01/2013'),
(3, 2, '22/01/2013'),
(4, 2, '23/01/2013'),
(5, 3, '23/01/2013'),
(6, 3, '24/01/2013');

insert into `option` values
(1, 1, 4),
(2, 1, 5),
(3, 2, 3),
(4, 2, 4),
(5, 3, 3),
(6, 3, 4);

insert into obj values
(1),
(2),
(3)

With this data it should filter out obj 1 because there is no option 3 for it, and filter out obj 3 because there is no date 22 for it.

Result:

ID  OBJ_ID  DISPO_DATE  RANDOM_OPTION
-------------------------------------
2   2       22/01/2013  3

Demo: http://sqlfiddle.com/#!2/a398f/1

Upvotes: 2

David W
David W

Reputation: 10184

First, I'm a little confused on which ID's map to which tables. I might respectfully suggest that the id field in DATES be renamed to date_id, the id in OPTION be renamed to option_id, and the id in obj to obj_id. Makes those relationships MUCH clearer for folks looking in through the keyhole. I'm going in a bit of a circle making sure I understand your relationships properly. On that basis, I may be understanding your problem incorrectly.

I think you have obj.id->dates.obj_id, and option.obj_id->dates.obj_id, so on that basis, I think your query has to be a bit more complicated:

This gives you object dates:

Select *
  from obj obj
  join dates d
    on obj.id=d.obj_id

This gives you user dates:

select *
  from option o
  join dates d
    on o.obj_id=d.obj_id

To get the result of objects and users having the same dates, you'd need to hook these two together:

select * 
  from (Select *
          from obj obj
          join dates d
            on obj.id=d.obj_id) a
  join (select *
          from option o
          join dates d
            on o.obj_id=d.obj_id) b
    on a.dispo_date=b.dispo_date
 where b.random=3

I hope this is useful. Good luck.

Upvotes: 1

tonyjmnz
tonyjmnz

Reputation: 536

Change your line

WHERE dates.dispo_date="22/01/2013"

for

WHERE DATE(dates.dispo_date)="22/01/2013"

Handling dates in text fields is a little tricky (also bad practice). Make sure both dates are in the same format.

Upvotes: 1

Related Questions