mysql query depending on other table

Question

I've two that i want to select from the first table depending on the second table.

First Table

ex_instagram_p:

id (int)
username (varchar)
cpc (int)
type (varchar)
active (int)

Second Table

exchanges

 id (int)
 user (varchar)
 exid (int)

What i want :

select values from ex_instagram_p and to make sure it does not equal any entry in the second table exchanges Where exchanges.user = $username rows Doesn't contains ex_instagram_p.id on exchanges.id

let me clear it

for example table one is for blog articles

so the user will show articles != his username and active = 1 and type ='".follow."'

and the second table is the views.

so if there is an entry on the second table with user = $username and exid = firsttable.id

this entry has been already viewed by this user and i don't want him to view it again.

Actually i need if no entry on the exchanges table got the values of
user - exid
$username - i.id

if this doesn't exist is should continue

Here is the code i've created, but it does not work

$prowz= mysql_query("SELECT i.*
FROM ex_instagram_p as i, exchanges as e
 HAVING COUNT(e.user = '".$username." And e.exid = i.id) = 0 
 WHERE 
 AND i.type = '".follow."' 
 AND i.active=1
 AND i.username != '".$username."'
ORDER BY i.cpc DESC
 LIMIT 1;");

@Strawberry That's [no mistake of Gordon Linoff](http://stackoverflow.com/questions/24851214/mysql-query-select-depending-on-2-tabels). — VMai, Jul 20 '14 at 21:31
@Strawberry: The only answer to the question I linked to: http://stackoverflow.com/questions/24851214/mysql-query-select-depending-on-2-tabels#answer-24851292 — VMai, Jul 20 '14 at 21:39

StackSlave · Answer 1 · 2014-07-20T22:49:26.947

0

$follow should be a variable, or just the String 'follow'. It's an undefined Constant like that.

"SELECT i.* FROM ex_instagram_p i, exchanges e
 WHERE i.id != e.exid
 && e.user = '$username'
 && i.type = '$follow'
 && i.active = 1 
 && i.username != '$username'
 ORDER BY i.cpc DESC LIMIT 1;"

edited Jul 20 '14 at 22:49

answered Jul 20 '14 at 22:23

StackSlave

10,613
2
18
35

i need to use `HAVING COUNT(e.user = '".$username." And e.exid = i.id) = 0 ` – Zack Jul 20 '14 at 22:44
Why, might I ask? I've never seen `COUNT()` used like that before. Are you sure you don't want a subquery? – StackSlave Jul 20 '14 at 23:01
Actually i need if no entry on the exchanges table got the values of user - exid $username - i.id if this doesn't exist it should continue – Zack Jul 20 '14 at 23:02
I don't understand what you're asking. user - exid $username - i.id? What does that mean in English? – StackSlave Jul 20 '14 at 23:14
i do not know what i really need to use , but what i really need is as following. for example i got two table one for blog topics and the other for topic views. i want to show the user the topics which is for other users only and that he did not view yet only. – Zack Jul 20 '14 at 23:35
Why don't you change your database structure? A viewed bool could be a column. Your columns that have the same values best be left with the same column names for simplicity. – StackSlave Jul 20 '14 at 23:41

score -1 · Answer 2 · answered Jul 20 '14 at 21:34

-1

Many WHERE clauses but it's simple..

SELECT i.*
FROM ex_instagram_p as i, exchanges as e
WHERE i.id != e.exid
AND e.user = '".$username."'
AND i.type = '".follow."' 
AND i.active=1
AND i.username != '".$username."'
ORDER BY i.cpc DESC
LIMIT 1;

answered Jul 20 '14 at 21:34

Alexis_user

427
5
14

Let's not promote 20th C. join conventions OK. So passé – Strawberry Jul 20 '14 at 21:35
[Difference between WHERE and JOIN.](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) Each one uses what he wants. And inthis case, both work. – Alexis_user Jul 20 '14 at 21:40
Actually i need if no entry on the exchanges table got the values of user - exid $username - i.id if this doesn't exist is should continue – Zack Jul 20 '14 at 21:43

mysql query depending on other table

2 Answers2