CODEWITHSUNDEEP
mysqljoin
neilakapete
neilakapete

Reputation: 1178

MYSQL - all rows for all possibilities from table 1 and table 2

Table users

id, name
1, Jay Bob
2, An Other

Table pages

id, name, html
1, 'Welcome', 'Welcome to this page'
2, 'Goodbye', 'Thanks for visiting'

Table user_pages ** stores user specific version of pages **

user_id, page_id, html
1,        1,      'User id 1 Welcome page'

I basically need a query that will return me the below data set - a row for every possibility even when no data exists.

Data Set 

user_id, page_id, html
1,        1,      'User is 1 Welcome page'
1,        2,      ''
2,        1,      ''
2,        2,      ''

Upvotes: 3

Views: 477

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think the clearest way is to use subqueries and cross join to create a driver table:

select driver.user_id, driver.page_id, up.html
from (select u.id as user_id, p.id as page_id
      from users u cross join
           pages p
     ) driver left outer join
     user_pages up
     on up.user_id = driver.user_id and up.page_id = driver.page_id;

It is important that the user_id and page_id in the select clause come from the driver table and not from user_pages, because the latter may be NULL.

Upvotes: 3

leonardo_assumpcao
leonardo_assumpcao

Reputation: 686

Code bellow was tested on MySQL Server 5.6
I hope / presume that your intention is near the following:

USE test;

CREATE TABLE users (
id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(80) NOT NULL);

CREATE TABLE pages (
id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
html VARCHAR(100) NOT NULL);

CREATE TABLE user_pages (
user_id INT UNSIGNED NOT NULL,
page_id INT UNSIGNED NOT NULL,
html VARCHAR(50) DEFAULT '',
PRIMARY KEY (user_id, page_id));

INSERT users (`name`) VALUES
  ('User 1'), ('User 2'), ('User 3');

INSERT pages (`name`, `html`) VALUES
  ('Welcome','Welcome to this page'),
  ('Goodbye','Thanks for visiting');

INSERT user_pages (user_id, page_id, html) VALUES
  (1,1,"First user's welcome page"),
  (1,2,"First user's goodbye page"),
  (2,1,"Second user's welcome page");

SELECT DISTINCT
  u.id  AS `u_id`,
  u.`name` AS `u_name`,
  IFNULL(x.page_id, p.id)  AS `p_id`,
  IFNULL(p.`name`,'') AS `p_name`,
  IFNULL(x.html,p.html) AS `p_html`
FROM users AS u
CROSS JOIN pages AS p
LEFT OUTER JOIN user_pages AS x
  ON (x.user_id = u.id AND x.page_id = p.id);

The code above shall return specific (if exists) or general pages for each user. You can also create a view, and query from it as if it were a normal table:

CREATE OR REPLACE VIEW `user_page_details` AS
SELECT DISTINCT
  u.id  AS `u_id`,
  u.`name` AS `u_name`,
  IFNULL(x.page_id, p.id)  AS `p_id`,
  IFNULL(p.`name`,'') AS `p_name`,
  IFNULL(x.html,p.html) AS `p_html`
FROM users AS u
CROSS JOIN pages AS p
LEFT OUTER JOIN user_pages AS x
  ON (x.user_id = u.id AND x.page_id = p.id);

Once you made it, your query would be quite simple:

SELECT * FROM user_page_details;

Or even

SELECT * FROM user_page_details WHERE u_id = 2;

Upvotes: 1

Marjeta
Marjeta

Reputation: 1141

Try this:

SELECT up.user_id, up.page_id, up.html
FROM users u,pages p
LEFT OUTER JOIN user_pages up ON u.id=up.user_id AND p.id=up.user_id

Upvotes: 0

Related Questions