-2

I have two tables 'account' and 'medium' in my mysql database.

The account table contains fields

'id','name','active','allow','parent_id','model'

Medium table contains fields

'med_id','acc_id','type','year'

The 'acc_id' in medium table is same as the 'id' in account table.So all the entries in medium table should be in account table.The 'type' field in medium table have values like 'BN','CD','CS','LL'.

Now i need to select id,name,allow,parent_id from account table so that active=1,allow=1 and also allow accounts in medium table with 'type' values as 'BN' or 'CS' to select from the account table.

That is the result may contain accounts which are not in medium table and accounts in medium table and if accounts in medium table is in it should be based on 'type' field value in medium table.How to write this query in mysql.

For example, account table:

Car
Bus
Town
Ornaments
Plants
Animals
Forest
Planets
Aquamarine
Hills
Waterfall
Temple

and medium table is

Plants        BN
Animals       LL
Forest        CS
Aquamarine    BN
Hills         CD
Waterfall     LL

And the result of query should be like:

Car
Bus
Town
Ornaments
Plants
Forest
Planets
Aquamarine
Temple

I don't know how to join these conditions in a query.Please help me.

KJEjava48
  • 1,967
  • 7
  • 40
  • 69
  • Provide proper DDLs and a desired result, and show us your best efforts to date. – Strawberry Jul 30 '15 at 06:36
  • I think now its clear after the edit – KJEjava48 Jul 30 '15 at 06:58
  • I disagree. For instance, I don't understand why you have two separate tables. But more than that, I don't understand how a) the data set relates to the question nor b) how the result set relates to the data set. – Strawberry Jul 30 '15 at 08:23

1 Answers1

0

Try the below query and i think this will satisfy your results as per my concern.

SELECT A.id, A.name, A.allow, A.parent_id
FROM account A WHERE A.active = 1 AND A.allow = 1 AND A.id not in(select M.acc_id from medium M where M.type not in('BN','CS'))
user2986084
  • 98
  • 4
  • 12