CODEWITHSUNDEEP
mysqlsql
malaquias
malaquias

Reputation: 65

SELECT DISTINCT with INNER JOIN

Hello All,
This sound very simple but I'm having a problem here.

There are tables:

table_A
TA_id | TA_user | TA_hour | TA_date

and 

table_B
TB_id | TB_name | TB_adress

Here is the query:

$sql = mysql_query("SELECT 
                    DISTINCT TA_user 
                    FROM table_A 
                    INNER JOIN table_B ON table_B.id = table_A.TA_user 
                    WHERE TA_date LIKE '%$vardate%' 
                    ORDER BY TA_user ASC ");

When I run the While loop in the array the TB_name that shuld print cause the inter join dosen't work.

Any ideas ?

Upvotes: 1

Views: 23251

Answers (3)

SeeJayBee
SeeJayBee

Reputation: 1249

It appears to me that you have multiple table A records for a given table B entity, is that right?

By the way, your column names are horrible...table_B.id = table_A.TA_user???? how is someone examining your database supposed to know that relationship exists? But that's beside the point.

Anyway, I guess that you're trying to return the table B entities that have records in table_A for a given date?

If so, distinct should work for you. I'd probably use group by instead because it's generally faster. But this should do what you want, if I understand you correctly.

SELECT DISTINCT table_B.TB_name
    FROM table_A 
        INNER JOIN table_B
            ON table_B.TB_id = table_A.TA_user
    WHERE TA_date LIKE '%$vardate%'
    ORDER BY TA_user ASC;

And, as @Gordon Linoff says above, the LIKE with a date field is suspicious. His idea there seems appropriate to me.

Upvotes: 1

malaquias
malaquias

Reputation: 65

I reach the deal : $sql = mysql_query("SELECT * FROM table_A INNER JOIN table_B ON table_B.id = table_A.TA_user WHERE TA_date LIKE '%$vardate%' GROUP BY TA_user ");

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

First, don't use deprecated interfaces like "mysql_". Use mysqli or PDO. But, focusing on your query:

SELECT DISTINCT TA_user
FROM table_A INNER JOIN
     table_B
     ON table_B.id = table_A.TA_user
WHERE TA_date LIKE '%$vardate%'
ORDER BY TA_user ASC;

One possibility is that the join key is not correct. Assuming it is, the other big issue is using like with a date. It is not clear exactly what you want, but I am guessing that it is something like this:

SELECT DISTINCT TA_user
FROM table_A INNER JOIN
     table_B
     ON table_B.id = table_A.TA_user
WHERE TA_date = date('$vardate')
ORDER BY TA_user ASC;

You will want $vardate to be in the format of "YYYY-MM-DD" to ensure proper conversion. Or, better yet, make it a parameter instead of an embedded constant.

Upvotes: 0

Related Questions