SQL JOIN query for Cakephp

Question

I have 2 tables, one is called items, and the other is called sliders.

I want to call all the columns where sliders.item_id = items.id , and both are published so items.published=1 and sliders.published=1. But also I want to call sliders.item_id's that are NULL.

So basicly is like a right join where i get all related record that match the constrain but also records on the sliders table that don't correspond to items table.

In few words the point is this: i want to get ALL sliders that belongs to items (sliders.item_id=items.id) AND (sliders.published=1 AND items.published=1) BUT also sliders where item_id=null.

I have made a working query, but it does not satisfy me.

select *
from items
right join sliders
on items.id = 27
and items.id = sliders.item_id
and items.published = 1
where sliders.published = 1

THE TABLES

CREATE TABLE IF NOT EXISTS `items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(30) NOT NULL,
  `item_description` text NOT NULL,
  `published` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE IF NOT EXISTS `sliders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pic_path` text NOT NULL,
  `item_id` int(11) DEFAULT NULL,
  `published` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

First i want it in basic MySQL query because i can convert in cakephp later.

Just to note - RIGHT JOINs are vanishingly rare, I assume because LEFT JOINs are more intuitive. — Strawberry, May 23 '14 at 08:53

score 1 · Answer 1 · answered May 23 '14 at 08:58

1

try this query,

SELECT *
FROM items 
RIGHT JOIN sliders
ON items.id = sliders.item_id
WHERE items.published = 1 AND sliders.published = 1

answered May 23 '14 at 08:58

ravikumar

893
1
8
12

Thank you ravikumar, but this is what i have done, i was thinking if was a better way, anyway thnx. – Joan May 23 '14 at 13:48

score 1 · Answer 2 · answered May 23 '14 at 10:31

when u got all record from table which are published from both :

$SQL = "SELECT * FROM items RIGHT JOIN sliders ON items.id=sliders.id WHERE items.published=1 AND sliders.published=1";

And when u want to get particular id record.. $id=27

$QRY = "SELECT * FROM items RIGHT JOIN sliders ON items.id=sliders.id WHERE items.published=1 AND sliders.published=1 AND items.id= $id";

and if you want to get more then one id records then... $ids="27,28,29,30,50,55";

$QRY = "SELECT * FROM items RIGHT JOIN sliders ON items.id=sliders.id WHERE items.published=1 AND sliders.published=1 AND items.id in($ids)";

in last query u fetch records which is published in both table and id from the above.

SQL JOIN query for Cakephp

2 Answers2