how to get multiple mysql table lookups
I have a unique situation, i have 3 tables and one description table id like to pull info from.
i was looking at join and im new to join mysql command and i do not understand how to make it work with what i need
im not saying it should be used im open to other ways it just seamed on trying to figure this out it was the logical choice to use.
here my problem
i have tables denoted by - with there field names under them as
what im looking to do it:
i need to look at Contact table and look for username and password if it is not found look in Client for username and password if that is not found look in EndUser for Login and Password, i know i need to do 'WHERE `Active` = '1' on all the tables someplace too.
my farther problem is if its the first two (Contact and Client) tables i need the info joined by ContactID and ClientID so i can pull the info Company_name and Contact Name etc from appropriate tables.
if the look up is EndUser i need just that tables info and the reference of UserTypeID description form the UserTypeID form EndUser along with the FirstName and LastName.
Im trying to make this so if a user logs in the admin(EndUser) and client can log into there accounts, but the tables i have are in 3 different spots(and i cant change them) i would have made it one table for login info and then broke up the tables in client/contact/admin using ids for each) but i didnt make the tables so im stuck with looking them up this way.
so whats the best way to do this please in MySQLi with php i really only need the sql statement but i wont complain if the php is there too
Thank you in advance for any code or help you may provide.
Johnny
i was looking at join and im new to join mysql command and i do not understand how to make it work with what i need
im not saying it should be used im open to other ways it just seamed on trying to figure this out it was the logical choice to use.
here my problem
i have tables denoted by - with there field names under them as
- Contact
Active
username
password
ContactID
ClientID
Contact Name
EmailAddress
Secretary Name
EmailAddress2
- Client
Active
username
password
ClientID
Company Name
EndUserID
Created
Modified
- EndUser
Active
Login
Password
EndUserID
UserTypeID
FirstName
LastName
-UserType
UserTypeID
Descriptionwhat im looking to do it:
i need to look at Contact table and look for username and password if it is not found look in Client for username and password if that is not found look in EndUser for Login and Password, i know i need to do 'WHERE `Active` = '1' on all the tables someplace too.
my farther problem is if its the first two (Contact and Client) tables i need the info joined by ContactID and ClientID so i can pull the info Company_name and Contact Name etc from appropriate tables.
if the look up is EndUser i need just that tables info and the reference of UserTypeID description form the UserTypeID form EndUser along with the FirstName and LastName.
Im trying to make this so if a user logs in the admin(EndUser) and client can log into there accounts, but the tables i have are in 3 different spots(and i cant change them) i would have made it one table for login info and then broke up the tables in client/contact/admin using ids for each) but i didnt make the tables so im stuck with looking them up this way.
so whats the best way to do this please in MySQLi with php i really only need the sql statement but i wont complain if the php is there too
Thank you in advance for any code or help you may provide.
Johnny
Hi!
The easiest way is to query the tables like this
This will give you one list of unique usernames and passwords.
I suggest ( if you can ) to create a view of active users with this sql (excluding the order by line) then you can query that view like this
select username,password
from activeusers_v
order by username
Regards,
Tomas Helgi
The easiest way is to query the tables like this
select username,password
from contact
where active = '1'
union
select username,password
from client
where active = '1'
union
select login as username,password
from enduser
where active = '1'
order by usernameThis will give you one list of unique usernames and passwords.
I suggest ( if you can ) to create a view of active users with this sql (excluding the order by line) then you can query that view like this
select username,password
from activeusers_v
order by username
Regards,
Tomas Helgi
Tomas' solution is a good start - UNION is certainly appropriate here - but I think your requirements call for a different approach.
One other small note... if you are using this for authentication, you should phrase the query like this:
i need to look at Contact table and look for username and password if it is not found look in Client for username and password if that is not found look in EndUser for Login and PasswordThe most obvious way is to query the first table, check your return, and query the other tables if necessary:
$queries = array(
"SELECT username, password FROM contact WHERE active=1",
"SELECT username, password FROM client WHERE active=1",
"SELECT Login as username, Password as password FROM enduser WHERE active=1",
);
$myrow = NULL;
foreach ($queries as $query) {
// only run the next query is $myrow is not populated yet
if (!$myrow && $result = $dbconn->query($query)) {
$myrow = $result->fetch_assoc();
}
}
// $myrow will either have a record, or be NULLCREATE VIEW consolidate_users AS
select username,password,active,1 as user_type
from contact
union
select username,password,active,2 as user_type
from client
union
select login as username,password,active,3 as user_type
from enduser;$query="SELECT username,password,user_type FROM consolidate_users WHERE active=1 ORDER BY user_type";One other small note... if you are using this for authentication, you should phrase the query like this:
SELECT username,user_type FROM consolidate_users WHERE username="My Name" AND password="encrypted_password"ASKER
ok so how would i get the Company_name and Contact Name etc from appropriate tables and the reference of UserTypeID description form the UserTypeID form EndUser along with the FirstName and LastName.
also how would i fire off consolidate_users to make my first temp table? - you know now that i look at it i think the for next loop maybe a better idea, heck i dont know... im still at a loss on how to check for login info then get the info i need - i had thought that i might check enduser first for match if 0 rows returned fire off next and so forth this was i didn't do joins and such but id like to understand the join and CORRECT way of doing this so if i run across it in the future i can, i also have a buncha tables in this like the UserTypeID i need to cross ref from table data so how to use join right would be nice too. i defer to see how this is done.
@Steve Bink seams to be exactly what im looking for. How would i get the rest of my info using your consolidate_users approach please. (im understanding the concept but i have never done temp tables before nor query such as these {complex}) thank you for your help so far
@Tomas Helgi that looked good till "Steve Bink" piped in. thank you for your reply
@PortletPaul i didn't know any info more then i posted i had no idea where to start at all hence me asking the question i looked up as much as i could and nothing seamed to fit correctly..again hence the post (thanks for pointing out it need more info)
also how would i fire off consolidate_users to make my first temp table? - you know now that i look at it i think the for next loop maybe a better idea, heck i dont know... im still at a loss on how to check for login info then get the info i need - i had thought that i might check enduser first for match if 0 rows returned fire off next and so forth this was i didn't do joins and such but id like to understand the join and CORRECT way of doing this so if i run across it in the future i can, i also have a buncha tables in this like the UserTypeID i need to cross ref from table data so how to use join right would be nice too. i defer to see how this is done.
@Steve Bink seams to be exactly what im looking for. How would i get the rest of my info using your consolidate_users approach please. (im understanding the concept but i have never done temp tables before nor query such as these {complex}) thank you for your help so far
@Tomas Helgi that looked good till "Steve Bink" piped in. thank you for your reply
@PortletPaul i didn't know any info more then i posted i had no idea where to start at all hence me asking the question i looked up as much as i could and nothing seamed to fit correctly..again hence the post (thanks for pointing out it need more info)
>>"... i had no idea where to start ..."
and that is why I suggested the technique of "sample data" and "expected result". This technique requires less words and is also more accurate than explanation using words. Words are still needed, just less of them.
My problem is that while I see several potential joins, e.g.
Contact.ClientID joins to Client.ClientID
Client.EndUserID joins to EndUser.EndUserID
EndUser.UserTypeID join to UserType.UserTypeID
Without some sample data I cannot assess which is the best/correct type of join to apply. Without data you force us to guess (and guessing is not a great way to solve your problem).
So, here are 3 guesses on how to join your tables. I stress these are GUESSES
please note that while I have used select * in the above, that is only used to abbreviate the answer. You should not use "select *" in production code (and that includes php). Instead of "select *" you should list out the fields.
and that is why I suggested the technique of "sample data" and "expected result". This technique requires less words and is also more accurate than explanation using words. Words are still needed, just less of them.
My problem is that while I see several potential joins, e.g.
Contact.ClientID joins to Client.ClientID
Client.EndUserID joins to EndUser.EndUserID
EndUser.UserTypeID join to UserType.UserTypeID
Without some sample data I cannot assess which is the best/correct type of join to apply. Without data you force us to guess (and guessing is not a great way to solve your problem).
So, here are 3 guesses on how to join your tables. I stress these are GUESSES
select
*
from client cl
left join Contact co on cl.ClientID = co.ClientID
left join EndUser eu on cl.EndUserID = eu.EndUserID
left join UserType ut on eu.UserTypeID = ut.UserTypeID
;
select
*
from EndUser eu
inner join UserType ut on eu.UserTypeID = ut.UserTypeID
left join client cl on eu.EndUserID = cl.EndUserID
left join Contact co on cl.ClientID = co.ClientID
;
select
*
from Contact co
inner join client cl on co.ClientID = cl.ClientID
left join EndUser eu on cl.EndUserID = eu.EndUserID
left join UserType ut on eu.UserTypeID = ut.UserTypeID
;please note that while I have used select * in the above, that is only used to abbreviate the answer. You should not use "select *" in production code (and that includes php). Instead of "select *" you should list out the fields.
ASKER
@PortletPaul my data is over 200 megs with over 10k lines of code its a database for a friend that someone else made, and unfortunately died. So my hands are tied and he does not want to change the data. i can only manipulate it.
i ran into a problem of how to get the logins to look up the matches across 3 tables and then get the appropriate items as needed. i figured from the table names and fields i needed was sufficient.
this is now becoming way to complicated and it looks like im just gonna lang do it, i figured this was something fairly simple for someone that knew join tables and i knew mysql could make use of id linking for data lookups. but it seams im gonna have to find tutorials or something as this is not looking like it easy.
i thought i was just missing something. sometimes i wonder why things are so complicated when they dont have to be.
with all that said
reason for this post is i know NOTHING about join for complex mysql calls, i am currently looking up the inner join and left join on what it actually does, a more leniently explanation would be fantastic please.
what info do you want for testing or something would you like me to call all the fields ive listed in each tables and make a sql file and post that here? with lets say 5 records each?
i ran into a problem of how to get the logins to look up the matches across 3 tables and then get the appropriate items as needed. i figured from the table names and fields i needed was sufficient.
this is now becoming way to complicated and it looks like im just gonna lang do it, i figured this was something fairly simple for someone that knew join tables and i knew mysql could make use of id linking for data lookups. but it seams im gonna have to find tutorials or something as this is not looking like it easy.
i thought i was just missing something. sometimes i wonder why things are so complicated when they dont have to be.
with all that said
reason for this post is i know NOTHING about join for complex mysql calls, i am currently looking up the inner join and left join on what it actually does, a more leniently explanation would be fantastic please.
what info do you want for testing or something would you like me to call all the fields ive listed in each tables and make a sql file and post that here? with lets say 5 records each?
"sample data" does not have to be large
the real trick is that it requires you to make it "representative" (but I suspect that will be too hard right now)
so: 5 records from each table will be enough to get started.
-------------
the real trick is that it requires you to make it "representative" (but I suspect that will be too hard right now)
so: 5 records from each table will be enough to get started.
-------------
joins are not that hard, you can be doing it without even realizing it
e.g. a letter from Bank X, account Y get filed to into the Bank X folder, account Y section
you have just "joined" correspondence to folder
If you are really at such a beginning stage with sql joins I suggest you try a tutorial
e.g. sqlzoo.net
e.g. a letter from Bank X, account Y get filed to into the Bank X folder, account Y section
you have just "joined" correspondence to folder
If you are really at such a beginning stage with sql joins I suggest you try a tutorial
e.g. sqlzoo.net
@Steve Bink seams to be exactly what im looking for. How would i get the rest of my info using your consolidate_users approach please. (im understanding the concept but i have never done temp tables before nor query such as these {complex}) thank you for your help so farTo get the extra info, just supply some joins, and a common field name. You have ContactName for contacts, CompanyName for clients, and Description for end users. If you use the loop strategy, your queries will look like this:
$queries = array(
"SELECT username, password, ContactName as ExtraInfo FROM contact WHERE active=1",
"SELECT username, password, CompanyName as ExtraInfo FROM client WHERE active=1",
"SELECT a.Login as username, a.Password as password, b.description as ExtraInfo
FROM enduser a INNER JOIN usertype b on a.UserTypeID=b.UserTypeID
WHERE a.active=1",
);If you think a view work better, then create the view like this:
CREATE VIEW consolidate_users AS
select username,password,active,1 as user_type,ContactName as ExtraInfo
from contact
union
select username,password,active,2 as user_type,CompanyName as ExtraInfo
from client
union
select a.login as username,a.password,a.active,3 as user_type, b.description as ExtraInfo
from enduser a inner join usertype b on a.UserTypeID=b.UserTypeID;
@Steve Bink
While pursuing the unioned approach may indeed be worthwhile, please use UNION ALL instead of UNION.
(Now that each table has 1 or 2 or 3 as user_type, there can no longer be any purpose to union that is not achieved by union all)
@Pern
The reason for raising this point is that UNION attempts to ensure all rows are unique, but UNION ALL does not do this. So UNION ALL is faster.
While pursuing the unioned approach may indeed be worthwhile, please use UNION ALL instead of UNION.
(Now that each table has 1 or 2 or 3 as user_type, there can no longer be any purpose to union that is not achieved by union all)
@Pern
The reason for raising this point is that UNION attempts to ensure all rows are unique, but UNION ALL does not do this. So UNION ALL is faster.
ASKER
Today I am really busy it being sunday. I will review and look stuff up to understand this. Thx for the zoo link too.
ASKER
@Steve Bink
i corrected the query so it reflected my table names (i also noticed a space in a lot of the fields like "Client Name" question is it ok to have white space in a field name? i try to use `` for field names.
returns
im not understanding some thing here
shouldn't that give me extrainfo with the description of the EndUser.UserType??
and how would i get ContactID & ClientID from Contact table so i can use there info in there respective tables please
beyond that the username and pw is perfectly joined.
if i have the example above i think i can wrap my head around this. and if i can see the output as the table like i did in myphpadmin. (i guess i could use a few query example calls to understand it a bit more.
oh one other thing OH wow it actually makes a table - wow
Thank you so much for your help so far.
here is test data and table structure
@PortletPaul i changed the UNION to UNION ALL it didnt seam to change much.
Thank you too for all your help so far
i corrected the query so it reflected my table names (i also noticed a space in a lot of the fields like "Client Name" question is it ok to have white space in a field name? i try to use `` for field names.
CREATE VIEW consolidate_users AS
select username,password,active,1 as user_type,ContactName as ExtraInfo
from Contact
union
select username,password,active,2 as user_type,CompanyName as ExtraInfo
from Client
union
select a.login as username,a.password,a.active,3 as user_type, b.description as ExtraInfo
from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;SELECT * FROM `consolidate_users` WHERE 1returns
username password active user_type ExtraInfo
FRANK HUGG [pw removed] 1 1 FRANK HUGG
AMY MARRON [pw removed] 1 1 AMY MARRON
JAMES D. HOEY, III [pw removed] 1 1 JAMES D. HOEY, III
EANBAR [pw removed] 1 1 ELIC ANBAR
ROXANNE LOPEZ [pw removed] 1 1 ROXANNE LOPEZ
STEPHEN F. LOPEZ [pw removed] 1 1 STEPHEN F. LOPEZ
STERLING J. STIRES [pw removed] 1 1 STERLING J. STIRES
RICHARD DEVIRIAN [pw removed] 1 1 RICHARD DEVIRIAN
RICK BARTON, ESQ. [pw removed] 1 1 RICK BARTON, ESQ.
LTWITCHELL [pw removed] 1 1 LAURIE TWITCHELL, ESQ.im not understanding some thing here
3 as user_type, b.description as ExtraInfo
from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;shouldn't that give me extrainfo with the description of the EndUser.UserType??
and how would i get ContactID & ClientID from Contact table so i can use there info in there respective tables please
beyond that the username and pw is perfectly joined.
if i have the example above i think i can wrap my head around this. and if i can see the output as the table like i did in myphpadmin. (i guess i could use a few query example calls to understand it a bit more.
oh one other thing OH wow it actually makes a table - wow
Thank you so much for your help so far.
here is test data and table structure
CREATE TABLE IF NOT EXISTS `Client` (
`ClientID` int(11) NOT NULL AUTO_INCREMENT,
`CompanyName` varchar(50) DEFAULT NULL,
`username` varchar(20) DEFAULT NULL,
`password` varchar(15) DEFAULT NULL,
`PaymentTierID` int(11) DEFAULT '0',
`OrderingAddress` varchar(50) DEFAULT NULL,
`Ordering City` varchar(50) DEFAULT 'CA',
`Ordering State` varchar(2) DEFAULT NULL,
`Ordering Zip` varchar(10) DEFAULT NULL,
`Phone Number` varchar(20) DEFAULT NULL,
`Fax Number` varchar(20) DEFAULT NULL,
`Billing Address` varchar(50) DEFAULT NULL,
`Billing City` varchar(50) DEFAULT NULL,
`Billing State` varchar(2) DEFAULT 'CA',
`Billing Zip` varchar(10) DEFAULT NULL,
`Billing Phone Number` varchar(20) DEFAULT NULL,
`Billing Fax Number` varchar(20) DEFAULT NULL,
`SalesRepId` int(11) DEFAULT '0',
`SalesTaxRateID` int(11) NOT NULL DEFAULT '0',
`CompanyURL` varchar(255) DEFAULT NULL,
`Active` tinyint(1) NOT NULL DEFAULT '0',
`EndUserID` int(11) NOT NULL DEFAULT '0',
`Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`ClientID`),
KEY `ClientID` (`ClientID`),
KEY `EndUserID` (`EndUserID`),
KEY `PaymentTierID` (`PaymentTierID`),
KEY `SalesRepId` (`SalesRepId`),
KEY `SalesTaxRateID` (`SalesTaxRateID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=2036 ;
INSERT INTO `Client` (`ClientID`, `CompanyName`, `username`, `password`, `PaymentTierID`, `OrderingAddress`, `Ordering City`, `Ordering State`, `Ordering Zip`, `Phone Number`, `Fax Number`, `Billing Address`, `Billing City`, `Billing State`, `Billing Zip`, `Billing Phone Number`, `Billing Fax Number`, `SalesRepId`, `SalesTaxRateID`, `CompanyURL`, `Active`, `EndUserID`, `Created`, `Modified`) VALUES
(1, 'HOEY & MORGAN', 'HOEY', 'xxxx', 1, '8910 UNIVERSITY CENTER LANE - STE. 425', 'SAN DIEGO', 'CA', '92122', '8585501002', '8585502043', '8910 UNIVERSITY CENTER LANE - STE. 425', 'SAN DIEGO', 'CA', '92122', '8585501002', '8585502043', 1, 8, '', 1, 9, '2004-11-02 21:37:01', '2004-12-09 15:23:04'),
(2, 'FRANK B. HUGG ESQ.', 'HUGG', 'xxxx', 1, '5810 TELEGRAPH AVENUE', 'OAKLAND', 'CA', '94609', '5105959575', '5105959525', '5810 TELEGRAPH AVENUE', 'OAKLAND', 'CA', '94609', '5105959575', '5105959525', 1, 8, '', 1, 23, '2004-11-05 17:43:34', '2008-06-12 20:10:21'),
(3, 'JOHN R. GARCIA ESQ.', '', 'xxxxx', 1, '3579 FIFTH AVE., STE. 100', 'SAN DIEGO', 'CA', '92103', '6192944040', '6192944174', '3579 FIFTH AVE., STE. 100', 'SAN DIEGO', 'CA', '92103', '6192944040', '', 1, 8, '', 1, 24, '2004-11-05 17:47:02', '2005-04-29 20:55:08'),
(4, 'WARD NORTH AMERICA', 'WNA', 'xxxxx', 1, '610 NORTH ASH SUITE 1900', 'SAN DIEGO', 'CA', '92101', '6195572777', '6195951689', '610 NORTH ASH SUITE 1900', 'SAN DIEGO', 'CA', '92101', '6195572777', '6195951689', 1, 8, '', 1, 9, '2004-11-05 17:49:12', '2004-11-05 17:49:12'),
(5, 'LINCOLN, GUSTAFSON & CERCOS', 'LG&C', 'xxxxx', 1, '550 WEST C ST., #1400', 'SAN DIEGO', 'CA', '92101', '6192331150', '6192336949', '550 WEST C ST., #1400', 'SAN DIEGO', 'CA', '92101', '6192331150', '6192336949', 2, 8, '', 1, 23, '2004-11-05 14:02:18', '2014-08-19 16:20:19');
CREATE TABLE IF NOT EXISTS `Contact` (
`ContactID` int(11) NOT NULL AUTO_INCREMENT,
`ClientID` int(11) DEFAULT '0',
`ContactName` varchar(50) NOT NULL,
`EmailAddress` varchar(255) DEFAULT NULL,
`Secretary Name` varchar(50) DEFAULT NULL,
`EmailAddress2` varchar(255) DEFAULT NULL,
`Active` tinyint(1) NOT NULL DEFAULT '0',
`EndUserID` int(11) NOT NULL DEFAULT '0',
`Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`username` varchar(20) DEFAULT NULL,
`password` varchar(15) DEFAULT NULL,
`Master` tinyint(1) DEFAULT '0',
PRIMARY KEY (`ContactID`),
KEY `ClientID` (`ClientID`),
KEY `ContactID` (`ContactID`),
KEY `EndUserID` (`EndUserID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4026 ;
INSERT INTO `Contact` (`ContactID`, `ClientID`, `ContactName`, `EmailAddress`, `Secretary Name`, `EmailAddress2`, `Active`, `EndUserID`, `Created`, `Modified`, `username`, `password`, `Master`) VALUES
(2, 2, 'FRANK HUGG', 'FHUGG@PACBELL.NET', 'SUSAN', '', 1, 9, '2004-11-05 17:43:59', '2007-02-14 12:12:27', 'FRANK HUGG', 'xxxxx', 1),
(8, 7, 'AMY MARRON', '', 'MERINN HURD', '', 1, 9, '2004-11-06 16:52:29', '2005-03-16 15:22:51', 'AMY MARRON', 'xxxxx', 0),
(18, 1, 'JAMES D. HOEY, III', '', 'DAVID PERKINS', '', 1, 9, '2004-11-07 17:02:57', '2007-02-14 11:49:02', 'JAMES D. HOEY, III', 'xxxxx', 0),
(26, 21, 'ELIC ANBAR', '', '', '', 1, 9, '2004-11-15 15:17:22', '2004-11-15 15:17:22', 'EANBAR', 'xxxxx', 0),
(37, 7, 'ROXANNE LOPEZ', '', 'MERINN HURD', '', 1, 9, '2004-11-22 22:32:36', '2005-03-16 15:22:08', 'ROXANNE LOPEZ', 'xxxxx', 0);
CREATE TABLE IF NOT EXISTS `EndUser` (
`EndUserID` int(11) NOT NULL AUTO_INCREMENT,
`FirstName` varchar(50) NOT NULL,
`LastName` varchar(50) NOT NULL,
`Login` varchar(50) NOT NULL,
`Password` varchar(90) NOT NULL,
`UserTypeID` int(11) NOT NULL DEFAULT '0',
`Active` tinyint(1) NOT NULL DEFAULT '0',
`Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`EndUserID`),
KEY `EndUserID` (`EndUserID`),
KEY `UserTypeID` (`UserTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=32 ;
INSERT INTO `EndUser` (`EndUserID`, `FirstName`, `LastName`, `Login`, `Password`, `UserTypeID`, `Active`, `Created`, `Modified`) VALUES
(9, 'NORMAL', 'NORMAL', 'ADMIN', 'xxx', 0, 1, '2004-06-10 23:01:02', '2005-04-11 19:31:08'),
(10, 'KAREN', 'MOORE', 'ORDER ENTRY', 'xxxxx', 2, 0, '2004-06-10 23:07:01', '2005-04-12 01:25:39'),
(11, 'Normal', 'User', 'User', 'xxxx', 1, 1, '2004-06-10 23:07:13', '2004-06-18 22:11:00'),
(22, 'BRAD', 'VEACH', 'BRAD', 'xxxxx', 0, 0, '2005-04-12 01:22:49', '2005-04-12 01:24:46'),
(23, 'CHERYLE', 'ONG', 'CHERYLE ONG', 'xxxxx', 0, 1, '2005-04-12 01:25:24', '2007-11-30 22:11:18');
CREATE TABLE IF NOT EXISTS `UserType` (
`UserTypeID` int(11) NOT NULL DEFAULT '0',
`Description` varchar(50) DEFAULT NULL,
`Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`UserTypeID`),
KEY `UserTypeID` (`UserTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `UserType` (`UserTypeID`, `Description`, `Created`, `Modified`) VALUES
(0, 'Admin', '2004-06-10 23:05:47', '2004-06-10 23:06:01'),
(1, 'Normal User', '2004-06-10 23:06:19', '2004-06-10 23:06:35'),
(2, 'Order Entry', '2004-06-10 23:06:07', '2004-06-10 23:06:19');@PortletPaul i changed the UNION to UNION ALL it didnt seam to change much.
Thank you too for all your help so far
(i also noticed a space in a lot of the fields like "Client Name" question is it ok to have white space in a field name? i try to use `` for field names.You should always try to avoid whitespace in database object names. It makes things more complicated, and prone to error. If you do have field names with spaces in them, encapsulating them in backtick (`) characters will allow MySQL to parse them properly.
Sort of... The ExtraInfo field will be populated with UserType.description, after having been joined to the table EndUser. That information only exists for records not found in Contact or Client. It is important to keep in mind that ExtraInfo is context-sensitive field. It's purpose will change depending on the value of consolidated_users.user_tyshouldn't that give me extrainfo with the description of the EndUser.UserType??3 as user_type, b.description as ExtraInfo from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;
and how would i get ContactID & ClientID from Contact table so i can use there info in there respective tables pleaseWhich fields from which tables would you like to have present in the results? Remember that, in a UNION query, all source queries must have the same number of fields. So, adding, for example, ClientID into one of the source queries, we'll need to add, at the least, a placeholder field with an arbitrary NULL value in the other source queries. For example, this query adds ContactID, but it is only populated with anything useful if user_type=1:
CREATE VIEW consolidate_users AS
select ContactID,username,password,active,1 as user_type,ContactName as ExtraInfo
from Contact
union
select NULL as ContactID,username,password,active,2 as user_type,CompanyName as ExtraInfo
from Client
union
select NULL as ContactID,a.login as username,a.password,a.active,3 as user_type, b.description as ExtraInfo
from EndUser a inner join UserType b on a.UserTypeID=b.UserTypeID;ASKER
@Steve Bink
ok this is where my problem is and im confused. the rest makes a little sense now ill have to play with it to understand it.
It's purpose will change depending on the value of consolidated_users.user_ty
ok so how would i get user type id 2 and 3 and how is it only getting 1
and FYI i need it to look at ALL usernames/pws and then give me back the relevant data
im trying to login in with either client,contact,enduser(adm
so how do i get this table to show me the joined info so i can do that please.
BTW i think i learned more about this then i ever could (again thanks, i cant say enoph how great this is)
ok this is where my problem is and im confused. the rest makes a little sense now ill have to play with it to understand it.
It's purpose will change depending on the value of consolidated_users.user_ty
ok so how would i get user type id 2 and 3 and how is it only getting 1
and FYI i need it to look at ALL usernames/pws and then give me back the relevant data
im trying to login in with either client,contact,enduser(adm
so how do i get this table to show me the joined info so i can do that please.
BTW i think i learned more about this then i ever could (again thanks, i cant say enoph how great this is)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@ Steve Bink
We do not seam to be on the same page here.
The user_type needs to be the usertype more then 1 it needs to be :
the table created only has type id 1's info i need both 2 and 0 as well
i also need to know it its from table Client,contact,or enduser or i cant call the relevant data to each table right? to know like the clients address info etc
Sorry im all new to this, im understanding it tho i think.
once i have the referance table with the user name,pw, user type, user_id, and database type(client,contact,enduse
id simply call it like you gave me as if its its own table then use its id and ref table to pull the data. this will work out great if the references are all there
We do not seam to be on the same page here.
The user_type needs to be the usertype more then 1 it needs to be :
CREATE TABLE IF NOT EXISTS `UserType` (
`UserTypeID` int(11) NOT NULL DEFAULT '0',
`Description` varchar(50) DEFAULT NULL,
`Created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`Modified` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`UserTypeID`),
KEY `UserTypeID` (`UserTypeID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `UserType` (`UserTypeID`, `Description`, `Created`, `Modified`) VALUES
(0, 'Admin', '2004-06-10 23:05:47', '2004-06-10 23:06:01'),
(1, 'Normal User', '2004-06-10 23:06:19', '2004-06-10 23:06:35'),
(2, 'Order Entry', '2004-06-10 23:06:07', '2004-06-10 23:06:19');the table created only has type id 1's info i need both 2 and 0 as well
i also need to know it its from table Client,contact,or enduser or i cant call the relevant data to each table right? to know like the clients address info etc
Sorry im all new to this, im understanding it tho i think.
once i have the referance table with the user name,pw, user type, user_id, and database type(client,contact,enduse
id simply call it like you gave me as if its its own table then use its id and ref table to pull the data. this will work out great if the references are all there
>>" i changed the UNION to UNION ALL it didnt seam to change much."
EXACTLY!
The query you are trialling does NOT BENEFIT from using union!
union all will be FASTER
EXACTLY!
The query you are trialling does NOT BENEFIT from using union!
union all will be FASTER
the table created only has type id 1's info i need both 2 and 0 as well
We're not on the same page, and it is really my fault for re-using a term from your existing schema. The usertype field in my example queries does not relate to your existing UserTypeID. Instead, think of my version as a "user category" or "user class", meaning it signifies only if the discovered login belongs to a contact, client, or enduser record. It is a separate and distinct data point.
Also, PortletPaul is correct - UNION ALL will be faster than UNION. You likely will not see much benefit until your record count is a little higher, though. As you can see in this example, UNION can be about 3-1/2 times slower than UNION ALL.
ASKER
@Steve Bink
i gave you the test data above please try and run the query your self maybe im missing something but i dont think so
i currently can not distinguish between Client,Contact,EndUser (i assume as there all 1 in the user_type field)
if the discovered login belongs to a contact, client, or enduser recordok then why are all my returned items in row user_type all 1 and i do not have any from enduser for data at all
i gave you the test data above please try and run the query your self maybe im missing something but i dont think so
i currently can not distinguish between Client,Contact,EndUser (i assume as there all 1 in the user_type field)
see http://sqlfiddle.com/#!9/72b47/1 the view proposed by Steve (but using UNION ALL) provides records from all 3 source tables (under the heading "USER_TYPE")
| USERID | USERNAME | PASSWORD | ACTIVE | USER_TYPE | EXTRAINFO |
|--------|--------------------|----------|--------|-----------|------------------------------|
| 2 | FRANK HUGG | xxxxx | 1 | 1 | FRANK HUGG |
| 8 | AMY MARRON | xxxxx | 1 | 1 | AMY MARRON |
| 18 | JAMES D. HOEY, III | xxxxx | 1 | 1 | JAMES D. HOEY, III |
| 26 | EANBAR | xxxxx | 1 | 1 | ELIC ANBAR |
| 37 | ROXANNE LOPEZ | xxxxx | 1 | 1 | ROXANNE LOPEZ |
| 1 | HOEY | xxxx | 1 | 2 | HOEY & MORGAN |
| 2 | HUGG | xxxx | 1 | 2 | FRANK B. HUGG ESQ. |
| 3 | | xxxxx | 1 | 2 | JOHN R. GARCIA ESQ. |
| 4 | WNA | xxxxx | 1 | 2 | WARD NORTH AMERICA |
| 5 | LG&C | xxxxx | 1 | 2 | LINCOLN, GUSTAFSON & CERCOS |
| 9 | ADMIN | xxx | 1 | 3 | Admin |
| 10 | ORDER ENTRY | xxxxx | 0 | 3 | Order Entry |
| 11 | User | xxxx | 1 | 3 | Normal User |
| 22 | BRAD | xxxxx | 0 | 3 | Admin |
| 23 | CHERYLE ONG | xxxxx | 1 | 3 | Admin |ASKER
well dont i feel stupid!!
i re-looked at the setup and found that myphpadmin was limited by default to 500 rows thus in my almost 6k records not pulling them all thus not having the admin (end users) at the end. so now that i have the correct info
so id just take the USER_TYPE and that be my table to use..OK now i get it light bulb!!
ok im so grateful you all didn't call me stupid...
@PortletPaul is it ok if i give you 200 points, as you assisted in the solution and Steve Bink gave complete code and examples solution
ill wait till tomorrow to accept but i believe this is now answered, unless you answer before
@Steve Bink Thank you in all that i can thank you!!
i re-looked at the setup and found that myphpadmin was limited by default to 500 rows thus in my almost 6k records not pulling them all thus not having the admin (end users) at the end. so now that i have the correct info
SELECT * FROM `consolidate_users`LIMIT 0, 7000so id just take the USER_TYPE and that be my table to use..OK now i get it light bulb!!
ok im so grateful you all didn't call me stupid...
@PortletPaul is it ok if i give you 200 points, as you assisted in the solution and Steve Bink gave complete code and examples solution
ill wait till tomorrow to accept but i believe this is now answered, unless you answer before
@Steve Bink Thank you in all that i can thank you!!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@PortletPaul
i used your pull but ill redo it to make sure later i still need to play a bit to pull info...
thank you again
i used your pull but ill redo it to make sure later i still need to play a bit to pull info...
thank you again
I suggest you provide some rows of data from each table.
Then also provide the result you would expect from that data.
You can use Excel or text (like CSV or tab delimited) for the data. Include the field names as headings too. Private data (like email addresses) can changed or, if not important for this purpose, excluded.