Jump to content

left join or right join


jimfog

Recommended Posts

I have these 2 tables avail_apps and store_open....http://pastebin.com/L5dHH6cK

 

I want to select these rows in avail_apps that are the same with the rows in store_open...and obviously disregard the rest---from avail_apps.

 

What kind of JOIN is this..left or right?

Link to comment
Share on other sites

Could be either, depending on which table you make the left table or the right table.

can you give me an example of how I could accomplish my goal...using LEFT or RIGHT join.

What interests me more though is finding out which columns must be NULL in this case...this is what troubles most.

Link to comment
Share on other sites

If you only want rows that exist in both tables then you would use an inner join.

yes...inner join was the solution after all....nonetheless there is something else yet

This INNER JOIN gives me the available booking slots for a hair salon(for example)-since I am making an appointments app.

I must also add the criteria to exclude the already booked appointments stored in this table:

CREATE TABLE `appointments` (
  `apID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `Bookfrom` varchar(45) DEFAULT NULL 
  `bookedfor` mediumint(11) unsigned DEFAULT NULL 
  `appont_close_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `startDate` date NOT NULL,
  `startime` time NOT NULL,
  `endDate` date NOT NULL,
  `endTime` time NOT NULL,
  `apps_origin` enum('frontend','backend') NOT NULL,
  `staffID` int(11) unsigned DEFAULT NULL,
  `deleted` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`apID`),
  KEY `fk_appointments_user1_idx` (`Bookfrom`),
  KEY `bokkedfor` (`bookedfor`),
  KEY `fk_appointments_staff1_idx` (`staffID`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8

I think here we need a LEFT JOIN with the result of the inner join.

 

I can perform LEFT JOIN between the avai_apps table and(have already done that) the appointments table....but I am stuck in performing a left join between the result of the above INNER JOIN and the appointments table so that I can get the available timeslots that will take into account the schedule and the already booked app.

 

Thanks

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...