Geting data from 2 differnt tables and joining them sql-CodePudding

I am trying to write a query where I need to list client names and their addresses. the problem I have is that the data is spread into two tables Client & Client_Address. I am new to SQL and databases so this might be an easy task to do I'm unsure. I have done some research and have thought about using inner-join but unsure how to implement it in my scenario. the contents of the client table are;

Client (ClientNum, ClientName)

the contents for Client_Address are;

Client_Address (ClientNum, addressType, street, city, state, postcode)

I need to write a query that includes the ClientName from the client table and I also need to include addressType, street, city, state and postcode from the Client_Address table.

the desired output would be;

clientName, addressType, street, city, state and postcode

See bellow my current code:

SELECT clientname, addressType, street, city, state, postcode
FROM   client , Client_Address
ORDER BY clientName ASC;

CodePudding user response：

You've written a cross product, you haven't specified how the rows are related between the two tables. Use a proper join:

SELECT clientname, addressType, street, city, state, postcode
FROM client AS c
JOIN client_address AS a ON c.clientnum = a.clientnum
ORDER BY clientName

CodePudding user response：

You need to apply join query as below:

SELECT C.clientname, CA.addressType, CA.street, CA.city, CA.state, CA.postcode
FROM   client AS C INNER JOIN Client_Address AS CA
ON C.clientnum = CA.clientnum
ORDER BY C.clientName ASC