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;
>Solution :
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