Selecting data from two different tables in phpmyadmin

Guys

I have two tables,

A = Client table (client_id, client_name fields)
B = History table (amount field)

I want to select all clients from Client tables who have any record/amount in History Table.

Please let me know how to do this?

Thank you


GCS

 

try this

SELECT client_id as client,client_name,(SELECT count(id) FROM history_table WHERE client_id=client) AS entries FROM clients_table WHERE entries>0

Code (markup):

 

Hi buddy,

Thanks for helping but it gives error

Documentation #1054 - Unknown column 'entries' in 'where clause'

I have another question you used "WHERE client_id=client" in this case I will use client_id=client_id because in both tables there is same field client_id. How SQL will know that with which client_id to match?

GCS

 

The correct forum for this question is the databases forum (http://forums.digitalpoint.com/forumdisplay.php?f=57).

If client_id is a field in both tables, you can refer to it in your query by doing table_A_name.client_id or table_B_name.client_id (where table_A_name is the name of your A table).

You can try this query:
SELECT DISTINCT table_A.client_id, client_name FROM table_A WHERE table_A.client_id = table_B.client_id

 

Thank you for your help but your command is not complete. That is missing GROUP BY option and also after DIST you did not selected field from table_B.

Anyhow you used DISTINCT and that clicked me and after some research I was able to do what I was trying to do.

GCS

 

i havent tested the query i wrote

SELECT client_id as client,client_name FROM clients_table WHERE (SELECT count(id) FROM history_table WHERE client_id=client)>0

Code (markup):

this should work

You can rename these like i did in here

SELECT client_id as client .....

Code (markup):

after this when u need client_id column just type client

 

Try this

SELECT client.name,history.amount FROM HISTORY LEFT JOIN client ON client .client_id = history.client_id