A MySQL query question

[audiopro]

I am writing a query on the following table.
Let's forget the syntax and concentrate on the function.

cust magazines
 1      4
 1      5
 2      4
 2      6
 3      5
 3      6

I am customer number 2 and I want to make a list of the magazines I subscribe to and other magazines available.

SELECT * WHERE CUST=2

will give me

2  4
2  6

Now I want to carry on the list with the rest ie.

SELECT * WHERE CUST <> 2
1  4
1  5
3  5
3  6

How can I avoid the duplicates and end up with the following list.

Subscribe
4
6
Not Subscribed
5

I can achieve this with code but is it possible in 1 single SQL statement?


Keith

http://www.studiosoft.co.uk

 

[Kozusnik]

Try SELECT DISTINCT magazines from TABLE WHERE (logic).

Mark

There are 10 types of people in this world, those who undersand binary and those who don't.

 

[audiopro]

Thanks I'll try that out.
This is part of a more complicated query so I am not sure if it is possible.
I have written a routine to sort the info within a fetchrow_array which works and allows progress to be made.


Keith

http://www.studiosoft.co.uk

 

[azzazzello]

I am feeling a bit queezy right now and my head is hazy, but I believe that a combination of IF and subselect can ackomplish this task...So, something like

Select id, (IF id in (SELECT ID WHERE CUST = 2) then 1 else 0) as subscribed from ...;

Do test this, because I am semi-lucid now