Advanced query assistance
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