• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

MySQL problem

have set up a mysql database, which I would like to use in conjuction with Joomla

Basically I have the following tables

it_info_store
----------------------
id
cat - references it_categores (id)
title
info
createdDate
createdUserID
lastEditDate
lastEditDateUserID


it_categories
-----------------------------
id
category


jos_users (joomla user db)
---------------------------------
id
name
various others

I want to be able to run a query to return the name from the jos_users table for both the ids in the it_info_store.  I can only get one or the other.  I used the following, but I could only see the entries that had a last edit date. Obviously not all entries will have been edited.

SELECT c.category, i.title, i.info, i.createdDate, i.lastEditDate, i.lastEditUserId, i.id, u.name FROM it.it_info_store i, it.it_categories c, joomla.jos_users u WHERE i.cat = c.id AND i.userid = u.id AND i.lastEditUserId = u.id

I have messed about with joins and sub queries but do not really know where to begin with this.

Thanks!!

0
booneball
Asked:
booneball
  • 3
  • 3
1 Solution
 
raysonleeCommented:
SELECT c.category, i.title, i.info, i.createdDate, i.lastEditDate, i.lastEditUserId, i.id, u.name FROM it.it_info_store i, it.it_categories c, joomla.jos_users u WHERE i.cat = c.id AND i.userid = u.id AND (i.lastEditUserId = u.id OR i.createdUserID = u.id)
0
 
raysonleeCommented:
Shouldn't it be i.CreatedUserID instead of i.userid?
SELECT c.category, i.title, i.info, i.createdDate, i.lastEditDate, i.lastEditUserId, i.id, u.name FROM it.it_info_store i, it.it_categories c, joomla.jos_users u WHERE i.cat = c.id AND (i.createdUserid = u.id OR i.lastEditUserId = u.id)
0
 
booneballAuthor Commented:
That was quick!

I have tried this but it only gives me the created name.  I need .... as createdName and ..... as lastEditName
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
booneballAuthor Commented:
yes that it correct with the user ids sorry.  I retyped it when I asked the question and mistyped that.  thanks
0
 
raysonleeCommented:
Sorry, it should be:
SELECT c.category, i.title, i.info, i.createdDate, i.lastEditDate, i.lastEditUserId, i.id,
(SELECT name FROM joomla.jos_users WHERE i.createUserID = joomla.jos_users.id) as createUserName,
(SELECT name FROM joomla.jos_users WHERE i.lastEditUserID = joomla.jos_users.id) as lastEditUserName FROM it.it_info_store i JOIN it.it_categories c WHERE i.cat = c.id
0
 
booneballAuthor Commented:
That works perfect.

It has saved me a lot of time!!

THANKS!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now