Trouble creating SQL queries – Beginner

November 26, 2021

I’ve created the linked ERD for some university work but SQL is the one thing that just hasn’t been sticking. I am required to list the id, first name, last name, telephone and address of all members who have made more than one purchase, ensuring I add an appropriate alias when retrieving the details for all members.

ERD link –
[1]: https://i.stack.imgur.com/AD6C0.png

My attempt is below. Is this correct? Can I add individual aliases to each member detail? Can I use "WHERE purchaseTotal" if purchases aren’t a part of the member table?

SELECT (memberId AS 'ID', memberFirstName AS 'First Name', memberLastName AS 'Last Name',
        memberAddress AS 'Address', memberPhone AS 'Phone')
FROM Member
WHERE purchaseTotal > 1;

>Solution :

In very basic terms, you can use only that column in a query, which is present in the tables being queried.

So in your query above, you are using purchaseTotal column, but that is not available in Member table. So you need to add Purchases table as well.

The query should be

SELECT Members.memberId AS 'ID', memberFirstName AS 'First Name', memberLastName AS 'Last Name', memberAddress AS 'Address', memberPhone AS 'Phone'
FROM Purchases
LEFT JOIN Members ON Purchases.memberId = Members.memberId
WHERE purchaseTotal > 1;

Note :

For all the columns that are common in both the tables, you need to specify from which table you need to display the column. Ex, Members.memberId