MySQL Forums
Forum List  »  Newbie

Advanced query assistance
Posted by: Steven Shepherd
Date: May 08, 2008 03:56PM

The query below is functioning, however it is returning the entire thing (server_name,account_id,account_type,user,domain,ip,type) for each row returned in "type", and thus will return multiple lines for the same account should they have more than 1 item in "extras"

select server_name,accounts.account_id, accounts.account_type,users.user, domain,ips.ip,extras.type from servers,accounts,domains,ips,extras,users where servers.server_id = accounts.server_id and domains.domain_id = accounts.primary_domain_id and accounts.account_status in ('A','L') and servers.os = 'linux' and servers.server_id not in (91,143) and accounts.account_id = ips.account_id and servers.realm_id = 4 and accounts.account_id = extras.account_id and accounts.account_id = users.account_id and servers.server_id = ips.server_id order by servers.server_name limit 10;

Sample:

|SERVER1|1234|REGULAR|USERNAME1|foo.com|127.0.0.1|type_1
|SERVER1|1234|REGULAR|USERNAME1|foo.com|127.0.0.1|type_2
|SERVER1|1234|REGULAR|USERNAME1|foo.com|127.0.0.1|type_3


Now, I want to return all the data, but I only want to limit the "type" to "type_2". If they have type_1 or type_3, it should print everything except the last part should be NULL.

Example:

|SERVER1|1234|REGULAR|USERNAME1|foo1.com|127.0.0.1|NULL
|SERVER1|1235|REGULAR|USERNAME2|foo2.com|127.0.0.2|type_2
|SERVER1|1236|REGULAR|USERNAME3|foo3.com|127.0.0.3|NULL

Obviously if I add the condition "and extras.type = 'type_2'", to the query above I ONLY get rows returned with "type_2". I believe this is where a JOIN (left join?) would come into play, but I just can't seem to get the query to return what I need.

I appreciate any assistance.

Steve

Options: ReplyQuote


Subject
Written By
Posted
Advanced query assistance
May 08, 2008 03:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.