[SOLVED] Tricky Query!!!

[Link]

So here's the deal:

 

I have two tables (schedules_private and users).  I want to select rows of schedules_private if the week column == a particular date.  ALSO, IF there is a user_id set, I want to also join the appropriate information from the users table.  The following can give you a bit of insight of what I have tried and where I am trying to go!  Any and all help is greatly appreciated!  It may not even need cases, so just let me know!

 

SELECT

(CASE

schedules_private.user_id

WHEN

''

THEN

'schedules_private.*'

ELSE

'schedules_private.*,

users.first_name,

users.last_name'

END)

 

FROM schedules_private,

users

 

WHERE

 

CASE

schedules_private.user_id

WHEN

''

THEN

schedules_private.week='2009-07-06'

ELSE

schedules_private.week='2009-07-06'

AND users.id = schedules_private.user_id

 

ORDER BY schedules_private.day ASC, users.last_name ASC, users.first_name ASC

[Zane]

is would be better if we saw a layout of your table.......using DESCRIBE

[Link]

--
-- Table structure for table `schedules_private`
--

CREATE TABLE `schedules_private` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `week` date NOT NULL,
  `day` int(1) unsigned NOT NULL default '0',
  `user_id` int(11) unsigned NOT NULL default '0',
  `time` time NOT NULL,
  `group_name` text NOT NULL,
  `contact` text NOT NULL,
  `size` text NOT NULL,
  `fee` text NOT NULL,
  `notes` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(100) NOT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `athena` varchar(20) NOT NULL,
  `phone` varchar(100) NOT NULL,
  `phone_type` varchar(4) NOT NULL,
  `phone_preference` varchar(100) NOT NULL,
  `prefers_call` tinyint(1) NOT NULL,
  `prefers_email` tinyint(1) NOT NULL,
  `prefers_text` tinyint(1) NOT NULL,
  `about_me` text NOT NULL,
  `activities` text NOT NULL,
  `interests` text NOT NULL,
  `active` tinyint(1) NOT NULL default '0',
  `sid` text NOT NULL,
  `timestamp` datetime NOT NULL,
  `userlevel` int(11) unsigned NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `athena` (`athena`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

[Zane]

SELECT *
FROM schedules_private sch, users
INNER JOIN users ON sch.user_id = users.id
WHERE sch.week = NOW()

[Link]

Error

SQL query:

 

SELECT *

FROM schedules_private sch, users

INNER JOIN users ON sch.user_id = users.id

WHERE sch.week =  '2009-07-06'

LIMIT 0 , 30

 

MySQL said:

 

#1066 - Not unique table/alias: 'users'

[Zane]

Error

SQL query:

 

SELECT *

FROM schedules_private sch, users

INNER JOIN users ON sch.user_id = users.id

WHERE sch.week =  '2009-07-06'

LIMIT 0 , 30

 

MySQL said:

 

#1066 - Not unique table/alias: 'users'

 

SELECT *

FROM schedules_private sch

INNER JOIN users u ON sch.user_id = u.id

WHERE sch.week =  '2009-07-06'

LIMIT 0 , 30

 

try that

[Link]

That returns rows only if sch.user_id is set.  If there isn't a sch.user_id (i.e. = 0), I want wants sch, but clearly there won't be any corresponding data in u.

 

Make sense?

[Zane]

try a LEFT JOIN or OUTER JOIN....I'm not sure of their differences but...play around with different joins and you should get a positive result at some point

 

SELECT *

FROM schedules_private sch

LEFT JOIN users u ON sch.user_id = u.id

WHERE sch.week =  '2009-07-06'

 

 

SELECT *

FROM schedules_private sch

OUTER JOIN users u ON sch.user_id = u.id

WHERE sch.week =  '2009-07-06'

 

 

SELECT *

FROM schedules_private sch

JOIN users u ON sch.user_id = u.id

WHERE sch.week =  '2009-07-06'

[Link]

LEFT JOIN wins!

 

Thanks a lot!!!