CODEWITHSUNDEEP
mysqlsql
John Smith
John Smith

Reputation: 11

sql query one field twice on one table using same condition with different values

I have a table called accounts

I have fields called

id  
provider
type
account_name

they are structured as

id - int(12)
provider -int(12)
type - int(12)
account_name - Varchar(25)

I think a join or subquery could work but cant get it work The aim is i want to display providers that have both type=2 and type=5 ORDER By account_name

eg shows companies that sell both real estate and loans

Select * from accounts WHERE type = 2 and type=5

I know the above doesnt work but its kind the mindset that i need. Id appreciate any help in getting this working.

The mysql output result should look like this in a single line.

Eg

Provider Account_Name1(this matched 2) Account_Name2(Matched 5) Johnson - Johnson Real Estate - Johnson Loans

Upvotes: 1

Views: 961

Answers (2)

El Ronnoco
El Ronnoco

Reputation: 11922

select distinct acc2.provider,acc2.account_name,acc5.account_name
from accounts acc2 inner join accounts acc5 on acc2.provider=acc5.provider
where acc2.type = 2 and acc5.type = 5

This makes a bit of an assumption that there won't be duplicate provider/type combinations. eg One provider having multiple rows having the same type. In fact the distinct isn't necessary if the data conforms to that requirement.

I would also say as an aside that this is a somewhat strange table, although I don't know the full story. This seems to be a table used to associate providers with account types. And each association of provider and account type can have an Account_Name. I would think this would be better represented by 3 tables.

TABLE Provider (
   ID INT PK
   Name VARHCAR(50)
)

TABLE AccountType (
   ID INT
   AccountType VARCHAR(50)
)

TABLE ProviderAccountType (
   ProviderID PK FK
   AccountTypeID PK FK
   AccountName VARCHAR(50)
)

Upvotes: 0

Andomar
Andomar

Reputation: 238048

This should look up providers that have both id 2 and 5:

select  provider
from    accounts
where   id in (2,5)
group by
        provider
having  count(distinct id) = 2

Upvotes: 4

Related Questions