2

I want to make a page where I can see who have purchased and who not, and I want make the people who HAVEN"T purchased into a new array. I searched some mysql function, but I couldn't find how to make the both of them. I know it will be like this:

mysql_query("SELECT id, name, organisation_id
FROM bought, organisation
WHERE id = id
)

but I don't know from here on, I think I need to use join function but how can I use this function in the way I want?

organisations who bought something are in the other table.

example:

$peoplewhodidn'tbuy:

1       
2       
3      
4       
5       
6

$peoplewhodidbuy:

7
8
9
10
11
12

Bought table:

id product | organisation id | name

organisation table:

organisation id | name | type
STP38
  • 348
  • 2
  • 14
  • 1
    Your schema is not speaking for itself at all. How can you determine if someone bought something? – kero Feb 20 '14 at 14:03
  • @kingkero Srry for not mentioning I edited – STP38 Feb 20 '14 at 14:05
  • 1
    Can you show the table structures, and how are you determining people who did/didn't buy ? There might be a possibility of doing it in one query. – Raiyan Feb 20 '14 at 14:08
  • 1
    [`mysql_*` has been ***deprecated***](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for quite some time now, stop using it – Elias Van Ootegem Feb 20 '14 at 14:12
  • I know it is just a practise on school, I need to do it so. – STP38 Feb 20 '14 at 14:14
  • Good to know that they teach up-to-date things ;) – rocknrollcanneverdie Feb 20 '14 at 14:15
  • 3
    @STP38: Change schools. You and the school have a contract: they teach you X, and you pay them to do so. If they promised you to teach you science, and then told you the sun revolved around the earth, you'd call that a breach of contract – Elias Van Ootegem Feb 20 '14 at 14:18

2 Answers2

1

Assuming organisation has the 'people' and bought has the purchases

something like:

select
   organisation.id, 
   organisation.name
from 
  organisation 
where 
  id in (
    select organisation_id from bought 
  )

would give the buyers 

select 
  organisation.id, 
  organisation.name
from 
  organisation 
where 
  id not in (
    select organisation_id from bought 
  )

for the non buyers

Ian Kenney
  • 6,376
  • 1
  • 25
  • 44
1

try 

SELECT t1.*
FROM organisation t1
LEFT JOIN Bought t2 ON t2.organisation_id = t1.organisation_id
WHERE t2.organisation_id IS NULL
Akhil Sidharth
  • 746
  • 1
  • 6
  • 16
  • 1
    Minor typo I think, but suspect you meant to check t2.organisation_id for null, not the t1 version. – Kickstart Feb 20 '14 at 14:25
  • @AkhilSidharth what you do mean by t1? organisations or bought? – STP38 Feb 20 '14 at 14:37
  • 1
    t1 and t2 are alias' for the table names. So this query has FROM organisation t1 which says that t1 is an alias for organisation, and then t1.organisation_id is referring to organisation.organisation_id. Makes the code smaller and often more readable. Far more common when a particular table is used multiple times in the same select statement. – Kickstart Feb 20 '14 at 14:49